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 It开发者_Go百科em 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....
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).
Yes, simply by writting a trigger you can insert
a row when an delete
action is performed in another table, have a look at Triggers
精彩评论