sqlserver 触发器简析及实例应用

一、啥是触发器

触发器（trigger）是SQL server 提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发。（----来自百度百科）

简单理解，触发器就是当你对数据库进行某些特定的行为时（比如更新、删除某张表），自动作出反馈（如记录你的修改内容、时间等）。

触发器的作用如下（----来自百度百科）：

1. 可在写入数据表前，强制检验或转换数据。
2. 触发器发生错误时，异动的结果会被撤销。
3. 部分数据库管理系统可以针对数据定义语言（DDL）使用触发器，称为DDL触发器。
4. 可依照特定的情况，替换异动的指令 (INSTEAD OF)。

简单来说，就是设计者可以根据实际情况，为数据库使用设定一些保护规则，当用户使用数据库触及这些规则时，触发器会给出相应的反馈。

二、触发器分类

DML触发器是基于数据库中各表内容的操作代码，当数据库中发生数据操作语言事件时执行这些操作。

登录触发器将为响应 LOGIN 事件而激发存储过程。与 SQL Server 实例建立用户会话时将引发此事件。登录触发器将在登录的身份验证阶段完成之后且用户会话实际建立之前激发。因此，来自触发器内部且通常将到达用户的所有消息（例如错误消息和来自 PRINT 语句的消息）会传送到 SQL Server 错误日志。如果身份验证失败，将不激发登录触发器。

DDL触发器是基于对数据架构进行的某些更改或记录数据中的更改或事件操作。

三、举个例子

1、DML触发器示例

DML触发器需要注意两张表：inserted表、deleted表。这两张表是系统自动创建的，记录了插入或更新后、删除或更新前的记录行。

注：图片来源于博主wangprince2017文章：https://www.cnblogs.com/wangprince2017/p/7827091.html 

1.1、insert触发器

有一个student表，包含6个字段，现在要在student表上写一个触发器，当对student表执行插入操作时，自动加插入的时间、student表中的id和name字段内容记录如日志表student_log中

-------------------建表---------------------------
create table student
(
[id] int
,[name] nvarchar(50)
,[Chinese] int 
,[English] int 
,[Math] int 
,[rank] int
)
----------------------第一步--------------------------  
--创建insert触发器
IF OBJECT_ID (N'trig_insert ', N'tr') IS NOT NULL
    DROP TRIGGER trig_insert 
go
create trigger trig_insert --创建触发器trig_insert
on student
after insert  --有after\for 选择
as
begin
	declare @id int
	declare @name nvarchar(50)

	if object_id(N'student_log',N'U') is null--判断student_log表是否存在
	--创建日志表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


----------------------第二步-------------------------- 
--查看已有触发器
select * from sysobjects where xtype='TR'

----------------------第三步-------------------------- 
--插入数据
insert into student values(9,'tianshiyi',54,72,17,1)

----------------------第四步-------------------------- 
--查看日志表
select * from student_log;

执行完上述代码，日志表会有如下记录代表成功

 

1.2、delete触发器

删除的逻辑跟上面的insert相同，唯一区别是数据从deleted表来

----------------------第一步--------------------------  
--创建delete触发器
IF OBJECT_ID (N'trig_delete ', N'tr') IS NOT NULL
    DROP TRIGGER trig_delete 
go
create trigger trig_delete --创建触发器trig_delete
on student
after delete  --有after\for 选择
as
begin
	declare @id int
	declare @name nvarchar(50)

	if object_id(N'student_log1',N'U') is null--判断表是否存在
	--创建日志表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


----------------------第二步-------------------------- 
--查看已有触发器
select * from sysobjects where xtype='TR'

----------------------第三步-------------------------- 
--插入数据
delete student where id='9'

----------------------第四步-------------------------- 
--查看日志表
select * from student_log1;

1.3、update触发器

更新日志稍微麻烦一点，因为更新同时涉及到deleted和inserted表

----------------------第一步--------------------------  
--创建update触发器
IF OBJECT_ID (N'trig_update', N'tr') IS NOT NULL
    DROP TRIGGER trig_update 
go
create trigger trig_update --创建触发器trig_update
on student
after update  --有after\for 选择
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--判断表是否存在
	--创建日志表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
		);
	--取更新前的数据
	select @id=id,@name=name,@Chinese=Chinese,@English=English,@Math=Math,@rank=rank from deleted;
	--取更新后的数据
	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


----------------------第二步-------------------------- 
--查看已有触发器
select * from sysobjects where xtype='TR'

----------------------第三步-------------------------- 
--更新数据
update student set name='linshi',Chinese='100',English='100',Math='100',rank=3 where id='8'

----------------------第四步-------------------------- 
--查看日志表
select * from student_log2;

2、DDL触发器示例

这部分内容写了之后，发现博主Brambling 写的《SQL Server DDL触发器》更详细，所以删掉了，放上该文章的链接给大家。