How to retrieve Inserted, Updated, and Deleted ROW IDENTITY for Trigger in SQL Server 2005

It is very simple to retrieve or select Row Identity or other Columns of inserted, updated or deleted Row while writing a Trigger.


CREATE  TRIGGER tr_EmployeeInsert
ON t_Employee
AFTER INSERT AS
BEGIN

DECLARE @InsertedID AS INT;
SELECT @InsertedID =  EmployeeID FROM INSERTED;
--Now you have the INSERTED Row ID

END


This one shows how to retrieve Row Identity or other Columns of the inserted Row.


CREATE  TRIGGER tr_EmployeeUpdate
ON t_Employee
AFTER UPDATE AS
BEGIN

DECLARE @UpdatedID AS INT;
SELECT @UpdatedID =  EmployeeID FROM DELETED;
-- Now you have the UPDATED Row ID

END


You can select Row Identity or other Columns of the updated Row as well.


CREATE  TRIGGER tr_EmployeeDelete
ON t_Employee
AFTER DELETE AS
BEGIN

DECLARE @DeletdID AS INT;
SELECT @ DeletdID =  EmployeeID FROM DELETED;
-- Now you have the DELETED Row ID.

END


The same can be done with deleted Row.

Thanks
A Rahim Khan

Advertisements
    • Al
    • November 22nd, 2011

    I want to create a trigger that allows me to update the row on the table that delete has been executed.

    For Instance:
    —– Delete 1 row where the Id is # 4
    delete from tableA where Id=4

    The Tirgger will catch the T-SQL delete and instead it will update the table with a flag Column Name DeletedRow=1

    update TableA set DeletedRow=1 where Id=1
    and leave the row instant in the tableA with deleting it.

    Can this be done.

    TIA

    • I am not sure what do you want do exactly.

      “delete from tableA where Id=4” will delete the row first then execute the trigger.

      Instead you execute “update TableA set DeletedRow=1 where Id=4” will serve your purpose keeping the row intact in TableA with just updating DeletedRow Column.

        • Al
        • November 23rd, 2011

        I thought trigger gets executed first
        I want to keep the row intact and instead update the table with DeletedRow flag.
        I have a table with Identity column and every time some one delete a row the saved report does not match with Id that is what I am trying to avoid reason for report to be saved is to avoid Timeout error I could generate report ask requested but for now it is saved and I thought that I could create a trigger that eliminate the row from been deleted.

        Thanks

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: