SQL Server Trigger - Insert the deleted record into another table with deletetime

Question

Currently I have a Item table and a ItemWaste table. Both tables will have some fields, such as: Name, Amount, etc. But the ItemWaste table will have one more field, which is the TimeWasted. I wish to automatically insert the DELETED item from the Item table to the ItemWaste table, and at the same time insert the deletion time to the TimeWasted field.

I got no idea how to do this, is it using trigger???

Hope can get some help here... Appreciate any feedback... Thanks....

Solution

Sure - not a problem.

You need a basic AFTER DELETE trigger - something like this:

CREATE TRIGGER trg_ItemDelete 
ON dbo.Item 
AFTER DELETE 
AS
    INSERT INTO dbo.ItemWaste(Name, Amount, TimeWasted)
        SELECT d.Name, d.Amount, GETDATE()
        FROM Deleted d

That's all there is! One point to remember: the trigger is called once per batch - e.g. if you delete 100 rows at once, it will be called once and the pseudo table Deleted will contain 100 rows. The trigger is not called once per row (a common misconception).

Comments

[shennyL]

Btw, I not really clear what you mean by once per batch, if i delete 3 rows, the deleted table will still have 3 rows rite?

[marc_s]

@user834754: yes - if you have a SQL statement that deletes 3 rows - your trigger will be fired once but the Deleted pseudo table will contain 3 rows, and thus 3 rows are being inserted into your ItemWaste table.