Open In App

Capturing Deleted Rows in SQL Server Without Using a Trigger

Improve
Improve
Like Article
Like
Save
Share
Report

Introduction :

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.

Example :
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

Output –

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

Output –

GeekTab1 

Id1 Name1
2 Komal

GeekTab2

Id2 Name2
1 Khushi

Conclusion :
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.


Last Updated : 13 Apr, 2021
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads