How to create delete trigger in SQL Server?

Hello Everyone, I hope you are doing well.

Today, I will be explaining on how to create the Delete Triggers in Sql Server. We all at some point would require this as someone might delete the data from the table that is of high priority. Here, I am assuming here 2 tables, one “Student” and another “Student_Restored”. So any records deleted from the Student table, will be created in Student_Restored.

Lets create a database and switch to it

CREATE DATABASE SomeDb
GO 
USING SomeDB
GO

Now create a table “STUDENT”

CREATE TABLE STUDENT
(
	ID INT PRIMARY KEY NOT NULL IDENTITY(1,1),
	NAME NVARCHAR(50) NOT NULL, 
	EMAIL NVARCHAR(50) NULL
)

Lets create a new one “STUDENT_RESTORED”

CREATE TABLE STUDENT_RESTORED
(
	ID INT PRIMARY KEY NOT NULL IDENTITY(1,1),
	NAME NVARCHAR(50) NOT NULL,
	EMAIL NVARCHAR(50) NULL
)

Lets create a trigger

CREATE TRIGGER deleteTrigger 
ON STUDENT
AFTER DELETE
AS
DECLARE @name NVARCHAR(50)
DECLARE @email NVARCHAR(50)

SELECT @name= DEL.NAME FROM DELETED DEL
SELECT @email = DEL.EMAIL FROM DELETED DEL
BEGIN
	INSERT INTO STUDENT_RESTORED 
	(NAME,EMAIL)
	VALUES
	(@name,@email)
END

Now lets create some records and try to delete it from “STUDENT” table

INSERT INTO STUDENT 
(NAME, EMAIL) 
VALUES 
('Chandan','chandan.bhagat@outlook.com'),
('Ram', 'ram@gmail.com')

Now, Query it out using the following sql

SELECT * FROM STUDENT
SELECT * FROM STUDENT_RESTORED

Lets delete a record

DELETE FROM STUDENT WHERE NAME = 'Chandan'

Lets check the record once again

SELECT * FROM STUDENT
SELECT * FROM STUDENT_RESTORED

However, this method only catches a single deletion. If you are dealing with multiple deletion, the top one will only be recorded in another db. Lets modify the code and try out again.