Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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....

like image 740
shennyL Avatar asked Oct 14 '11 05:10

shennyL


People also ask

How triggers use the inserted and deleted tables?

In DML triggers, the inserted and deleted tables are primarily used to perform the following: Extend referential integrity between tables. Insert or update data in base tables underlying a view. Test for errors and take action based on the error.

Can we use Delete in trigger?

To delete a DML trigger In Object Explorer, connect to an instance of Database Engine and then expand that instance. Expand the database that you want, expand Tables, and then expand the table that contains the trigger that you want to delete. Expand Triggers, right-click the trigger to delete, and then click Delete.


1 Answers

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).

like image 54
marc_s Avatar answered Oct 11 '22 08:10

marc_s