December 6, 2012 at 6:29 am
I have a trigger with the line "SET IDENTITY_INSERT [dbo].[tblTransactions] ON" but when I run the trigger I get an error that IDENTITY_INSERT is set to OFF... any help greatly appreciated!:
USE [TrackIT]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET IDENTITY_INSERT [dbo].[tblTransactions] ON
GO
ALTER TRIGGER [dbo].[trCreateTerminationRecord]
ON [dbo].[tblTransactions]
AFTER UPDATE
AS
BEGIN
INSERT INTO tblTransactions
(tblTransactions.PO_Number,
tblTransactions.Quantity,
tblTransactions.Transaction_Number,
tblTransactions.Transaction_Type)
SELECT
INSERTED.PO_Number,
INSERTED.Quantity,
INSERTED.Transaction_Number,
INSERTED.Transaction_Type
FROM INSERTED
WHERE Transaction_Type = 'Terminate SW'
END
December 6, 2012 at 6:32 am
nowhere in the trigger example you posted is the IDENTITY_INSERT command being used.
can you show us the code you are using?
i would have expected it to look like this:
ALTER TRIGGER [dbo].[trCreateTerminationRecord]
ON [dbo].[tblTransactions]
AFTER UPDATE
AS
BEGIN
SET IDENTITY_INSERT tblTransactions ON
INSERT INTO tblTransactions
(tblTransactions.PO_Number,tblTransactions.Quantity,tblTransactions.Transaction_Number,tblTransactions.Transaction_Type)
SELECT
INSERTED.PO_Number,
INSERTED.Quantity,
INSERTED.Transaction_Number,
INSERTED.Transaction_Type
FROM INSERTED
WHERE Transaction_Type = 'Terminate SW'
SET IDENTITY_INSERT tblTransactions OFF
END
Lowell
December 6, 2012 at 6:47 am
Got this:
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[trCreateTerminationRecord]
ON [dbo].[tblTransactions]
AFTER UPDATE
AS
BEGIN
SET IDENTITY_INSERT tblTransactions ON
INSERT INTO tblTransactions
(tblTransactions.PO_Number,
tblTransactions.Quantity,
tblTransactions.Transaction_Number,
tblTransactions.Transaction_Type)
SELECT
INSERTED.PO_Number,
INSERTED.Quantity,
INSERTED.Transaction_Number,
INSERTED.Transaction_Type
FROM INSERTED
WHERE Transaction_Type = 'Terminate SW'
SET IDENTITY_INSERT tblTransactions OFF
END
ERROR: Violation of PRIMARY KEY constraint 'PK_dbo.tblTransactions'. Cannot insert duplicate key in object 'dbo.tblTransactions'.
The statement has been terminated.
December 6, 2012 at 6:51 am
We know from your other post that tblTransactions.Transaction_Number is an IDENTITY field. You are attempting to insert an Transaction_Number back into the IDENTITY field that was just created. It doesn't matter if IDENTITY_INSERT is on or not, it cannot have duplicates.
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
December 6, 2012 at 6:53 am
Transaction_Number is your PK (assuming the table definition is the same as in your other post) - you can't have duplicate values in it.
December 6, 2012 at 6:54 am
toddasd (12/6/2012)
We know from your other post that tblTransactions.Transaction_Number is an IDENTITY field. You are attempting to insert an Transaction_Number back into the IDENTITY field that was just created. It doesn't matter if IDENTITY_INSERT is on or not, it cannot have duplicates.
Unfortunately, IDENTITY fields can contain duplicates. PK's can't though.
December 6, 2012 at 7:04 am
So is there a way of simply inserting a new record into a table with a PK via a trigger?
December 6, 2012 at 7:08 am
briancampbellmcad (12/6/2012)
So is there a way of simply inserting a new record into a table with a PK via a trigger?
yes. don't reference the identity column,and let the pk automatically generate a new value..
why do you have to enter an almost identical row into the same table WHERE Transaction_Type = 'Terminate SW'?
why do you need to identical rows?
Lowell
December 6, 2012 at 7:11 am
Something like this?:
USE [TrackIT]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[trCreateTerminationRecord]
ON [dbo].[tblTransactions]
AFTER UPDATE
AS
BEGIN
SET IDENTITY_INSERT tblTransactions ON
INSERT INTO tblTransactions
(tblTransactions.PO_Number,
tblTransactions.Quantity,
tblTransactions.Transaction_Type)
SELECT
INSERTED.PO_Number,
INSERTED.Quantity,
INSERTED.Transaction_Type
FROM INSERTED
WHERE Transaction_Type = 'Terminate SW'
SET IDENTITY_INSERT tblTransactions OFF
END
December 6, 2012 at 7:14 am
no, like the code below..you don't use identity_insert unless you have to.
you didn't answer my questions:
why do you have to enter an almost identical row into the same table WHERE Transaction_Type = 'Terminate SW'?
why do you need to identical rows?
ALTER TRIGGER [dbo].[trCreateTerminationRecord]
ON [dbo].[tblTransactions]
AFTER UPDATE
AS
BEGIN
INSERT INTO tblTransactions
(tblTransactions.PO_Number,tblTransactions.Quantity,tblTransactions.Transaction_Type)
SELECT
INSERTED.PO_Number,
INSERTED.Quantity,
INSERTED.Transaction_Type
FROM INSERTED
WHERE Transaction_Type = 'Terminate SW'
END
Lowell
December 6, 2012 at 7:15 am
Yep, except you'll get an error unless you remove the SET IDENTITY_INSERT ON/OFF statements as you're no longer inserting an explicit value into the Identity field (Transaction_Number).
Lowell - http://www.sqlservercentral.com/Forums/Topic1393096-392-1.aspx
Thanks
December 6, 2012 at 7:23 am
The scenario is an ASP.net/VB.net/Javascript application has a popup that lists all the user's software. A dropdownbox allows for a Yes/no deactivation of the software, thus triggering an update that needs to be referenced in the trigger. The update is to an existing record and this is performing well. A new record has to be produced to indicate that an action has been performed, thus the need for the trigger. The application side code for generating a new record from this activity is extremely involved.
December 6, 2012 at 7:27 am
Your above code recommendation works perfectly... thank you for sharing! - Brian
December 6, 2012 at 8:18 am
briancampbellmcad (12/6/2012)
Your above code recommendation works perfectly... thank you for sharing! - Brian
So did this one http://www.sqlservercentral.com/Forums/FindPost1393195.aspx
But I understand the attraction Lowell's avatar brings. 😎
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply