inserts not working when trigger is turned on

Question

inserts not working when trigger is turned on

torpkev

SSCarpal Tunnel

Points: 4819
More actions
June 25, 2009 at 2:23 pm

#211271

I'm seeing something a bit weird
I have a stored procedure that inserts some data into a table, once it inserts that, it'll insert into another table (which is foreign key'd to the first table)
This works just fine (see pseudo code below)
INSERT INTO com_S
DECLARE @myID INT
SET @myID = @@IDENTITY
At this point its working, @myID has a valid value (lets say 15012)
So then I do
INSERT INTO com_T
And it works just fine.
Now I added a log table to store some changes made in com_S.. its called com_SL and I put a trigger on com_S to auto-insert the information into com_SL
ALTER TRIGGER [dbo].[trg_InsertSource] ON [dbo].[com_S] AFTER INSERT
FOR INSERT AS
BEGIN
SET NOCOUNT ON;
INSERT INTO dbo.com_SL (
source_id,
comments,
create_date,
create_user)
SELECT source_id,
reprocess_reason, GETDATE(), SUSER_SNAME()
FROM Inserted
END
The trigger works well until I run my stored procedure.. with the trigger on, heres what happens
INSERT INTO com_S
DECLARE @myID INT
SET @myID = @@IDENTITY
At this point its working, @myID has a valid value (lets say 15012)
So then I do
INSERT INTO com_T fails due to a constraint error.. so I through some debug code in there and @myID is marked as being there, but if I do a SELECT * FROM com_S where source_id = @myID it tells me there are 0 records.
So it seems like with the trigger on, the com_S record is not being committed correctly.. weird stuff. Any help would be appreciated
Thanks
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Does com_SL have an identity column? @@Identity returns the last identity value assigned, so if the trigger inserts into a table with an identity, it'll be that identity value that comes back.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

To add to Gail's comment, you should look into SCOPE_IDENTITY() or the new OUTPUT clause (the recommended solution).

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

torpkev SSCarpal Tunnel Points: 4819 More actions · Answer 3

Yes it does.. i didn't think it would overlay the one inside my current transaction though.. looks like SCOPE_IDENTITY did the trick though.. handy to know.. i have @@IDENTITY in a lot of places, I always assumed it was just within the 1 transaction i was working in.

Thanks guys.. on to the next problem 🙂

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

I long for a job where my databases dont have any pesky users accessing them 🙂