Trigger Help

[cpark87]

I am having trouble with an insert trigger: Here are some quick facts.

1) Using Data Access Page with a SQL Backend to enter data and then save into a table

2) I have an insert trigger on a table titled tblMapping_Sft that will take the values entered on the data access page and insert then in a table titled tblMapping_Sft_Audit

I have been able to get this to work, however something strange occurs. After saving the record, the data access page will immediately display another record. After running a trace I found out what is happening but cannot fix it.

When I run the trace and insert through Query Analyzer it works fine. When I run the trace and insert through a data access page I get the problem... The details of the trace are listed below

The problem seems like the Select @@Identity is retriving the Identity field in my audit table and not my original table. Is there a way around this.

SQL:BatchCompleted
Insert tangram.tblMapping_Sft (fldID, AltName, AltVersion, AltStatus)
Values (100, 'Test', 'Test', 'test')

SET NO_BROWSETABLE ON
go
exec sp_executesql N'INSERT INTO "Inventory_Dev"."tangram"."tblMapping_Sft"
("fldID","AltName","AltVersion","AltStatus","Deleted","DateCreated","CreatedBy")
VALUES (@P1,@P2,@P3,@P4,@P5,@P6,@P7)', N'@P1 int,@P2 nvarchar(3),@P3 nvarchar(3),
@P4 nvarchar(3),@P5 int,@P6 datetime,@P7 nvarchar(7)', 123, N'123', N'123', N'123', 0, 'Jan 20 2005 2:13:49:000PM', N'parkxcx'
go
SELECT @@IDENTITY
go
exec sp_executesql N'SELECT * FROM (SELECT "tangram"."tblMapping_Sft".*
FROM "tangram"."tblMapping_Sft")
AS DRVD_TBL WHERE "AltNameID" = @P1', N'@P1 int', 415
go

 

[SQLSister]

NEver use @@identy to get an identity value inserted. Use scope_identity() instead. @@identity will not work properly if there is a trigger on the table which inserts to another table with an identity field.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[cpark87]

I read that in an article and tried. My trigger doesn't currenty have the @@identity in it. The select @@identity is being picked up from the trace I ran and it only occurs when adding records to sql using access adp, data access page, or in sql enterprise manager. I haven't found a way to stop if from using the @@Identity.

 

[cpark87]

I found this question posted and it basically is the same problem has mine.. Just looking to see if there is a fix or this is just an issue when using a front end like an access project

http://www.tek-tips.com/viewthread.cfm?qid=535231