What is a Trigger? What Are the Types of Triggers? How to Display Information About Events - Part Two

Introduction

 

In this article, we will see what is DDL trigger and how to create one. We will also see how to store the audit data when a DML event occurs. 

 

Previous Article link.

 

Let’s begin…..

 

What is a  DML trigger?

 

DML triggers run when a user tries to modify data through a data manipulation language (DML) event. DML events are INSERT, UPDATE, or DELETE statements. DML triggers can be used to enforce business rules and data integrity. DML trigger uses two special tables.

1. The Inserted table 
2. The Deleted table

SQL Server automatically creates and manages these tables. It can be used to capture details of the data modifications and to set conditions for DML trigger actions.

 

The inserted table stores copies of the affected rows during INSERT and UPDATE statements. While executing the insert or update query, new rows are added to both the inserted table and the trigger table. The rows in the inserted table are copies of the new rows in the trigger table.

 

The deleted table stores copies of the affected rows during DELETE and UPDATE statements. During the execution of a DELETE or UPDATE statement, rows are deleted from the trigger table and transferred to the deleted table.

 

An update transaction is similar to a delete operation followed by an insert operation; the old rows are copied to the deleted table first, and then the new rows are copied to the trigger table and to the inserted table.

 

To create DML triggers use the following syntax,

CREATE TRIGGER <Trigger_Name>
ON <Table_Name>
FOR INSERT|DELETE|UPDATE
AS
BEGIN
<Trigger Body>
END

We will see it practically, so first open SQL Server Management Studio and take new worksheet and create a new database ‘DEMOS’, which is shown below.

 

 

Now create a table as 'employee', on which we will perform our DML operations.

CREATE table employee (
Emp_id INT IDENTITY(1,1) PRIMARY KEY,
first_name varchar(20),
last_name varchar(20),
address_ varchar(20),
);

Suppose you want to capture all the modifications made into the ‘employee’ table, then you require one more table to store that audit data. To do that, create a new table named ‘Employee_Audit’

Create table Employee_Audit(
Audit_Id INT IDENTITY(1,1) PRIMARY KEY,
Audit_Data NVARCHAR(150)
);

Now that the ‘employee’ table and ‘Employee_Audit’ are created, when you refresh 'Databases' in the object explorer window, you can see it. Alternatively, you can see it by executing the select command.

 

 

Next, insert a few records into the ‘employee’ table.

insert into employee(first_name,last_name,address_) values
('Rahul','Randhir','Delhi'),
('Rajesh','Raut','Mumbai'),
('Rutuja','Rathore','Pune');

When you executes the below select command,

select * from employee;

It shows three records inserted into the ‘employee’ table.

 

 

Now create an insert trigger on the ‘employee’ table as shown below.

CREATE TRIGGER Trg_employee_Insert
ON employee
FOR INSERT
AS
BEGIN
Declare @Audit_Id  INT
SELECT @Audit_Id= Emp_id from inserted
INSERT into Employee_Audit(Audit_Data)
VALUES('New employee with Eployee Id = ' + CAST(@Audit_Id AS NVARCHAR(50)) +' is added at ' + CAST(GETDATE() AS NVARCHAR(150)));
END

When you execute the above query, it creates the ‘Trg_employee_Insert’ trigger. Now execute the below insert query.

insert into employee(first_name,last_name,address_) values ('Rohit','Rane','Nashik');

To ensure the ‘Trg_employee_Insert’ trigger is invoked or not, execute the below query.

select * from Employee_Audit;

It will show the following result:

 

 

Now create an update trigger on the ‘employee’ table as shown below:

CREATE TRIGGER Trg_employee_Update
ON employee
FOR UPDATE
AS
BEGIN
Declare @Audit_Id  INT,@first_name VARCHAR(25),@last_name VARCHAR(25),@address_ VARCHAR(25)
SELECT @Audit_Id= Emp_id,@first_name= first_name,@last_name= last_name,@address_= address_ from inserted
SELECT @Audit_Id= Emp_id,@first_name= first_name,@last_name= last_name,@address_= address_ from deleted

INSERT into Employee_Audit(Audit_Data)
VALUES('Record with Eployee Id = ' + CAST(@Audit_Id AS NVARCHAR(50)) +' is changed. Old Data was'+' First Name = ' + CAST(@first_name AS NVARCHAR(50))
+', Last_Name = ' + CAST(@last_name AS NVARCHAR(50))+', address_ = ' + CAST(@address_ AS NVARCHAR(50)) +' at ' + CAST(GETDATE() AS NVARCHAR(150)));
END

When you execute the above query, it creates the ‘Trg_employee_Update’ trigger. Now just execute the below update query.

Update  employee set first_name='Rohini',last_name='Jagtap', address_='Nagpur'where Emp_id='2';

To ensure the ‘Trg_employee_Update’ trigger is invoked or not, execute the below query.

select * from Employee_Audit;

It will show the following result:

 

 

Now create a delete trigger on ‘employee’ table as shown below:

CREATE TRIGGER Trg_employee_Delete
ON employee
FOR DELETE
AS
BEGIN
Declare @Audit_Id  INT
SELECT @Audit_Id= Emp_id from deleted
INSERT into Employee_Audit(Audit_Data)
VALUES('Employee with Eployee Id = ' + CAST(@Audit_Id AS NVARCHAR(50)) +' is removed at ' + CAST(GETDATE() AS NVARCHAR(150)));
END

When you execute the above query, it creates the ‘Trg_employee_Delete’ trigger. Now just execute the below delete query.

delete from employee where Emp_id='1';

To ensure the ‘Trg_employee_Delete’ trigger is invoked or not, execute the below query.

select * from Employee_Audit;

It will show the following result.

 

 

Summary

 

In this article, we learned what about the DML trigger and how to create one. We also saw how to capture the DML event information when the trigger is fired.