What are SQL-Trigger

In this article we will learn about Sql triggers which automatically triggers the corresponding event (DML/DDL) statement that is occurred in the database.

To understand the working of sql triggers kindly follow the points given below:

1. Introduction :
• Sql Triggers are the database system object which’s automatically shoots when corresponding event (DML/DDL) statement is occurred in database.
• In this article I have explained the usages DML database triggers.
• Following are the Action of DML Triggers that execute itself when DML statement is performed on its associated table:
1. For Insert
2. For Update
3. For Delete
2. Usages of Trigger :
• Trigger is very useful to track the data manipulation in tables.
• It is useful to maintain data history in tables.
• No need to run the job scheduler. It automatically fire’s when corresponding event occur in tables.
• Trigger is very useful to maintain the data integrity in tables
3. DML Triggers :
• DML Triggers execute itself when DML (Insert/Update/Delete) statement event occurs in database.
1. It performs action in database according to behaviour of data modification. For example after or before data modification.
2. DML Trigger provides data consistency, the entire records rollback if any server error detect.
3. DML trigger perform action according to its virtual table which are called inserted and deleted. These tables store the value of before and after data modification.

To understand the behaviours of these table let’s see following figure 1.

DML Triggers Examples:

• You can see, salary audit table is empty. Now we perform Some DML Statement in Salary Table.
• The DML trigger that associated with Salary table will fire automatically when corresponding DML event will fire.
• Trigger hold modified data in virtual table (Deleted/Inserted) and store the record in Salary Audit Table.
• It inserts new records in salary audit table for insert update and deletes action in main salary table.

CREATE TABLE [dbo].[salary](
	[Employee_id] [int] IDENTITY(1,1) NOT NULL,
	[Employee_code] [char](6) NOT NULL,
	[Name] [varchar](50) NULL,
	[Date_of_Birth] [date] NULL,
	[Basic_Pay] [bigint] NULL,
	[Position] [varchar](50) NULL,
 CONSTRAINT [PK_salary] PRIMARY KEY CLUSTERED 
(
	[Employee_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[salary_audit](
	[Employee_id] [int] IDENTITY(1,1) NOT NULL,
	[Employee_code] [char](6) NOT NULL,
	[Name] [varchar](50) NULL,
	[Date_of_Birth] [date] NULL,
	[Basic_Pay] [bigint] NULL,
	[Position] [varchar](50) NULL,
	[changeDate] [date] NOT NULL DEFAULT (getdate()),
 CONSTRAINT [PK_salary_audit] PRIMARY KEY CLUSTERED 
(
	[Employee_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

DML Triggers:

• For Insert (execute when insert statement is occur)
• For Update (execute when update statement is occur)
• For Delete (execute when Delete statement is occur)

Trigger itself add a new records in salary audit table for every changes in salary table changes like (insert update and delete).

So now let’s start with creating DML triggers.

1. For Insert:

CREATE TRIGGER Salary_InsertTrigger
ON salary
FOR INSERT
AS
BEGIN
   INSERT INTO salary_audit (Employee_code,Name,Date_of_Birth,Basic_Pay,Position )
   SELECT Employee_code,Name,Date_of_Birth,Basic_Pay,Position
   FROM inserted;
END;
GO

2. For Update:

CREATE TRIGGER Salary_UpdateTrigger
ON salary
FOR UPDATE

AS
BEGIN
  INSERT INTO salary_audit (Employee_code,Name,Date_of_Birth,Basic_Pay,Position )
   SELECT Employee_code,Name,Date_of_Birth,Basic_Pay,Position
   FROM deleted;
  
   INSERT INTO salary_audit (Employee_code,Name,Date_of_Birth,Basic_Pay,Position )
    SELECT Employee_code,Name,Date_of_Birth,Basic_Pay,Position
   FROM inserted;
END;

3. For Delete:

CREATE TRIGGER Salary_DeleteTrigger
ON salary
FOR DELETE
AS
BEGIN
  INSERT INTO salary_audit (Employee_code,Name,Date_of_Birth,Basic_Pay,Position )
 
   SELECT Employee_code,Name,Date_of_Birth,Basic_Pay,Position
   FROM deleted;
END;
GO

Let test DML trigger:

• Here I am going to execute the Update statement in salary table figure 1.
• You can see that NAND KISHORE whose employee code is 065dg Earning 5000 per month lets change his salary to 10000.
• There’s no need to worry about salary audit table.
• The data go in audit table automatically when corresponding event will occur.

Let make update query in salary table now :

update salary set Basic_Pay =10000 where employee_code='065dg' 

Let’s check output:


• This will change the basic pay of NAND KISHORE to 10000.
• . And in case you want to find previous basic pay NAND KISHORE you can check that in salary Audit Table.
• Salary audit table hold the previous state of salary table.

Output: Now you can see salary audit table hold all the before and after modification.

You can see previous basic pay was 5000 and latest basic pay is 10000.

Thus, we learn about Sql triggers and implemented DML Triggers along with examples for Insert/Delete/Update statement.