How to create delete trigger in SQL Server?

How to create delete trigger in SQL Server?

Hello Everyone, I hope you are doing well.

SQL SERVER
SQL SERVER

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

How to whitelist website on AdBlocker?

How to whitelist website on AdBlocker?

  1. 1 Click on the AdBlock Plus icon on the top right corner of your browser
  2. 2 Click on "Enabled on this site" from the AdBlock Plus option
  3. 3 Refresh the page and start browsing the site
%d bloggers like this: