@@TRANCOUNT difference between SQL 7 and SQL 2000 in trigger

[Neil Rutherford]

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

[Erland Sommarskog]

Neil Rutherford (ne*************@yahoo.com) writes:

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?

Apparently there was - consciously or by chance - a change in SQL2000.
I cannot say why, and indeed 2 would be a more expected result in
this situation.

But I would be interesting to know why this would be an issue? It sounds
to me like your triggers must be doing something quite interesting.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Igor Raytsin]

BOL says:
"Microsoft SQL Server 2000 increments the transaction count within a
statement only when the transaction count is 0 at the start of the
statement. In SQL Server version 7.0, the transaction count is always
incremented, regardless of the transaction count at the start of the
statement. This can cause the value returned by @@TRANCOUNT in triggers to
be lower in SQL Server 2000 than it is in SQL Server version 7.0.

In SQL Server 2000, if a COMMIT TRANSACTION or COMMIT WORK statement is
executed in a trigger, and there is no corresponding explicit or implicit
BEGIN TRANSACTION statement at the start of the trigger, users may see
different behavior than on SQL Server version 7.0. Placing COMMIT
TRANSACTION or COMMIT WORK statements in a trigger is not recommended."
HTH
Igor
ne*************@yahoo.com (Neil Rutherford) wrote in message news:<d6**************************@posting.google. com>...

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

[Erland Sommarskog]

Igor Raytsin (ig*****@yahoo.com) writes:

BOL says:
"Microsoft SQL Server 2000 increments the transaction count within a
statement only when the transaction count is 0 at the start of the
statement. In SQL Server version 7.0, the transaction count is always
incremented, regardless of the transaction count at the start of the
statement. This can cause the value returned by @@TRANCOUNT in triggers to
be lower in SQL Server 2000 than it is in SQL Server version 7.0.

In SQL Server 2000, if a COMMIT TRANSACTION or COMMIT WORK statement is
executed in a trigger, and there is no corresponding explicit or implicit
BEGIN TRANSACTION statement at the start of the trigger, users may see
different behavior than on SQL Server version 7.0. Placing COMMIT
TRANSACTION or COMMIT WORK statements in a trigger is not recommended."

It's even documented! I didn't know that. Thanks, Igor!

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Neil Rutherford]

Thanks for your help guys.

Someone put logic in a trigger to only continue
if the @@TRANCOUNT came from an explicit transaction in
SQL Server 7 and the @@TRANCOUNT > 1

Like mentioned, in SQL 7 this works, but in SQL 2000..
it breached the integrity of a whole data warehouse system
test.

I have to convince the developers and management that
there is a change between versions. The developers
are convinced there is difference between the server
config and they believe that both versions should
work identically.

Unless I'm going blind.. where in books on-line is
the passage above?



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

Neil Rutherford (ne*************@yahoo.com) writes:

Someone put logic in a trigger to only continue
if the @@TRANCOUNT came from an explicit transaction in
SQL Server 7 and the @@TRANCOUNT > 1
Dubious usage. I have not checked, but recursive trigger calls
may have slipped.

True, I have stored procedures which barf if you call them without
a transaction in progress, but that is because they perform only
half the job.
I have to convince the developers and management that
there is a change between versions. The developers
are convinced there is difference between the server
config and they believe that both versions should
work identically.
Obviously there is a difference between versions. This is nothing you
configure.
Unless I'm going blind.. where in books on-line is
the passage above?

I searched for the string "the transaction count is always incremented"
and found two hits.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp