Get Current Identity(Proced ure)

[Gard T?nnesland]

Hi everyone,
How can I retrieve the current identity used in a procedure from a
trigger.

What I am trying to do is:

The procedure takes a @username (nvarchar) as a parameter, and the
procedure identifies the @username's identity and tries to delete the
identity's row. An Instead Of trigger fires, and updates the specific
row.

But how can the trigger identify the targeted row?
Which built-in function can I use?

Thanks

gardmt

[Simon Hayes]

"Gard T?nnesland" <ga*********@ho tmail.com> wrote in message
news:3d******** *************** ***@posting.goo gle.com...

Hi everyone,
How can I retrieve the current identity used in a procedure from a
trigger.

What I am trying to do is:

The procedure takes a @username (nvarchar) as a parameter, and the
procedure identifies the @username's identity and tries to delete the
identity's row. An Instead Of trigger fires, and updates the specific
row.

But how can the trigger identify the targeted row?
Which built-in function can I use?

Thanks

gardmt

You can use @@IDENTITY - see Books Online. Assuming you have MSSQL 2000,
there are also SCOPE_IDENTITY( ), and IDENT_CURRENT() , but the functionality
is slightly different in each case. To get the new value while in the stored
procedure you would use SCOPE_IDENTITY( ) instead.

Simon

[Erland Sommarskog]

[posted and mailed, please reply in news]

Gard T?nnesland (ga*********@ho tmail.com) writes:

How can I retrieve the current identity used in a procedure from a
trigger.

What I am trying to do is:

The procedure takes a @username (nvarchar) as a parameter, and the
procedure identifies the @username's identity and tries to delete the
identity's row. An Instead Of trigger fires, and updates the specific
row.

But how can the trigger identify the targeted row?
Which built-in function can I use?

In a trigger, both INSTEAD OF and AFTER triggers, you have access to
the virtual tables "inserted" and "deleted". They hold the rows that
were inserted and deleted. (So in an INSERT trigger you have only
data in "inserted" and in a DELETE trigger only in "deleted", and
in UPDATE trigger, you have data in both.)

Note that since a trigger fires once per statment, you should write
the trigger so that it handles this situation. For instance:

CREATE TRIGGER Gard_tri ON tbl INSTEAD OF DELETE AS
UPDATE tbl
SET active = 0
FROM tbl t
JOIN deleted d ON t.keycol = d.keycol
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Gard mt]

Erland Sommarskog <so****@algonet .se> wrote in message news:<Xn******* ************@12 7.0.0.1>...

[posted and mailed, please reply in news]

Gard T?nnesland (ga*********@ho tmail.com) writes:

How can I retrieve the current identity used in a procedure from a
trigger.

What I am trying to do is:

The procedure takes a @username (nvarchar) as a parameter, and the
procedure identifies the @username's identity and tries to delete the
identity's row. An Instead Of trigger fires, and updates the specific
row.

But how can the trigger identify the targeted row?
Which built-in function can I use?

In a trigger, both INSTEAD OF and AFTER triggers, you have access to
the virtual tables "inserted" and "deleted". They hold the rows that
were inserted and deleted. (So in an INSERT trigger you have only
data in "inserted" and in a DELETE trigger only in "deleted", and
in UPDATE trigger, you have data in both.)

Note that since a trigger fires once per statment, you should write
the trigger so that it handles this situation. For instance:

CREATE TRIGGER Gard_tri ON tbl INSTEAD OF DELETE AS
UPDATE tbl
SET active = 0
FROM tbl t
JOIN deleted d ON t.keycol = d.keycol

Thanks for your great reply. Takker og bukker (sjefen blir fornøyd).

Gard Tønnesland