INSTEAD OF INSERT Trigger
-
Hi guys
have 2 tables: Package and Object
Made a trigger on Package:
CREATE TRIGGER [TRG_Package_Ins] ON [Package]
FOR INSERT
AS
DECLARE
@id int,
@idObject int
SELECT @id = idPackage
FROM inserted
INSERT INTO Object (DocRef) VALUES (1)
SELECT @idObject = @@Identity
UPDATE [Package]
SET idObject = @idObject
WHERE idPackage = @id
[/Code]
This works fine. but if I after insert into package call Select @@Identity to get the idPackage, I get idObject instead
So how to make a INSTEAD OF Trigger, that FISRT insert into Object, then insert into Package??
/Weje
-
Do you mean that when calling @@IDENTITY in the script that inserts into Package in the first place you get idObject?
If so then you need to use Scope_Identity(). This will return the last identity insert within the current scope (stored procedure, trigger, etc).
For example
Insert Package (idObject) VALUES (1)
select scope_identity() -- will return the last inserted id in package
select @@IDENTITY -- will return the last inserted identity (which will be whatever the trigger inserted into Object)
Hope that helps.
-
Bit late in the day to be adding another point but I thought I'd best mention the use of variables in your trigger.
You're selecting idPackage into a variable from the inserted table. This will only work as long as only one row at a time gets inserted into the table - which of course you can never guarantee won't be the case over the lifetime of the database. If multiple rows get inserted then the 'inserted' table will have multiple rows and the select will throw an error. I'd suggest rewriting it to not use the variables.
Let me know if that didn't make sense.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply