April 24, 2013 at 8:04 am
Hi,
I need to use SET IDENTITY_INSERT ON within a trigger query:
---------------------------------------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[tr_HEADER]
ON [dbo].[HEADER]
INSTEAD OF INSERT
--SET IDENTITY_INSERT [HEADER] ON
AS
DELETE FROM HEADER
FROM HEADER P JOIN INSERTED I ON
P.[TRADE_CD] = I.[TRADE_CD]
INSERT INTO HEADER
SELECT * FROM INSERTED
SET IDENTITY_INSERT [HEADER] OFF
------------------------------------------------
I have tried putting 'SET IDENTITY_INSERT [HEADER] ON' after 'INSTEAD OF INSERT' but it gives an error.
Thanks.
April 24, 2013 at 8:14 am
wow that looks a little scary to me, where maybe the logic isn't quite clear.
it looks like you want to delete any existing records that might end up having duplicate TRADE_CD.
I'd consider changing the INSTEAD OF INSERT to merge the data instead; that would be a much better decision, but if you want to stick with deleting,
i'd suggest a different trigger instead, maybe something like this:
CREATE TRIGGER [dbo].[tr_HEADER]
ON [dbo].[HEADER]
AFTER INSERT
AS
DELETE FROM HEADER
FROM HEADER P JOIN INSERTED I ON
P.[TRADE_CD] = I.[TRADE_CD]
AND HEADER.PRIMARYKEYCOLUMN NOT IN(SELECT PRIMARYKEYCOLUMN INSERTED)
Lowell
April 24, 2013 at 8:31 am
Lowell (4/24/2013)
wow that looks a little scary to me, where maybe the logic isn't quite clear.it looks like you want to delete any existing records that might end up having duplicate TRADE_CD.
I'd consider changing the INSTEAD OF INSERT to merge the data instead; that would be a much better decision, but if you want to stick with deleting,
i'd suggest a different trigger instead, maybe something like this:
CREATE TRIGGER [dbo].[tr_HEADER]
ON [dbo].[HEADER]
AFTER INSERT
AS
DELETE FROM HEADER
FROM HEADER P JOIN INSERTED I ON
P.[TRADE_CD] = I.[TRADE_CD]
AND HEADER.PRIMARYKEYCOLUMN NOT IN(SELECT PRIMARYKEYCOLUMN INSERTED)
Actually my question was to use 'SET IDENTITY_INSERT ON' within the above trigger.
I have tested the trigger I created and it works okay. Its just that the table has an identity column and to insert values I need to use 'SET IDENTITY_INSERT ON'.
My only question is where do I use the 'SET IDENTITY_INSERT ON' within the trigger ?
Thanks.
April 24, 2013 at 8:42 am
ok, i'll skip the peer review and go straight to the error you are tripping over:
your error was pure syntax.
the SET IDENTITY_INSERT must be inside the body of the trigger,..... that is, AFTER THE AS
CREATE TRIGGER [dbo].[tr_HEADER]
ON [dbo].[HEADER]
INSTEAD OF INSERT
AS
BEGIN --Trigger Body
SET IDENTITY_INSERT [HEADER] ON
DELETE FROM HEADER
FROM HEADER P JOIN INSERTED I ON
P.[TRADE_CD] = I.[TRADE_CD]
INSERT INTO HEADER
SELECT * FROM INSERTED
SET IDENTITY_INSERT [HEADER] OFF
END --Trigger Body
Lowell
April 24, 2013 at 8:45 am
Lowell (4/24/2013)
ok, i'll skip the peer review and go straight to the error you are tripping over:your error was pure syntax.
the SET IDENTITY_INSERT must be inside the body of the trigger,..... that is, AFTER THE AS
CREATE TRIGGER [dbo].[tr_HEADER]
ON [dbo].[HEADER]
INSTEAD OF INSERT
AS
BEGIN --Trigger Body
SET IDENTITY_INSERT [HEADER] ON
DELETE FROM HEADER
FROM HEADER P JOIN INSERTED I ON
P.[TRADE_CD] = I.[TRADE_CD]
INSERT INTO HEADER
SELECT * FROM INSERTED
SET IDENTITY_INSERT [HEADER] OFF
END --Trigger Body
Thanks a lot Lowell, your initial solution worked !!! Sorry I misjudged your idea. Thanks again !!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply