Get Even More Visitors To Your Blog, Upgrade To A Business Listing >>
Idioma: En

SQL Server After Delete Trigger with example

In this article, I am going to explain you AFTER Delete Trigger in SQL-Server with example. In the previous tutorial, I have explained introduction to triggers in SQL-Server. Please go through the links provided below.

Related Links

Introduction to triggers in SQL-Server.
After INSERT Trigger in SQL Server with example.

AFTER Delete Trigger (for delete) with Example

When we try to delete records from a Table then delete trigger is fired if trigger is associated with that table. After Delete trigger is also called as For Delete trigger. Now, let's implement after delete trigger.

Create a table Employee with four columns EmployeeId, Name, Age and gender. EmployeeId will be auto generated. Below is the code to create Employee table and insert some dummy records.

CREATE TABLE Employee
(
EmployeeId INT PRIMARYKEY IDENTITY,
Name VARCHAR(20),
Age INT,
Gender VARCHAR(10)
)
GO
INSERT INTO Employee VALUES('Mr. A',50,'Male')
INSERT INTO Employee VALUES('Mr. B',55,'Male')
INSERT INTO Employee VALUES('Mr. C',60,'Male')
INSERT INTO Employee VALUES('Mr. D',65,'Male')
INSERT INTO Employee VALUES('Mr. E',70,'Male')
GO

Now, execute the select query to check inserted records.

SELECT * FROM Employee

OUTPUT:
EmployeeId        Name    Age        Gender
1                             Mr. A      50           Male
2                             Mr. B     55           Male
3                             Mr. C     60           Male
4                             Mr. D     65           Male
5                             Mr. E     70           Male

Now, we will create a Audit Table for Employee table. When somebody delet record of Employee table, then we will insert same record in audit table by using after delete trigger. Below is the script to create Employee_Audit table.

CREATE TABLEEmployee_Audit
(
Id INT IDENTITYPRIMARY KEY,
EmployeeId INT,
Name VARCHAR(20),
Age INT,
Gender VARCHAR(10),
DeletedDate datetime
)

Creating AFTER Delete Trigger

Now, we will create after delete trigger. Below is the script to create after update trigger.

CREATE TRIGGERtr_Employee_AfterDelete   -- Trigger name

ON Employee    -- table name

AFTER DELETE   --(you can also use 'for delete')

AS
BEGIN

     DECLARE@EmployeeId INT,
             @Name VARCHAR(20),
             @Age INT,
             @Gender VARCHAR(20)

     SELECT@EmployeeId=d.EmployeeId,
            @Name=d.Name,
            @Age=d.Age,
            @Gender=d.Gender
            FROMdeleted AS d
    
       INSERTINTO Employee_Audit VALUES(@EmployeeId,@Name,@Age,@Gender,GETDATE()) 

END

When you delete a record of Employee table then SQL-Server creates a magic table named as 'deleted' and same copy of record will we inserted into this magic table. Deleted table is accessible inside the context of trigger. You can not access deleted table outside of triggers.

Executing After Delete Trigger

As we can not execute triggers explicitly, to execute trigger, we need to delete an record from the table. Execute below query to delete record from Employee table whose EmployeeId is 1.

DELETE FROM Employee WHERE EmployeeId = 1

As you can see below a record will be deleted for Employee table and a new will be inserted for Employee_Audit table.

SELECT * FROM Employee
SELECT * FROM Employee_Audit

Employee Table:
EmployeeId        Name    Age        Gender
2                             Mr. B     55           Male
3                             Mr. C     60           Male
4                             Mr. D     65           Male
5                             Mr. E     70           Male
Employee_Audit Table:
Id            EmployeeId      Name    Age        Gender   DeletedDate
1             1                            Mr. A     50           Male        2017-10-01 21:11:15.870

Now truncate both the tables and again insert same record (5 records) into Employee table. Now try to delete multiple records at single time. Execute below query to delete multiple records and select from both the tables.

DELETE FROM Employee WHERE EmployeeId in (1,2)
go
SELECT * FROM Employee
SELECT * FROM Employee_Audit

Employee Table:
EmployeeId        Name    Age        Gender
3                             Mr. C     60           Male
4                             Mr. D     65           Male
5                             Mr. E     70           Male

Employee_Audit Table:
Id            EmployeeId        Name    Age        Gender     DeletedDate
1             1                             Mr. A      50           Male         2017-10-01 21:23:45.653

As you can see from above output, two records are deleted from Employee table and only one record is inserted into Employee_Audit table because delete trigger fires only once per action.

Now to prevent from this, we will alter the procedure as shown below. We will directly use deleted table to insert into audit table.

ALTER TRIGGERtr_Employee_AfterDelete   -- Trigger name

ON Employee    -- table name

AFTER DELETE   --(you can also use 'for delete')

AS
BEGIN
     DECLARE@EmployeeId INT,
             @Name VARCHAR(20),
             @Age INT,
             @Gender VARCHAR(20)

     SELECT@EmployeeId=d.EmployeeId,
            @Name=d.Name,
            @Age=d.Age,
            @Gender=d.Gender
            FROMdeleted AS d

       INSERTINTO Employee_Audit(EmployeeID,Name,Age,Gender,DeletedDate)
      
       SELECTd.EmployeeID,d.Name,d.Age,d.Gender,GETDATE() FROM deleted d
      
END

Now truncate both the tables and again insert same record (5 records) into Employee table. Now try to delete multiple records at single time. Execute below query to delete multiple records and select from both the tables.

DELETE FROM Employee WHERE EmployeeId in (1,2)
go
SELECT * FROM Employee
SELECT * FROM Employee_Audit

Employee Table:
EmployeeId        Name    Age        Gender
3                             Mr. C     60           Male
4                             Mr. D     65           Male
5                             Mr. E     70           Male
Employee_Audit Table:
Id            EmployeeId        Name     Age        Gender     DeletedDate
1             2                             Mr. B       55           Male         2017-10-01 21:34:22.253
2             1                             Mr. A       50           Male         2017-10-01 21:34:22.253

Related Articles



This post first appeared on ASPArticles, please read the originial post: here

Share the post

SQL Server After Delete Trigger with example

×

Subscribe to Asparticles

Get updates delivered right to your inbox!

Thank you for your subscription

×