SQL Server触发器 - 使用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.

目前我有一个Item表和一个ItemWaste表。两个表都有一些字段,例如:Name,Amount等。但ItemWaste表将有一个字段,即TimeWasted。我希望自动将Item表中的DELETED项插入ItemWaste表,同时将删除时间插入TimeWasted字段。

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

我不知道怎么做,是不是使用了触发器???

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

希望能在这里得到一些帮助...感谢任何反馈...谢谢......

2 个解决方案

#1

Sure - not a problem.

当然 - 不是问题。

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

你需要一个基本的AFTER DELETE触发器 - 类似这样的东西:

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

这就是全部!需要记住的一点是:每批调用一次触发器 - 例如如果一次删除100行,它将被调用一次,伪表Deleted将包含100行。每行不调用一次触发器(一种常见的误解)。

#2

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

是的,只需通过写入触发器,您可以在另一个表中执行删除操作时插入一行,查看触发器

#1