Determine identity of row to be deleted 1

[Kraeg]

I'm trying to create a Trigger that will execute when a row in a certain table is deleted. It will ever only be one row at a time, and I need the trigger to determine which row it is (by its primary key).

So I am thinking an INSTEAD OF trigger that will grab the PK value, perform the trigger task, then delete it. Is there a way to determine which row was supposed to be deleted (like @@IDENTITY etc. for INSERTS)?

 

[john76]

Use the 'Deleted' table in the trigger.

CREATE TRIGGER MyTrigger
  ON  MyTable
  FOR Delete
  AS
    SELECT  pkField
      FROM  Deleted

Then perform the rest of your processing.
Also, there is no need to use your only INSTEAD OF trigger for this, an AFTER trigger will work just as well. (Unless there is more you haven't said)

HTH,
John

 

[MacLeod72]

When a delete trigger is activated, a logical table named deleted is created containing all of the data to be removed. If I am understanding your question correctly, you should be able to use a trigger with the following:

DECLARE @DEL_PK bigint

SELECT @DEL_PK = PK from deleted

INSERT INTO AUDIT (Table, Key) values ('This_Table', @DEL_PK)

This will insert the primary key into an audit table. It is important to note that this action will update the @@IDENTITY value if the AUDIT table contains an identity column, but will leave the SCOPE_IDENTITY value intact after leaving this trigger.

Hope this helps!

--Rob

 

[Kraeg]

Many thanks John..... I've got it working thanks to your advice.

 

[Kraeg]

I've got it working Rob, but thanks.