Question: How to Capture Deleted Rows Without Trigger?
Answer: I was recently asked by my client during the Comprehensive Database Performance Health Check if there is any way to capture the deleted row without a trigger. The answer is yes.
If you have worked with me during any consulting engagement or if you have attended any of my SQL Server Performance Tuning class, you might be familiar with my opinion about triggers. I do not like triggers and I have done my best in my career to not use them – not a single one of them. Triggers often kill the performance of the server and also add additional overhead on various available resources.
I often see with my clients that they use triggers to log all the data which have been deleted from their key tables. This actually adds lots of overhead. Here is another solution that you MAY consider instead of the triggers.
Note: Please note the solution which I am suggesting is not a 100% replacement of the after trigger for delete. However, it is totally possible for you to use that instead of the trigger if you are just looking to record deleted rows.
-- Creating two tables CREATE TABLE MainTab (ID1 INT, Col1 VARCHAR(100)) GO CREATE TABLE DeletedTab (ID3 INT, Col3 VARCHAR(100)) GO -- Inserting into two tables together INSERT INTO MainTab (ID1, Col1) VALUES(1,'Col'), (2, 'Col2') GO -- Deleting from Main Table -- Inserting in Deleted Table DELETE FROM MainTab OUTPUT deleted.ID1, deleted.Col1 INTO DeletedTab WHERE ID1 = 1 GO --Selecting from both the tables SELECT * FROM DeletedTab; SELECT * FROM MainTab; -- Clean up DROP TABLE MainTab; DROP TABLE DeletedTab; GO
In this example, you can see the OUTPUT clause in the DELETE statement. It will now record the statement which is deleted from the MainTab statement. Here is the output that looks like before and after the DELETE with OUTPUT statement.
Well, there you go, you can now record the deleted rows without using the trigger. The OUTPUT clause works with INSERT statement as well and here I have blogged about it and also created SQL in Sixty Seconds video.
Let me know if you would like me to build a video on the topic of this blog post.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
2 Comments. Leave new
Hi Pinal,
Very nicely explained.
Looking for another article on CDC feature to capture the same as normally application doesn’t delete data in this way of query. Tq as posting all good learning articles.
2005