SQL Server Trigger Insert Bug

[Pete Nurse]

I first observed the following behaviour in Access ADP forms and tables
but then reproduced in Enterprise Manager. This is variation on a
recent post to microsoft.public.sqlserver -I'm posting here because
I've further refined the problem.

To preempt some advice, the following behaviour wouldn't occur if I
didn't use bound forms in Access ADP. It very much suits my purposes
to use this mode and I'm very reluctant to change that - I prefer to
resolve the basic problem.

I have several tables with insert triggers which in turn insert a
linked record in an another table. This is a necessary client
requirement.

The SQL Server cursor software (please excuse me if my terminology is
unsound) which handles data transfer to and from other applications
(such as not only Access ADP subforms but also Enterprise Manager) uses
@@IDENTITY (rather than SCOPE_IDENTITY() which would do the job
perfectly) to retrieve the ID of a newly inserted record. @@IDENTITY
seems to hold the ID of the last record created anywhere by anyone -
remarkably shoddy coding it seems to me. The upshot of this is that
rather than displaying the newly created record to the user, a record
(coincidentally) with the same ID as the record created by the trigger
routine is displayed.

If you'd like to see what problems that causes look at
http://groups.google.com.au/group/microsoft.public.access.adp.sqlserver/browse_thread/thread/f64116f7846dfa76/648f24faf6304938#648f24faf6304938
Please ignore the fact that I thought I'd disabled the trigger and
hadn't!!

Does anybody know how I can work around this problem?

[R.D]

That is the right behaviour. You can't away with that if it uses @@IDENTITY
internally.
But your problem appears to be solved with
SET NOCOUNT ON in trigger

--
Regards
R.D
--Knowledge gets doubled when shared

[Pete Nurse]

I do have SET NO COUNT ON in the trigger routine, so I don't think
that's the solution!

It would be great if I could save the @@IDENTITY before the trigger
insert and reset it afterwards. . . but I can't - I tried. I assume
@@IDENTITY is read only.

[SriSamp]

No, you cannot. @@IDENTITY is read-only.
Am not an Access Guy, but are these triggers auto generated? Can you not
change them?

Maybe you can try using INSTEAD OF triggers (if you are using SQL Server
2000). Using INSTEAD OF triggers, a regular INSERT INTO will call your code
where you can do some specific logic for saving the records and use
SCOPE_IDENTITY.
--
HTH,
SriSamp
Email: sri...@gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp

"Pete Nurse" <P...@sunspotcomp.com.au> wrote in message
news:1129546378....@g43g2000cwa.googlegroups.com...

[Pete Nurse]

OK, I clearly need to clarify.

The problem has nothing to do with Access - it also occurs if you add a
record to table from Enterprise Manager (EM) or Query Analyser (QA).

Try this . . . turn on a SQL Server Profiler trace, open a table in EM
or QA, add a record then you'll see in Profiler that @@IDENTITY is used
to identify the record to be displayed when the table display is
updated after the record insertion. If a trigger routine sqeezes
itself between the INSERT and the SELECT @@IDENTITY statement (which it
does), @@IDENTITY returns the wrong value, the wrong record appears (or
no record at all if there's no ID = @@IDENTITY) appears in the table
and I wind up posting this message.

I believe I'm right in saying that this behaviour will occur
irrespective of which type insert trigger I use.

I guess what I really need to do is run the "insert" routine after the
display had been updated - but how do make that robust? Do I wait a
second, 500 ms, 100ms or do I set semaphores with all the attendant
complications that introduces. I don't know . . . that's why my posts
are the ones with all the question marks.

[Jerry Spivey]

Pete,

Might check out

IDENT_CURRENT
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ia-iz_82i1.asp

and

SCOPE_IDENTITY
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sa-ses_6n8p.asp

HTH

Jerry

"Pete Nurse" <P...@sunspotcomp.com.au> wrote in message

news:1129549421.3...@g49g2000cwa.googlegroups.com...

[Sylvain Lafontaine fill the blanks, no spam please]

The @@identity is a global value; hence your problem. You must store its
value at the beginning of the trigger and reset it just before the trigger
exits with the following statement from Nick Sestrin:

(http://groups.google.ca/group/microsoft.public.access.adp.sqlserver/browse_thread/thread/36f0e97fdeebd0c3/8f4ba270c141bd1a?q=trigger+identity&rnum=3#8f4ba270c141bd1a)

create trigger mytable_insert_trigger on mytable for insert as

declare @identity int
declare @strsql varchar(128)

set @identity=@@identity
--your code
--insert into second table ...
--your code
set @strsql='select identity (int, ' + cast(@identity as varchar(10)) + ',1)
as id into #tmp'
execute (@strsql)

or a little more simple for the last instruction:
EXECUTE (N'SELECT Identity (Int, ' + Cast(@myid As NVarchar(10)) + ',1) AS
id INTO #Tmp'

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

"Pete Nurse" <P...@sunspotcomp.com.au> wrote in message

news:1129540307....@f14g2000cwb.googlegroups.com...

[Pete Nurse]

Thanks, Jerry, though the point is that I don't have any control over
the use of @@IDENTITY.

Have a look at Sylvain's entry - he's right on the track.

[Pete Nurse]

Sylvain, I'm indebted to you and those people who solved the same
problem 4(!) years ago. You've stuck with me though all this (and
three newsgroups!) and given me two out of my three break throughs.

The code you recommend works brilliantly - thank you very much. I'm
pretty sure that the initial problem (of cascading error messages when
inserting records in Access) will be solved). My guess is that I won't
need to use the Resynch Command trick - in fact I may now be able to
use Resynch Command as it's supposed to be used!

Here's some test code which both demonstrates the problem and solution.
Thank you linesmen, thank you ballboys.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - -

--This code demonstrates the solution to the SQL Server Insert
Trigger bug:
use Northwind

--If necessary, drop the debug table:
IF EXISTS (SELECT * FROM sysobjects
WHERE ((ID = object_id(N'dbo.tblDebug')) and
(OBJECTPROPERTY(id, N'IsUserTable') = 1)))
BEGIN
DROP TABLE dbo.tblDebug
END
GO

--Create the debug table:
CREATE TABLE dbo.tblDebug (
dbgID int IDENTITY (1, 1) NOT NULL ,
dbgDesc VARCHAR (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
GO

--If necessary, drop the trigger:
IF EXISTS (SELECT * FROM sysobjects
WHERE ((name = N'trgEmployees_Insert') AND
(type = 'TR')))
DROP TRIGGER dbo.trgEmployees_Insert
GO

--Create the defective trigger:
CREATE TRIGGER dbo.trgEmployees_Insert ON dbo.Employees FOR INSERT
AS
--Here's the offending code - this updates @@IDENTITY
INSERT INTO dbo.tblDebug (dbgDesc) VALUES ('New employee')
GO

--Trigger dbo.trgEmployees_Insert by inserting into the Employees
table:
INSERT INTO Employees
(FirstName, LastName)
VALUES('Bad','Trigger')

--Show the various different versions of identity:
SELECT FirstName, LastName, @@IDENTITY AS '@@IDENTITY',
SCOPE_IDENTITY() AS 'SCOPE_IDENTITY',
IDENT_CURRENT('Employees') 'IDENT_CURRENT'
FROM dbo.Employees
WHERE (EmployeeID = SCOPE_IDENTITY())
GO

--Alter the trigger so @@IDENTITY is now reset:
ALTER TRIGGER dbo.trgEmployees_Insert ON dbo.Employees FOR INSERT
AS
DECLARE @Identity INT
DECLARE @strSQL VARCHAR(128)

--Save @@IDENTITY:
set @Identity=@@IDENTITY

--Here's the offending code - this updates @@IDENTITY
INSERT INTO dbo.tblDebug (dbgDesc) VALUES ('New employee')

--Construct the SQL string to reset @@IDENTITY:
SET @strsql='select identity (int, ' + cast(@identity as
VARCHAR(10)) + ',
1) as id into #tmp'

--Execute the SQL:
EXEC (@strsql)
GO

--Trigger dbo.trgEmployees_Insert by inserting into the Employees
table:
INSERT INTO dbo.Employees
(FirstName, LastName)
VALUES('Good','Trigger')

--Show the various different versions of identity:
SELECT FirstName, LastName, @@IDENTITY AS '@@IDENTITY',
SCOPE_IDENTITY() AS 'SCOPE_IDENTITY',
IDENT_CURRENT('Employees') 'IDENT_CURRENT'
FROM dbo.Employees
WHERE (EmployeeID = SCOPE_IDENTITY())
GO

--Delete added Employees records:
DELETE FROM Employees WHERE (EmployeeID >= @@IDENTITY)

IF EXISTS (SELECT * FROM sysobjects
WHERE ((ID = object_id(N'dbo.tblDebug')) and
(OBJECTPROPERTY(id, N'IsUserTable') = 1)))
BEGIN
DROP TABLE dbo.tblDebug
END
GO

IF EXISTS (SELECT * FROM sysobjects
WHERE ((name = N'trgEmployees_Insert') AND
(type = 'TR')))
DROP TRIGGER dbo.trgEmployees_Insert
GO