使用 DELETE TRIGGER 在 DELETE 之前将已删除的值插入表中

2022-01-01 00:00:00 sql tsql sql-delete sql-server triggers

出于某种原因，我找不到我需要的确切答案.我在这里搜索了最后 20 分钟.

For some reason I can't find the exact answer that I need. I searched for at last 20 minutes in here.

我知道这很简单.很简单的.但是由于某种原因我无法触发触发器..

I know it's simple. VERY simple. But I can't fire the trigger for some reason..

我有一个包含两列的表格

I have a table with two columns

dbo.HashTags

|__Id_|_name_|
|  1  | Love |

我想在 DELETE 查询中将删除的值插入到另一个名为 dbo.HashTagsArchive 的表中.

I want to insert the deleted values into another table called dbo.HashTagsArchive on a DELETE query.

示例:

DELETE FROM [dbo].[HashTags] WHERE Id=1

在这个例子之后，我应该在 dbo.HashTagsArchive 中删除行，而 Id=1 的行应该在 dbo.HashTags 中删除>

After this example I should have the deleted row in dbo.HashTagsArchive and the row with Id=1 should be deleted in dbo.HashTags

我试过这个触发器:

ALTER TRIGGER [dbo].[HashTags_BeforeDelete]
    ON [dbo].[HashTags]
    FOR DELETE
AS
  BEGIN
    INSERT INTO HashTagsArchive
   ( Id,
     HashTagId,
     delete_date)
   SELECT d.Id, m.HashTagId,GETUTCDATE() FROM deleted d 
   JOIN dbo.HashTags m ON m.Id=d.Id
    DELETE FROM dbo.HashTags
    WHERE ID IN(SELECT deleted.Id FROM deleted)
  END
GO

它正在 Deleted 但在 HashTagsArchive

推荐答案

你的问题是:这个触发器触发AFTER删除已经发生.所以 HashTags 中没有更多的行可以加入了！

Your problem is: this trigger fires AFTER the delete has already happened. So there is no more row in HashTags which you could join on!

您需要改用此触发器:

ALTER TRIGGER [dbo].[HashTags_BeforeDelete]
    ON [dbo].[HashTags]
    FOR DELETE
AS
  BEGIN
    INSERT INTO HashTagsArchive(Id, HashTagId, delete_date)
       SELECT 
           d.Id, d.HashTagId, GETUTCDATE() 
       FROM deleted d 
  END
GO

Deleted 伪表包含被删除的整行 - 无需加入任何内容...

The Deleted pseudo table contains the whole row(s) that were deleted - no need to join on anything...

另外:这个触发器在删除发生后触发 - 所以你不需要自己在触发器内部做任何事情 - 只需将这些信息插入你的存档表 - 就是这样.其他一切都由 SQL Server 为您处理.

Also: this trigger fires after the delete has happened - so you don't need to do anything yourself, inside the trigger - just insert those bits of information into your archive table - that's all. Everything else is handled by SQL Server for you.

相关文章