I. what is trigger

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 programs or started manually, but triggered by events. (--- from Baidu Encyclopedia)

Simple understanding, trigger is when you do some specific actions to the database (such as updating, deleting a table), automatically make feedback (such as recording your changes, time, etc.).

The function of trigger is as follows (- --- from Baidu Encyclopedia):

1. You can force verification or conversion of data before writing it to a data table.
2. When an error occurs in the trigger, the result of the change is undone.
3. Some database management systems can use triggers for data definition language (DDL), which is called DDL triggers.
4. The changed instruction (INSTEAD OF) can be replaced according to the specific situation.

In short, the designer can set some protection rules for the use of database according to the actual situation. When the user touches these rules, the trigger will give corresponding feedback.

II. Trigger classification

DML triggers are operation codes based on the contents of tables in the database, which are executed when data operation language events occur in the database.

The LOGIN trigger fires the stored procedure in response to the LOGIN event. This event is raised when a user session is established with an SQL Server instance. The LOGIN trigger fires after the authentication phase of the LOGIN and before the user session is actually established. As a result, all messages from within the trigger that normally reach the user, such as error messages and messages from PRINT statements, are delivered to the SQL Server error log. If authentication fails, the LOGIN trigger is not fired.

DDL triggers are based on certain changes to the data schema or changes or event actions in the recorded data.

3. An example

1. Example of DML trigger

DML triggers need to pay attention to two tables: inserted table and deleted table. These two tables are automatically created by the system, recording the record rows after inserting or updating, before deleting or updating.

Note: the picture is from the blogger wangprince2017 Article: https://www.cnblogs.com/wangprince2017/p/7827091.html 

1.1 insert trigger

There is a student table, which contains six fields. Now, you need to write a trigger on the student table. When you insert the student table, the time of insertion, the id and name fields in the student table will be automatically added, as shown in the student log table.

-------------------Building tables---------------------------
create table student
(
[id] int
,[name] nvarchar(50)
,[Chinese] int 
,[English] int 
,[Math] int 
,[rank] int
)
----------------------First step--------------------------  
--Establish insert trigger
IF OBJECT_ID (N'trig_insert ', N'tr') IS NOT NULL
    DROP TRIGGER trig_insert 
go
create trigger trig_insert --Create trigger trig_insert
on student
after insert  --Yes after\for Choice
as
begin
	declare @id int
	declare @name nvarchar(50)

	if object_id(N'student_log',N'U') is null--judge student_log Does the table exist
	--Create log table student_log
		create table student_log(logid int identity(1,1),inserttime datetime,id int,name nvarchar(50));

	select @id=id, @name=name from inserted;
	insert into student_log (inserttime, id, name) values ({fn NOW()},@id,@name);
    
end


----------------------The second step-------------------------- 
--View existing triggers
select * from sysobjects where xtype='TR'

----------------------The third step-------------------------- 
--insert data
insert into student values(9,'tianshiyi',54,72,17,1)

----------------------The fourth step-------------------------- 
--View log table
select * from student_log;

After executing the above code, the log table will have the following records to represent success

 

1.2 delete trigger

The logic of deletion is the same as the above insert. The only difference is that the data comes from the deleted table.

----------------------First step--------------------------  
--Establish delete trigger
IF OBJECT_ID (N'trig_delete ', N'tr') IS NOT NULL
    DROP TRIGGER trig_delete 
go
create trigger trig_delete --Create trigger trig_delete
on student
after delete  --Yes after\for Choice
as
begin
	declare @id int
	declare @name nvarchar(50)

	if object_id(N'student_log1',N'U') is null--Judge whether the table exists
	--Create log table student_log1
		create table student_log1(logid int identity(1,1),deletetime datetime,id int,name nvarchar(50));

	select @id=id, @name=name from deleted;
	insert into student_log1 (deletetime, id, name) values ({fn NOW()},@id,@name);
    
end


----------------------The second step-------------------------- 
--View existing triggers
select * from sysobjects where xtype='TR'

----------------------The third step-------------------------- 
--insert data
delete student where id='9'

----------------------The fourth step-------------------------- 
--View log table
select * from student_log1;

1.3 update trigger

Updating the log is a bit more cumbersome because the update involves both the deleted and inserted tables

----------------------First step--------------------------  
--Establish update trigger
IF OBJECT_ID (N'trig_update', N'tr') IS NOT NULL
    DROP TRIGGER trig_update 
go
create trigger trig_update --Create trigger trig_update
on student
after update  --Yes after\for Choice
as
begin
	declare @id int
	declare @name nvarchar(50)
	declare @Chinese int
	declare @English int
	declare @Math int
	declare @rank int
	declare @update_id int
	declare @update_name nvarchar(50)
	declare @update_Chinese int
	declare @update_English int
	declare @update_Math int
	declare @update_rank int

	if object_id(N'student_log2',N'U') is null--Judge whether the table exists
	--Create log table student_log2
		create table student_log2
		(
		logid int identity(1,1),
		deletetime datetime,
		id int,
		name nvarchar(50),
		Chinese int,
		English int,
		Math int,
		rank int,
		update_id int,
		update_name nvarchar(50),
		update_Chinese int,
		update_English int,
		update_Math int,
		update_rank int
		);
	--Take the data before update
	select @id=id,@name=name,@Chinese=Chinese,@English=English,@Math=Math,@rank=rank from deleted;
	--Take the updated data
	select @update_id=id,@update_name=name,@update_Chinese=Chinese,@update_English=English,@update_Math=Math,@update_rank=rank from inserted;
	
	insert into student_log2(deletetime,id,name,Chinese,English,Math,rank,update_id,update_name,update_Chinese,update_English,update_Math,update_rank) 
	values ({fn NOW()},@id,@name,@Chinese,@English,@Math,@rank,@update_id,@update_name,@update_Chinese,@update_English,@update_Math,@update_rank);
    
end


----------------------The second step-------------------------- 
--View existing triggers
select * from sysobjects where xtype='TR'

----------------------The third step-------------------------- 
--Update data
update student set name='linshi',Chinese='100',English='100',Math='100',rank=3 where id='8'

----------------------The fourth step-------------------------- 
--View log table
select * from student_log2;

2. Example of DDL trigger

After writing this part, I found that the blogger Brambling Written by SQL Server DDL trigger >More detailed, so deleted, put the link of this article to everyone.