September 12, 2003 at 1:16 pm
I am trying to create a trigger
CREATE TRIGGER trgSystemAfterInsert ON ROCMaster.dbo.tblSystem AFTER INSERT
AS
BEGIN TRANSACTION TranSync
INSERT INTO FPAAsset.dbo.tblSystem
(SystemId)
VALUES
(@@IDENTITY)
IF @@ERROR <> 0
ROLLBACK TRANSACTION TranSync
RETURN
COMMIT TRANSACTION
GO
The trigger is created but I get an error:
Server: Msg 515, Level 16, State 2, Line 3
Cannot insert the value NULL into column 'SystemId', table 'FPAAsset.dbo.tblSystem'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Apparently, it is trying to execute the insert when I try to create the trigger. Can someone tell me what am I missing here?
September 12, 2003 at 4:32 pm
Can you give a little more background on what you are trying to do? Some DDL on the tables structure would really help here.
It seems to me from looking at this that what you would really want is more along the lines of the following...
...
INSERT INTO FPAAsset.dbo.tblSystem(SystemID)
SELECT <IdentityColHere>
FROM inserted
...
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
September 12, 2003 at 4:46 pm
gljjr- Thanks for the help. My problem got solved when I replaced the @@IDENTITY with the SELECT SystemId FROM INSERTED.
Thanks again
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply