How To Use Triggers In SQL

In this article, we are going to learn how to use Trigger in SQL.

A Trigger is a piece of procedural code, like a stored procedure or stored program that executes automatically whenever an event such as Insert, Update and Delete occurs against a table.

Syntax

CREATE TRIGGER [trigger_name] 
[before | after]  
{insert | update | delete}  
ON [table_name]  
[for each row]  
[trigger_body]

In this article we are going to create Insert, Update and Delete Trigger.

First of all we are going to create a table for which we will create a trigger and insert some records into it.

CREATE TABLE Student_Test  
(  
Student_ID INT Identity,  
Student_name Varchar(100),  
Student_Marks Decimal (10,2)  
)  

INSERT INTO Student_Test VALUES ('Seema',79);  
INSERT INTO Student_Test VALUES ('John',85);  
INSERT INTO Student_Test VALUES ('Stephen',63);  
INSERT INTO Student_Test VALUES ('Maria',98); 

Create another table which is going to store all transaction records of Student_Test Table such as Insert, Update and Delete.

CREATE TABLE Student_Test_Audit  
(  
Student_ID INT,  
Student_name VARCHAR(100),  
Student_Marks DECIMAL(10,2),  
Audit_Action VARCHAR(100),  
Audit_Timestamp datetime  
)  

Trigger for Insert

CREATE TRIGGER trgAfterInsert ON [dbo].[Student_Test]   
FOR INSERT  
AS  
DECLARE @studentid INT ;  
DECLARE @studentname VARCHAR (100);  
DECLARE @studentmarks DECIMAL (10,2);  
DECLARE @audit_action VARCHAR (100);  
SELECT @studentid=i.Student_ID FROM inserted i;   
SELECT @studentname=i.Student_name FROM inserted i;   
SELECT @studentmarks=i.Student_Marks FROM inserted i;   
SET @audit_action='Inserted Record -- After Insert Trigger.';  
  
INSERT INTO Student_Test_Audit  
(Student_ID,Student_name,Student_Marks ,Audit_Action,Audit_Timestamp)   
VALUES(@studentid,@studentname,@studentmarks,@audit_action,getdate());  
  
PRINT 'After INSERT trigger fired.'  
GO  

Now Insert Record in Student_Test Table.

INSERT INTO Student_Test VALUES('Rahul',89);

Trigger for Update

CREATE TRIGGER trgAfterUpdate ON [dbo].[Student_Test]   
FOR UPDATE 
AS  
DECLARE @studentid INT ;  
DECLARE @studentname VARCHAR (100);  
DECLARE @studentmarks DECIMAL (10,2);  
DECLARE @audit_action VARCHAR (100);  
SELECT @studentid=i.Student_ID FROM inserted i;   
SELECT @studentname=i.Student_name FROM inserted i;   
SELECT @studentmarks=i.Student_Marks FROM inserted i;   
SET @audit_action='Update Record -- After Update Trigger.';  
  
INSERT INTO Student_Test_Audit  
(Student_ID,Student_name,Student_Marks ,Audit_Action,Audit_Timestamp)   
VALUES(@studentid,@studentname,@studentmarks,@audit_action,getdate());  
  
PRINT 'After UPDATE trigger is fired.'  
GO  

Now Update Record in Student_Test Table.

Update Student_Test SET Student_name= 'Hafeezjaha', Student_Marks = 99 where Student_ID=6 ;

Trigger for Delete

CREATE TRIGGER trgAfterDelete ON [dbo].[Student_Test]   
FOR DELETE
AS  
DECLARE @studentid INT ;  
DECLARE @studentname VARCHAR (100);  
DECLARE @studentmarks DECIMAL (10,2);  
DECLARE @audit_action VARCHAR (100);  
SELECT @studentid=d.Student_ID FROM deleted d;   
SELECT @studentname=d.Student_name FROM deleted d;   
SELECT @studentmarks=d.Student_Marks FROM deleted d;   
SET @audit_action='Delete Record -- After Delete Trigger.';  
  
INSERT INTO Student_Test_Audit  
(Student_ID,Student_name,Student_Marks ,Audit_Action,Audit_Timestamp)   
VALUES(@studentid,@studentname,@studentmarks,@audit_action,getdate());  
  
PRINT 'After DELETE trigger is fired.'  
GO  

Now Delete Record in Student_Test Table.

DELETE FORM Student_Test where Student_ID=6 ;

You can see records in Student_Test_Audit Table.

SELECT * FROM Student_Test_Audit

Here you can see the output:

Please give your valuable feedback and if you have any questions or issues about this article, please let me know.

Also, check Find Specific Text String In Stored Procedures, Functions, Views And Triggers In SQL Server