SQL Server触发器(附有实例)

触发器的概念

触发器（trigger）是SQL server 提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发 ，当对一个表进行操作（ insert，delete， update）时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。触发器可以实现的功能包括：使用触发器实现检查约束、维护冗余数据、维护外键列数据等。

触发器与存储过程的区别

触发器与存储过程的区别是运行方式的不同，触发器不能执行execute语句调用，是在用户执行T-SQL语句时自动触发执行，而存储过程需要用户、用户程序或者触发器来显示地调用并执行。

触发器的优点

1. 触发器是自动的。当对表中的数据做了任何修改之后立即被激活。
2. 触发器可以通过数据库中的相关表进行叠层修改。
3. 触发器可以强制限制，这些限制比用check约束所定义的更复杂。与check约束不同的是，触发器可以引用其他表中的列。

触发器的作用

1. 强制数据库间的引用完整性。
2. 级联修改数据库中所有相关的表，自动触发其他与之相关的操作。
3. 跟踪变化，撤销或回滚违法操作，防止非法修改数据。
4. 返回自定义的错误信息，约束无法返回信息，而触发器可以。
5. 触发器可以调用更多的存储过程。

语法

--创建触发器语法
create trigger trigger_name on {table_name | view_name}
for | after | instead of delete | insert | update
as
sql_statement
--删除触发器语法
drop trigger trigger_name
--修改触发器语法
alter trigger trigger_name on {table_name | view_name}          
for | after | instead of delete | insert | update          
as 
sql_statement 

inserted表和deleted表

SQL Server为每个触发器都创建了两个专用表：inserted表和deleted表。这两个表由系统来维护，它们存在于内存中而不是在数据库中。这两个表的结构总是与被该触发器作用的表的结构相同。触发器执行 完成后﹐与该触发器相关的这两个表也被删除。 deleted表存放由于执行delete或update语句而要从表中删除的所有行。 inserted表存放由于执行insert或update语句而要向表中插入的所有行。

instead of 触发器

instead of 触发器用来代替通常的触发动作，即当对表进行insert、update、或delete操作时，系统不是直接对表执行这些操作，而是把操作内容交给触发器，让触发器检查所进行的操作是否正确。如果正确才进行相应的操作。因此，instead of 触发器的动作要早于表的约束处理。
instead of 触发器的操作有点类似于完整性约束。在对数据库进行操纵时，有些情况下使用约束可以达到更好的效果，而如果采用触发器，则能定义比完整性约束更加复杂的约束。
instead of 触发器不仅可在表上定义，还可在带有一个或多个基表的视图上定义，但在作为级联引用实现完整性约束目标的表上限制应用。

after 触发器

after 触发器定义了对表执行insert、update或delete语句操作之后再执行的操作。比如对某个表中的数据进行了更新操作后，要求立即对相关的表进行指定的操作，这时就可以采用after触发器。after触发器只能在表上指定，且动作晚于约束处理。
每一个表上只能创建一个instead of 触发器，但可以创建多个after触发器。

实例

Topic【试题表】

Paper【试卷表】

需求说明：

1. 新增一个试题时，同时试卷表的试卷总分根据新增试题的分值随之增加。
2. 删除一个试题时，同时试卷表的试卷总分根据删除试题的分值随之减少。
3. 修改一个试题内容，同时试卷表随之改变，比如总分。

-- 1. 新增一个试题时，同时试卷表的试卷总分根据新增试题的分值随之增加
CREATE TRIGGER [dbo].[Trigger_Topic_Insert]
   ON  [dbo].[Topic]
   AFTER Insert
AS 
BEGIN
	declare @paperSocre int;--定义试卷总分
	declare @p_id int;      --定义试卷编号
	select @p_id = PaperID,@paperSocre = TopicScore  from inserted          --存入inserted表中
	update Paper set PaperSorce = @paperSocre + PaperSorce where PaperID = @p_id
END

select * from Paper
insert into Topic values('无序列表的标签是？',25,3,'','','','',3,'<ul></ul>',1)
select * from Paper

查询结果

--2. 删除一个试题时，同时试卷表的试卷总分根据删除试题的分值随之减少
CREATE TRIGGER [dbo].[Trigger_Topic_Delete]
   ON  [dbo].[Topic]
   AFTER Delete
AS 
BEGIN
    declare @paperSocre int;  --试卷总分
    declare @p_id int;        --试卷编号
	select @p_id = PaperID from deleted --存入deleted表中
	--获取总分
	select @paperSocre = sum(TopicScore) from Topic where PaperID = @p_id
	--修改总分
	update Paper set PaperSorce = @paperSocre where PaperID = @p_id
END

select p.PaperID, p.PaperSorce,t.TopicScore,t.TopicID
from  Paper p  inner join Topic t on p.PaperID = t.PaperID

delete Topic where TopicID = 15

select p.PaperID, p.PaperSorce,t.TopicScore,t.TopicID
from  Paper p  inner join Topic t on p.PaperID = t.PaperID

删除结果

--3. 修改一个试题内容，同时试卷表随之改变，比如总分
CREATE TRIGGER [dbo].[Trigger_Topic_Update]
   ON  [dbo].[Topic]
   AFTER UPDATE
AS 
BEGIN
	declare @p_id int;
	declare @paperSocre int
	select @paperSocre =TopicScore ,@p_id = PaperID from deleted
	update Paper set PaperSorce -= @paperSocre where PaperID = @p_id
	select @paperSocre =TopicScore ,@p_id = PaperID from inserted
	update Paper set PaperSorce += @paperSocre where PaperID = @p_id
END

select p.PaperID, p.PaperSorce,t.TopicScore,t.TopicID
from  Paper p  inner join Topic t on p.PaperID = t.PaperID

update Topic set TopicScore = 20 where TopicID = 15

select p.PaperID, p.PaperSorce,t.TopicScore,t.TopicID
from  Paper p  inner join Topic t on p.PaperID = t.PaperID

更新结果