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
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, som...@algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Thanks for your great reply. Takker og bukker (sjefen blir fornøyd).
Gard Tønnesland