SCOPE_IDENTITY() and "instead of" Triggers.

Here's a fun question :)
If I have an instead of trigger on a table, which replaces the insert, how
can I get the identity insert from the data inserted?
-- example --
IF OBJECT_ID('dbo.tblTest') IS NOT NULL DROP TABLE dbo.tblTest
CREATE TABLE dbo.tblTest ( ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY
CLUSTERED , Data1 CHAR(1) NOT NULL )
INSERT INTO dbo.tblTest ( Data1 ) VALUES ( 'A' )
PRINT 'T-SQL: B ->' + CAST( SCOPE_IDENTITY() AS VARCHAR(11)) -- 1
GO
CREATE TRIGGER
TR_dbo_tblTest
ON
dbo.tblTest
INSTEAD OF INSERT
AS
SET NOCOUNT ON
IF ( ( SELECT COUNT(*) FROM inserted ) > 0 )
BEGIN
INSERT INTO
dbo.tblTest ( Data1 )
SELECT
Data1
FROM
inserted
PRINT 'TR_dbo_tblTest ->' + CAST( SCOPE_IDENTITY() AS VARCHAR(11)) -- 2
END
GO
INSERT INTO dbo.tblTest ( Data1 ) VALUES ( 'B' )
-- The following should return 2, but it returns NULL because the insert
was done by the trigger.
PRINT 'T-SQL: B ->' + ISNULL( CAST( SCOPE_IDENTITY() AS VARCHAR(11)) ,
'<NULL>' ) -- NULLI could be wrong, but are trying to get the ID for the record inserted?
If you are... When I use Int Identity fields for the primary key, I use a SP
to insert my records when I need to know what the primary key is.
For example, this SP is used to insert a new record into the Contacts table.
It returns a result set with a field called NewID that contains the ID for
the new record.
Create Procedure [dbo].[NewContactRec_SP]
@.LName VARCHAR(30),
@.FName VARCHAR(20),
@.ResID Int,
@.StaffID Int
as
Insert Into Contacts
(LName,FName,ResID,CreatedStaffID)
Values (@.LName,@.Fname,@.ResID,@.StaffID)
/* Return New ID */
Select SCOPE_IDENTITY() As NewID
HTH,
-Steve-|||For INSTEAD OF triggers, use the ol' @.@.IDENTITY instead:
INSERT INTO dbo.tblTest ( Data1 ) VALUES ( 'B' )
PRINT 'T-SQL: B ->' + ISNULL( CAST( @.@.IDENTITY AS VARCHAR(11)) ,'<NULL>' )
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Rebecca York" <rebecca.york {at} 2ndbyte.com> wrote in message
news:43551654$0$135$7b0f0fd3@.mistral.news.newnet.co.uk...
> Here's a fun question :)
> If I have an instead of trigger on a table, which replaces the insert, how
> can I get the identity insert from the data inserted?
>
> -- example --
> IF OBJECT_ID('dbo.tblTest') IS NOT NULL DROP TABLE dbo.tblTest
> CREATE TABLE dbo.tblTest ( ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY
> CLUSTERED , Data1 CHAR(1) NOT NULL )
> INSERT INTO dbo.tblTest ( Data1 ) VALUES ( 'A' )
> PRINT 'T-SQL: B ->' + CAST( SCOPE_IDENTITY() AS VARCHAR(11)) -- 1
>
> GO
> CREATE TRIGGER
> TR_dbo_tblTest
> ON
> dbo.tblTest
> INSTEAD OF INSERT
> AS
> SET NOCOUNT ON
> IF ( ( SELECT COUNT(*) FROM inserted ) > 0 )
> BEGIN
> INSERT INTO
> dbo.tblTest ( Data1 )
> SELECT
> Data1
> FROM
> inserted
> PRINT 'TR_dbo_tblTest ->' + CAST( SCOPE_IDENTITY() AS VARCHAR(11)) -- 2
> END
> GO
>
> INSERT INTO dbo.tblTest ( Data1 ) VALUES ( 'B' )
> -- The following should return 2, but it returns NULL because the insert
> was done by the trigger.
> PRINT 'T-SQL: B ->' + ISNULL( CAST( SCOPE_IDENTITY() AS VARCHAR(11)) ,
> '<NULL>' ) -- NULL
>|||We have a ton of auditing code inside the trigger and can't change it to a
stored proc, mainly because people can still edit the data in SQL-EM and
these changes still need to be audited.
"Steve Zimmelman" <skz@.charter.nospam.net> wrote in message
news:eEstw$$0FHA.3660@.TK2MSFTNGP15.phx.gbl...
> I could be wrong, but are trying to get the ID for the record inserted?
> If you are... When I use Int Identity fields for the primary key, I use a
SP
> to insert my records when I need to know what the primary key is.
> For example, this SP is used to insert a new record into the Contacts
table.
> It returns a result set with a field called NewID that contains the ID for
> the new record.
> Create Procedure [dbo].[NewContactRec_SP]
> @.LName VARCHAR(30),
> @.FName VARCHAR(20),
> @.ResID Int,
> @.StaffID Int
> as
> Insert Into Contacts
> (LName,FName,ResID,CreatedStaffID)
> Values (@.LName,@.Fname,@.ResID,@.StaffID)
> /* Return New ID */
> Select SCOPE_IDENTITY() As NewID
> HTH,
> -Steve-
>|||Ok,
But that requires an exclusive table lock to stop people putting in more
data :)
I tried SET TRANSACTION ISOLATION LEVEL SERIALIZABLE,
but this still allowed multiple transactions to insert data
Good 'ol WITH(TABLOCKX) :)
Unless there's another way to block inserts, without blocking everything
else?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uUnggGA1FHA.268@.TK2MSFTNGP09.phx.gbl...
> For INSTEAD OF triggers, use the ol' @.@.IDENTITY instead:
> INSERT INTO dbo.tblTest ( Data1 ) VALUES ( 'B' )
> PRINT 'T-SQL: B ->' + ISNULL( CAST( @.@.IDENTITY AS VARCHAR(11)) ,'<NULL>' )
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Rebecca York" <rebecca.york {at} 2ndbyte.com> wrote in message
> news:43551654$0$135$7b0f0fd3@.mistral.news.newnet.co.uk...
how
insert
>|||> But that requires an exclusive table lock to stop people putting in more
> data :)
Hmm, not sure I understand. Why are you saying that?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Rebecca York" <rebecca.york {at} 2ndbyte.com> wrote in message
news:43551dbd$0$142$7b0f0fd3@.mistral.news.newnet.co.uk...
> Ok,
> But that requires an exclusive table lock to stop people putting in more
> data :)
> I tried SET TRANSACTION ISOLATION LEVEL SERIALIZABLE,
> but this still allowed multiple transactions to insert data
> Good 'ol WITH(TABLOCKX) :)
> Unless there's another way to block inserts, without blocking everything
> else?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:uUnggGA1FHA.268@.TK2MSFTNGP09.phx.gbl...
> how
> insert
>|||Yeah, this stinks. Like Tibor says, use @.@.identity if you can (it does not
require locks, it is scoped to a single session.
The best thing to do is to use your other key (you should have one because
the surrogate key (the identity value) should be a surrogate for something,
otherwise you have a potential mess) to fetch the value:
insert into dbo.tblTest --probably stop prefixing tables with tbl too :)
values...
select ID -- generally better to name <tablename>Id so they are easier to
implement/use as foreign keys
from tblTest
where keycolumn<s> = @.valueYouEntered
If you want it changed in the future, please go to:
http://lab.msdn.microsoft.com/produ...1b29351&lc=1033
And vote for this!
Thanks!
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Rebecca York" <rebecca.york {at} 2ndbyte.com> wrote in message
news:43551654$0$135$7b0f0fd3@.mistral.news.newnet.co.uk...
> Here's a fun question :)
> If I have an instead of trigger on a table, which replaces the insert, how
> can I get the identity insert from the data inserted?
>
> -- example --
> IF OBJECT_ID('dbo.tblTest') IS NOT NULL DROP TABLE dbo.tblTest
> CREATE TABLE dbo.tblTest ( ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY
> CLUSTERED , Data1 CHAR(1) NOT NULL )
> INSERT INTO dbo.tblTest ( Data1 ) VALUES ( 'A' )
> PRINT 'T-SQL: B ->' + CAST( SCOPE_IDENTITY() AS VARCHAR(11)) -- 1
>
> GO
> CREATE TRIGGER
> TR_dbo_tblTest
> ON
> dbo.tblTest
> INSTEAD OF INSERT
> AS
> SET NOCOUNT ON
> IF ( ( SELECT COUNT(*) FROM inserted ) > 0 )
> BEGIN
> INSERT INTO
> dbo.tblTest ( Data1 )
> SELECT
> Data1
> FROM
> inserted
> PRINT 'TR_dbo_tblTest ->' + CAST( SCOPE_IDENTITY() AS VARCHAR(11)) -- 2
> END
> GO
>
> INSERT INTO dbo.tblTest ( Data1 ) VALUES ( 'B' )
> -- The following should return 2, but it returns NULL because the insert
> was done by the trigger.
> PRINT 'T-SQL: B ->' + ISNULL( CAST( SCOPE_IDENTITY() AS VARCHAR(11)) ,
> '<NULL>' ) -- NULL
>|||> Yeah, this stinks. Like Tibor says, use @.@.identity if you can (it does
> not require locks, it is scoped to a single session.
Oh yeah, the reason why you might not be able to use this would be if a
trigger inserted data into another table.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:OyyqtiB1FHA.2884@.TK2MSFTNGP09.phx.gbl...
> Yeah, this stinks. Like Tibor says, use @.@.identity if you can (it does
> not require locks, it is scoped to a single session.
> The best thing to do is to use your other key (you should have one because
> the surrogate key (the identity value) should be a surrogate for
> something, otherwise you have a potential mess) to fetch the value:
> insert into dbo.tblTest --probably stop prefixing tables with tbl too :)
> values...
> select ID -- generally better to name <tablename>Id so they are easier to
> implement/use as foreign keys
> from tblTest
> where keycolumn<s> = @.valueYouEntered
> If you want it changed in the future, please go to:
> http://lab.msdn.microsoft.com/produ...1b29351&lc=1033
> And vote for this!
> Thanks!
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often
> convincing." (Oscar Wilde)
> "Rebecca York" <rebecca.york {at} 2ndbyte.com> wrote in message
> news:43551654$0$135$7b0f0fd3@.mistral.news.newnet.co.uk...
>|||Yeah I just remembered,
The auditing tables also have an identity field :)
fun \o/
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:e4ckAmB1FHA.3956@.TK2MSFTNGP09.phx.gbl...
> Oh yeah, the reason why you might not be able to use this would be if a
> trigger inserted data into another table.
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often
convincing."
> (Oscar Wilde)
> "Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
> news:OyyqtiB1FHA.2884@.TK2MSFTNGP09.phx.gbl...
because
to
http://lab.msdn.microsoft.com/produ...1b29351&lc=1033
> ----
--
2
insert
>|||Yes you're right of course it's session based,
I've been playing with mysql too much at the wend :o
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:OyyqtiB1FHA.2884@.TK2MSFTNGP09.phx.gbl...
> Yeah, this stinks. Like Tibor says, use @.@.identity if you can (it does
not
> require locks, it is scoped to a single session.