Introduction

The trigger is a method provided by SQL Server to programmers and data analysts to ensure data integrity. It is a special stored procedure related to table events. Its execution is not called by the program or started manually but triggered by events. For example, when a table is operated on with an INSERT, DELETE, or UPDATE, the trigger will be fired for execution. Triggers are often used to strengthen data integrity constraints and business rules. SQL Server includes three general types of triggers: DML trigger, DDL trigger, and logon trigger. This article focuses on DML triggers.

DML triggers contain code attached to specific tables or views, which are executed when a DML event occurs in the database server. In this article, I use the AFTER trigger to design the three frequently-used DML triggers in SQL Server:

1. Insert operation trigger: triggered after inserting data into a table
2. Update operation trigger: triggered after modifying the data in the table
3. Delete operation trigger: triggered after data is deleted from the table.

DML triggers should be considered when you need to handle any of these situations:

1. Cascading changes through related tables in the database.
2. Preventing malicious or erroneous insert, update, and delete operations.
3. Enforcing other restrictions that are more complex than those defined by the check constraint.
4. Evaluate the status of the table before and after a data modification and take measures according to the difference.

The following figure is cited to show how these DML triggers work in a simple and clear manner:

DML trigger statements use two special tables: the deleted and inserted tables. The inserted table stores copies of the new or changed rows after an INSERT or UPDATE statement. During the execution of an INSERT or UPDATE statement, the new or changed rows in the trigger table are copied to the inserted table. The rows in the inserted table are copies of the new or updated rows in the trigger table. The deleted table stores copies of the affected rows in the trigger table before they were changed by a DELETE or UPDATE statement (the trigger table is the table on which the DML trigger runs). During the execution of a DELETE or UPDATE statement, the affected rows are first copied from the trigger table and transferred to the deleted table.

Refer to Microsoft's guide: Use the inserted and deleted tables

Sample

Let me use two sample tables “Person” and “PersonLog” to explain how the trigger works. Here is the creation script for these two tables:

CREATE TABLE [dbo].[Person](
[ID] [int] NOT NULL,
[Name] [varchar](50) NULL,
[Age] [int] NULL,
 CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED 
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[PersonLog](
[PersonID] [int] NOT NULL,
[Name] [varchar](50) NULL,
[Age] [int] NULL,
[AddDate] [datetime] NULL,
[UpdateDate] [datetime] NULL,
[DeleteDate] [datetime] NULL
) ON [PRIMARY]
GO

I use the table "Person" to store the data and I use the table "PersonLog" to store the information of insert, update and delete on the table "Person":

Insert operation trigger:

In the following code, I created a trigger for the INSERT operation, once after executing the "INSERT " SQL statement if the inserted age value is less than 130, and inserted data with its operation date will be added to the PersonLog table. If the inserted age value is greater than or equal to 130, the transaction will be rolled back and the inserted data with its operation date as "AddDate" will not be added to the PersonLog table. Here is the creation script of the Insert operation trigger:

--Insert trigger
Create TRIGGER [dbo].[Trigger_Insert]
   ON  [dbo].[Person]
   AFTER INSERT
AS 
BEGIN

SET NOCOUNT ON;
Declare @age int;
Select @age=Age  From inserted
--If the age is less than 130, normal insertion, otherwise data rollback
IF(@age<130)
Begin
Insert into PersonLog(PersonID, Name, Age, AddDate)
Select ID, Name, Age, GETDATE() From inserted
End
ELSE
Begin
    print('the age should be less than 130')
rollback transaction     --data rollback
END
    
END
GO
ALTER TABLE [dbo].[Person] ENABLE TRIGGER [Trigger_Insert]
GO

Testing case 01: When I inserted a row with the value of “Age” over 130 it was aborted and printed the message "the age should be less than 130":

insert into [DBInventory].[dbo].[Person] ([ID],[Name],[Age]) VALUES ('1101','Ted','140')

But when I inserted the rows with the value of “Age” less than 130, the insert action succeeded, and the logged timestamp of action as "AddDate" into the PersonLog table:

insert into [DBInventory].[dbo].[Person] ([ID],[Name],[Age]) VALUES ('1102','Vector','25')
insert into [DBInventory].[dbo].[Person] ([ID],[Name],[Age]) VALUES ('1103','Krik','35')
insert into [DBInventory].[dbo].[Person] ([ID],[Name],[Age]) VALUES ('1104','Leo','37')
insert into [DBInventory].[dbo].[Person] ([ID],[Name],[Age]) VALUES ('1105','Ace','40')
SELECT * FROM [DBInventory].[dbo].[Person]
SELECT * FROM [DBInventory].[dbo].[PersonLog]

Update operation trigger:

In the following code, I created a trigger for the UPDATE operation, once after executing the "UPDATE" SQL statement, it will temporarily store the existing rows in the PersonLog table according to the updated rows in the Person table to a temp table #pel then it will remove them from PersonLog table. At last, it will add the value of the updated rows with its operation date as "UpdateDate" and the "AddDate" in the temp table #pel to the PersonLog table. Here is the creation script of the Update operation trigger:

--Update trigger
Create TRIGGER [dbo].[Trigger_Update]
   ON  [dbo].[Person]
   AFTER UPDATE
AS 
BEGIN
  SET NOCOUNT ON;
  SELECT PersonID,AddDate into #pel from PersonLog
  DELETE FROM [PersonLog] WHERE [PersonID] in (SELECT ID FROM inserted)
  
  INSERT INTO [PersonLog] ([PersonID],[Name],[Age],[AddDate],[UpdateDate]) SELECT [ID],[Name],[Age],#pel.AddDate,GETDATE() FROM inserted LEFT JOIN #pel on inserted.[ID] = #pel.PersonID
drop table #pel
END
GO
ALTER TABLE [dbo].[Person] ENABLE TRIGGER [Trigger_Update]
GO

Testing case 02: When the "Age" value of  rows whose ID is 1104 or 1105 was updated in the table Person, the logged timestamp of the update statement as "UpdateDate" and the new value of "Age"  were updated in the PersonLog table:

update [DBInventory].[dbo].[Person] set Age = '39' where ID ='1104' or ID ='1105'
SELECT * FROM [DBInventory].[dbo].[Person]
SELECT * FROM [DBInventory].[dbo].[PersonLog]

Delete operation trigger:

In the following code, I created a trigger for the DELETE operation, once after executing the "DELETE" SQL statement, it will temporarily store the existing rows in the PersonLog table according to the deleted rows in the Person table to the temp table #pel then it will remove them from PersonLog table. At last, it will add the operation date as "DeleteDate" and the "AddDate" and "UpdateDate" in the temp table #pel to the PersonLog table Here is the creation script of the Delete operation trigger:

--Delete trigger
Create TRIGGER [dbo].[Trigger_Delete]
   ON  [dbo].[Person]
   AFTER DELETE
AS 
BEGIN
SET NOCOUNT ON;
SELECT PersonID,AddDate,UpdateDate into #pel from PersonLog
DELETE FROM [PersonLog] WHERE [PersonID] in (SELECT ID FROM deleted)
INSERT INTO [PersonLog] ([PersonID],[Name],[Age],[AddDate],[UpdateDate],[DeleteDate]) SELECT [ID],[Name],[Age],#pel.AddDate,#pel.UpdateDate,GETDATE() FROM deleted LEFT JOIN #pel on deleted.[ID] = #pel.PersonID
drop table #pel
END
GO
ALTER TABLE [dbo].[Person] ENABLE TRIGGER [Trigger_Delete]
GO

Testing case 03:When I deleted the rows record of "Name" are "Krik" and "Leo", and the logged timestamp of the delete statement as "DeleteDate" into the PersonLog table:

delete from [DBInventory].[dbo].[Person] where name ='Krik' or name ='Leo'
SELECT * FROM [DBInventory].[dbo].[Person]
SELECT * FROM [DBInventory].[dbo].[PersonLog]

Summary

Here are my suggestions for Triggers: Try to avoid time-consuming operations in the trigger, because the trigger and the SQL statement will think that they are in the same transaction, and the lock cannot be released until the transaction ends. Avoid complex operations in triggers. There are many factors that affect the performance of triggers (eg: product version, used architecture, etc.). It is difficult to write high-performance triggers because there are many factors to consider in writing efficient triggers. When writing triggers, pay attention to the processing when multiple lines are triggered (cursors are generally not recommended).