INSERT deletes values ​​in a table before DELETE with DELETE TRIGGER

Question

For some reason, I cannot find the exact answer that I need. I searched for the last 20 minutes here.

I know this simply. Very simple. But I can not start the trigger for some reason.

I have a table with two columns

dbo.HashTags

|__Id_|_name_| | 1 | Love | 

I want to insert deleted values ​​into another table named dbo.HashTagsArchive in 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 gets a Deleted string, but not Inserted in a HashTagsArchive

Answer 1

Your problem: this trigger fires AFTER the deletion has already occurred . Thus, in HashTags no longer a line that you could join!

Instead, you need to use this trigger:

 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 have been deleted - no need to join anything ...

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