INSERT deleted values into a table before DELETE with a DELETE TRIGGER
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.
Comments
-
Ofir Hadad over 3 years
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 aDELETE
query.Example:
DELETE FROM [dbo].[HashTags] WHERE Id=1
After this example I should have the deleted row in
dbo.HashTagsArchive
and the row withId=1
should be deleted indbo.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 noInserted
row in theHashTagsArchive