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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy