How to Capture Deleted Rows Without Trigger? – Interview Question of the Week #297

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)