使用DELETE TRIGGER将删除的值插入DELETE之前的表中 [英] INSERT deleted values into a table before DELETE with a DELETE TRIGGER

问题描述

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

我知道这很简单。很简单。但是由于某些原因，我无法触发触发器。

我有一个包含两列的表

dbo.HashTags

  | __Id_ | _name_ | 
 | 1 |爱
  

我想将已删除的值插入另一个名为 dbo.HashTagsArchive 进行 Delete 查询。

示例：

 从[dbo]中删除。[HashTags] Id = 1 
  

在此示例之后，我应该在 dbo.HashTagsArchive 中删除​​行，并在 Id = 1 应该在 dbo.HashTags

中删除。

我尝试了此TRIGGER：

  ALTER TRIGGER [dbo]。[HashTags_BeforeDelete] 
开[dbo]。[HashTags] 
删除
 AS 
开始
插入HashTags归档
（Id，
 HashTagId，
 delete_date）
 SELECT d.Id，m.HashTagId，GETUTCDATE（）FROM删除的d 
加入dbo.HashTags m ON m.Id = d.Id 
从dbo.HashTags 
删除ID（选择删除ID从已删除）
 END 
 GO 
  

正在获取删除ed ，但在 HashTagsArchive

<中没有 Inserted 行div class = h2_lin>解决方案

您的问题是：此触发器触发之后删除已经发生。因此， HashTags 中没有更多行可以加入！

您需要使用以下触发器：

  ALTER TRIGGER [dbo]。[HashTags_BeforeDelete] 
开[dbo]。[HashTags] 
 FOR删除
 AS 
开始
插入HashTagsArchive（Id，HashTagId，delete_date）
 SELECT 
 d.Id，d.HashTagId，GETUTCDATE（）
 FROM删除d 
 END 
 GO 
  

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

同样：删除发生后，此触发器将触发-因此您无需在触发器内部自己做任何事情-只需将这些信息插入到存档表中即可。其他所有内容都由SQL Server为您处理。

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 |

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

Example:

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

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

I tried this TRIGGER:

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

It's getting Deleted but no Inserted row in the HashTagsArchive

解决方案

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!

You need to use this trigger instead:

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

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

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.

这篇关于使用DELETE TRIGGER将删除的值插入DELETE之前的表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！