April 30, 2010 at 6:27 am
Hi,
I've a table with an identity column as a primary key, when I perform an insert I'd like to use the identity value as a default value for another column. At the moment we perform an insert, get the identity value using SCOPE_IDENTITY() and then update the record. However, the table is audited using insert and update triggers and a client has complained that three rows are being added to the audit table (one for the insert, one for the old record and one for the new).
Ideally I'd like to do the following:
INSERT INTO dbo.Test (externalID) VALUES (SCOPE_IDENTITY())
But of course it doesn't work, is there any other way around this?
My sample table looks like the following:
CREATE TABLE dbo.Test
(
ID INT IDENTITY(1,1) PRIMARY KEY,
externalID INT NOT NULL
)
Thanks for your help.
April 30, 2010 at 7:31 am
instead of using SCOPE_IDENTITY, it's usually recommended to use the OUTPUT clause to get the values inserted;
the advantage is similar to how you have to construct a TRIGGER: often times you need to handle more than one row at a time, so isntead of stuffing stuff into local variables, you use temp tables or variables.
using your table, here is an example
CREATE TABLE dbo.Test
(
ID INT IDENTITY(1,1) PRIMARY KEY,
externalID INT NOT NULL
)
CREATE TABLE dbo.AnotherTable
(
ID INT ,
externalID INT NOT NULL
)
declare @results table (id int ,externalID int )
INSERT INTO dbo.Test(externalID)
--the magic OUTPUT Clause has access to the internal tables INSERTED and DELETED, depedning on whether you are doing insert/update/delete
OUTPUT
INSERTED.id,
INSERTED.externalID
--need to put the results in a table of some sourt.
INTO @results
--the stuff getting inserted
SELECT 21 AS ExternalID UNION ALL
SELECT 42 AS ExternalID
--now do something witht he resutls
insert into AnotherTable
Select * from @results
--confirm i got the values
select * from AnotherTable
Lowell
April 30, 2010 at 8:09 am
Hi Lowell,
Thanks for your response, but I don't think the OUTPUT clause will help me. I should have posted a full example showing the problem the first time:
CREATE TABLE dbo.Test
(
ID INT IDENTITY(1,1) PRIMARY KEY,
externalID INT NULL
)
GO
CREATE TABLE dbo.TestAudit
(
auditID INT IDENTITY(100,1) PRIMARY KEY,
auditType char(3) NOT NULL,
ID INT NOT NULL,
externalID INT NULL
)
GO
CREATE TRIGGER dbo.tr_test_insert ON dbo.Test FOR INSERT
AS
BEGIN
SET NOCOUNT ON
INSERT INTO dbo.TestAudit (auditType, ID, externalID)
SELECT 'INS', ID, externalID
FROM inserted
END
GO
CREATE TRIGGER dbo.tr_test_update ON dbo.Test FOR UPDATE
AS
BEGIN
SET NOCOUNT ON
INSERT INTO dbo.TestAudit (auditType, ID, externalID)
SELECT 'OLD', ID, externalID
FROM deleted
INSERT INTO dbo.TestAudit (auditType, ID, externalID)
SELECT 'NEW', ID, externalID
FROM inserted
END
GO
INSERT INTO dbo.Test (externalID) VALUES (null)
DECLARE @ID int
SET @ID = SCOPE_IDENTITY()
UPDATE dbo.Test SET externalID = @ID WHERE ID = @ID
-- This will show three records, when ideally I only want to see one.
SELECT * FROM dbo.TestAudit
Basically I want externalID to default to the value of ID on the insert. At the moment we have to do an update after the insert but this generates audits which the client doesn't want. The user can change externalID if they want later.
Thanks again.
April 30, 2010 at 8:26 am
gotcha;
I worked around the issue by combining the two triggers into one.
my logic is on insert, the external id is ALWAYS the Identity ID; you can change it later.
this does what you were after:
CREATE TABLE dbo.Test
(
ID INT IDENTITY(1,1) PRIMARY KEY,
externalID INT NULL
)
GO
CREATE TABLE dbo.TestAudit
(
auditID INT IDENTITY(100,1) PRIMARY KEY,
auditType char(3) NOT NULL,
ID INT NOT NULL,
externalID INT NULL
)
GO
CREATE TRIGGER dbo.tr_test_insert ON dbo.Test FOR INSERT,UPDATE
AS
BEGIN
SET NOCOUNT ON
--get my default value
UPDATE dbo.Test
SET dbo.Test.externalID = INSERTED.ID
FROM INSERTED
WHERE dbo.Test.ID = INSERTED.ID
--do my regular auditing
IF EXISTS(SELECT * FROM DELETED)--we KNOW it's an updated
BEGIN
INSERT INTO dbo.TestAudit (auditType, ID, externalID)
SELECT 'OLD', ID, externalID
FROM deleted
INSERT INTO dbo.TestAudit (auditType, ID, externalID)
SELECT 'NEW', ID, externalID
FROM inserted
END
ELSE
BEGIN
INSERT INTO dbo.TestAudit (auditType, ID, externalID)
SELECT 'INS', ID, ID
FROM INSERTED
END
END
GO
INSERT INTO dbo.Test (externalID) VALUES (null)
-- This will show three records, when ideally I only want to see one.
SELECT * FROM dbo.Test
SELECT * FROM dbo.TestAudit
Lowell
April 30, 2010 at 8:35 am
Good idea, thanks Lowell.
April 30, 2010 at 9:06 am
You could also use a proc and an independent sequence table. This has the advantage that you can make the target column NOT NULL and/or UNIQUE if you want to:
CREATE TABLE dbo.Test
(
ID INT PRIMARY KEY,
externalID INT NOT NULL
);
GO
CREATE PROC dbo.prc_TestInsert
AS
BEGIN;
INSERT INTO dbo.Sequence DEFAULT VALUES /* IDENTITY column */;
INSERT INTO dbo.Test (ID, externalID)
VALUES (SCOPE_IDENTITY(),SCOPE_IDENTITY());
END;
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply