trigger to capture identity - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

trigger to capture identity

Author

Topic

CorpDirect
Yak Posting Veteran

92 Posts

Posted - 2006-01-18 : 21:30:10

Trying to find a way to capture the identity value from an insert statement in a trigger. Here's what I've tried (also tried some variations on the trigger):

-- create source table for testing
CREATE TABLE	test_src	(
		id_col		INT			IDENTITY	(1,1)	NOT NULL
	,	tname		VARCHAR(30)	COLLATE Latin1_General_BIN NULL
	)
GO


-- create destination table for testing
CREATE TABLE	test_dst	(
		id_col		INT			IDENTITY	(100,1)	NOT NULL
	,	src_name	VARCHAR(30)	COLLATE Latin1_General_BIN NULL
	,	src_id		INT			NULL
	)
GO


-- create trigger for testing
CREATE TRIGGER	test_trg	ON	test_src
FOR INSERT
AS

INSERT INTO
			test_dst	(
				src_name
			,	src_id
		)
SELECT
				tname
			,	src_id	=	SCOPE_IDENTITY()
FROM
			inserted
GO


-- perform an insert on the source table
INSERT INTO	test_src
VALUES	('Test Name 01')
GO


-- select records from the destination table to see if it worked
SELECT	*	FROM	test_dst


-- clean up
DROP TABLE	test_src
DROP TABLE	test_dst

What am I doing wrong with this trigger? Can this be done?

Thanks,

Daniel

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-18 : 21:41:43

change SCOPE_IDENTITY() to @@IDENTITY

-----------------
'KH'

CorpDirect
Yak Posting Veteran

92 Posts

Posted - 2006-01-18 : 21:54:34

Oh rats, nevermind -- I realize my error. Wasn't looking at INSERTED correctly.

-- create source table for testing
CREATE TABLE	test_src	(
		id_col		INT			IDENTITY	(1,1)	NOT NULL
	,	tname		VARCHAR(30)	COLLATE Latin1_General_BIN NULL
	)
GO


-- create destination table for testing
CREATE TABLE	test_dst	(
		id_col		INT			IDENTITY	(100,1)	NOT NULL
	,	src_name	VARCHAR(30)	COLLATE Latin1_General_BIN NULL
	,	src_id		INT			NULL
	)
GO


-- create trigger for testing
CREATE TRIGGER	test_trg	ON	test_src
FOR INSERT
AS

INSERT INTO
			test_dst	(
				src_name
			,	src_id
		)
SELECT
				tname
			,	id_col
FROM
			inserted
GO


-- perform an insert on the source table
INSERT INTO	test_src
VALUES	('Test Name 01')
GO


-- select records from the destination table to see if it worked
SELECT	*	FROM	test_dst


-- clean up
DROP TABLE	test_src
DROP TABLE	test_dst

Now my test seems to work just fine. Will try it on production tables tomorrow.

One more concern: will this break if a multi-record insert is performed? I think the application inserts only a single record in the source table per transaction, so should be safe, but I still would like to know...

Thanks,

Daniel

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources