August 7, 2004 at 7:05 am
During testing of an application, i noticed a difference between
SQL 2000 and SQL 7, both with identical config.
In a nutshell:
A table has a trigger for UPDATE and DELETE.
When a column in the table is UPDATED the following happens:
In autocommit mode, when entering a trigger the trancount equals
1 for both SQL 7 and 2000.
When the same update is performed in an explicit transaction
in SQL 7 @@TRANCOUNT equal 2, and in SQL 2000 @@TRANCOUNT equals 1.
Configuration is the same and there are no implicit transactions.
I don't need a work around as this will invalidate the migration
process as both products should behave identically.
What would influence the difference or why is there a difference???
Is there something which has been overlooked?
=========================================================
The following code replicates the problem
Ensure implicit transactions are off in both versions at the server
level, thus defaulting to autocommitted mode.
Ensure sp_configure settings are identical.
Step 1: Create a DB called test:
Step 2: Execute the following under the context of test DB.
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[trigtest]') and OBJECTPROPERTY(id, Outrigger') = 1)
drop trigger [dbo].[trigtest]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[test]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[trancount]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[trancount]
GO
CREATE TABLE [dbo].[test] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[text] [char] (10) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[trancount] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[trancount] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TRIGGER trigtest ON [dbo].[test]
FOR UPDATE, DELETE
AS
declare @trancount int
select @trancount = @@TRANCOUNT
insert into trancount ( trancount ) values ( @trancount )
Step 3: Run the following against the DB, then check trancount table.
-- Add a record to the test table (trigger will not fire)
insert into test (text) values ( 'xxxx' );
go
-- Update the value (autocommit mode) to fire trigger
-- Under SQL 7 and 2000, trancount table will only indicate 1
tranaction open.
-- This is being performed in autocommit mode.
update test set text = 'test1'
go
-- Update value using an explicit transaction
-- Under SQL 7, trancount will equal 2 in trigger, in SQL 2000
trancount equals 1
begin transaction
update test set text = 'test2'
commit work
go
August 9, 2004 at 10:41 am
This is documented in BOL in the following topic:
http://msdn.microsoft.com/library/en-us/createdb/cm_8_des_08_22lq.asp
It also should be under the "compatibility" topic, but it isn't...
Razvan
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply