Capturing Deleted Rows in SQL Server Without Using a Trigger

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 –

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 

GeekTab2

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.