Sameer Gaikwad The CREATE TRIGGER INSERT, DELETE, or UPDATE statement

Follow me Sameer Gaikwad then follow the link below! 👉 💯day challenge 📩 Reach out to me on Twitter or Linkedin, or Blogspot if you want to discuss this further. :)

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.

check out my blog post

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. check out my blog post

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. check out my blog post

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,

1. CREATE TRIGGER <Trigger_Name>
2. ON <Table_Name>
3. FOR INSERT|DELETE|UPDATE
4. AS
5. BEGIN
6. <Trigger Body>
7. END

We will see it practically, so first open SQL Server Management Studio and take a new worksheet and create a new database ‘sameer’, which is shown below. check out my blog post

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

1. CREATE table employee (
2. Emp_id INT IDENTITY(1,1) PRIMARY KEY,
3. first_name varchar(20),
4. last_name varchar(20),
5. address_ varchar(20),
6. );

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’

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

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.

1. insert into employee(first_name,last_name,address_) values
2. (‘Ashish’,’ Randhir’,’ Delhi’),
3. (‘Sameer’,’ Gaikwad’,’ Mumbai’),
4. (‘Satish’,’ Rathore’,’ Pune’);

When you execute the below select command,

1. select * from employee;

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

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

1. CREATE TRIGGER Trg_employee_Insert
2. ON employee
3. FOR INSERT
4. AS
5. BEGIN
6. Declare @Audit_Id INT
7. SELECT @Audit_Id= Emp_id from inserted
8. INSERT into Employee_Audit(Audit_Data)
9. VALUES(‘New employee with Eployee Id = ‘ + CAST(@Audit_Id AS NVARCHAR(50)) +’ is added at ‘ + CAST(GETDATE() AS NVARCHAR(150)));
10. END

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

1. insert into employee(first_name,last_name,address_) values (‘Rohit’,’Kadam’,’Nashik’);

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

1. select * from Employee_Audit;

It will show the following result:

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

1. CREATE TRIGGER Trg_employee_Update
2. ON employee
3. FOR UPDATE
4. AS
5. BEGIN
6. Declare @Audit_Id INT,@first_name VARCHAR(25),@last_name VARCHAR(25),@address_ VARCHAR(25)
7. SELECT @Audit_Id= Emp_id,@first_name= first_name,@last_name= last_name,@address_= address_ from inserted
8. SELECT @Audit_Id= Emp_id,@first_name= first_name,@last_name= last_name,@address_= address_ from deleted
9. INSERT into Employee_Audit(Audit_Data)
10. VALUES(‘Record with Eployee Id = ‘ + CAST(@Audit_Id AS NVARCHAR(50)) +’ is changed. Old Data was’+’ First Name = ‘ + CAST(@first_name AS NVARCHAR(50))
11. +’, Last_Name = ‘ + CAST(@last_name AS NVARCHAR(50))+’, address_ = ‘ + CAST(@address_ AS NVARCHAR(50)) +’ at ‘ + CAST(GETDATE() AS NVARCHAR(150)));
12. END

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

1. 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.

1. select * from Employee_Audit;

It will show the following result:

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

1. CREATE TRIGGER Trg_employee_Delete
2. ON employee
3. FOR DELETE
4. AS
5. BEGIN
6. Declare @Audit_Id INT
7. SELECT @Audit_Id= Emp_id from deleted
8. INSERT into Employee_Audit(Audit_Data)
9. VALUES(‘Employee with Eployee Id = ‘ + CAST(@Audit_Id AS NVARCHAR(50)) +’ is removed at ‘ + CAST(GETDATE() AS NVARCHAR(150)));
10. END

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

1. delete from employee where Emp_id=’1';

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

1. select * from Employee_Audit;

It will show the following result.

Summary

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

#sameergaikwadbymesameergaikwad #sameergaikwadcrudoperation #sameergaikwadmvccrudoperation #sameergaikwadjoinsinsqlserver #sameergaikwadsqlstoredprocedure #sameergaikwadsqltolinq

sameer gaikwad crud operation Stored Procedure — mesameergaikwad blog

Sameer Gaikwad CRUD Operations Using LINQ to SQL In ASP.NET

Sameer Gaikwad CRUD Operation Using LINQ to SQL CREATE, READ, UPDATE, DELETE ASP.NET

Sameer Gaikwad CRUD Operations Using LINQ To SQL In ASP.NET

Sameer Gaikwad CRUD Operations In ASP.NET MVC Using ADO.NET

Sameer Gaikwad CRUD Operation Using Stored Procedure In ASP.NET