Capturing Deleted Rows in SQL Server Without Using a Trigger
Working with triggers can be difficult and time-consuming at times. Triggers can slow down the server’s output and place additional pressure on the system’s resources. Many people use triggers to keep track of all the data that has been removed from tables. Instead of using triggers, we might use another process, as seen in the example below. Notice that the following arrangement is not a substitute for the delete trigger. On the other hand, if we just want to record deleted rows, we might use that instead of the trigger.
Let us create two tables –
CREATE TABLE GeekTab1 (Id1 INT, Name1 VARCHAR(100)) GO CREATE TABLE GeekTab2 (Id2 INT, Name2 VARCHAR(100)) GO
Insert sample data into the table –
INSERT INTO GeekTab1 (Id1, Name1) VALUES(1,'Khushi'), (2, 'Komal') GO
Select data from the table –
Select * from GeekTab1 GO
Id1 Name1 1 Khushi 2 Komal
Delete from GeekTab1 and Insert in GeekTab2 –
DELETE FROM GeekTab1 OUTPUT deleted.Id1, deleted.Name1 INTO GeekTab2 WHERE Id1 = 1 GO
Select data from both the tables –
SELECT * FROM GeekTab1; GO SELECT * FROM GeekTab2; GO
Id1 Name1 2 Komal
Id2 Name2 1 Khushi
The OUTPUT clause is used in the DELETE statement in the above case, and it has documented the statement that is deleted from the GeekTab1.