Triggers are often used to log all the data that has been deleted from key tables in SQL Server. However, triggers can negatively impact server performance and add additional overhead to available resources. If you’re looking for an alternative solution to capture deleted rows without using triggers, there is a way to achieve this.
Instead of using triggers, you can utilize the OUTPUT clause in the DELETE statement to record the deleted rows. This approach provides a more efficient and lightweight solution. Let’s take a look at an example:
-- 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 and inserting into 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 the above example, the OUTPUT clause is used in the DELETE statement to capture the deleted rows from the MainTab table. The deleted rows are then inserted into the DeletedTab table. This allows you to keep a record of the deleted rows without the need for triggers.
By using the OUTPUT clause, you can achieve the same result as a trigger without the performance impact and resource overhead. It’s important to note that this solution is not a 100% replacement for triggers, but it can be a suitable alternative if you only need to record deleted rows.
The OUTPUT clause can also be used with the INSERT statement to capture the inserted rows. If you’re interested in learning more about the OUTPUT clause and its capabilities, you can check out my blog post and SQL in Sixty Seconds video on the topic.
Thank you for reading! Let me know if you have any questions or if there are any other SQL Server topics you’d like me to cover in future blog posts or videos.