【数据库学习笔记】SQL触发器（例题+代码）

数据库SQL

1、触发器概念

（1）触发器（trigger）是用户定义在关系表上的一类由事件驱动的存储过程，由服务器自动激活。

（2）触发器可进行更为复杂的检查和操作，具有更精细和更强大的数据控制能力。

（3）触发器是一种特殊的存储过程，不管什么原因造成的数据变化都能自动响应，对于每条SQL语句，触发器仅执行一次。

总之触发器是一个自动激活的存储过程

 2、触发器的分类

在SQLServer2008中，按照触发事件的不同可以将触发器
分两大类：DML触发器和DDL触发器。

（1）DML触发器。当数据库中发生数据操纵语言 （DML）事件时将调用DML触发器。一般情况下，DML事件包括对表或视图的INSERT语句、UPDATE语句和DELETE语句，因而DML触发器也可分三种类型：INSERT、UPDATE和DELETE。（对数据操作）

（2）DDL触发器。DDL触发器也是由相应的事件触发 的，但DDL触发器触发的事件是数据定义语句（DDL）。这些语句主要是以CREATE、ALTER、DROP等关键字开头的语句。（对对象操作）

3、触发器的实现

CREATE TRIGGER trigger_name
ON {table/view}   /*指定操作对象*/
{FOR/AFTER/INSTEAD OF}
{INSERT/UPDATE/DELETE}
AS {sql_statement }   /*一条或多条SQL语句*/

After：用于说明触发器在指定操作成功执行后触发；

instead of：指定用DML触发器中的操作代替触发语句的操作，一条DML语句最多只能定义一条instead of触发器。

｛INSERTIUPDATE|DELETE｝：触发器事件，触发器的事件可以 是插入INSERT、更新UPDATE和删除DELETE事件，也可以是这几个事件的组合。

• 变量的定义：declare @ 变量名 数据类型
• 循环：while..｛..｝
• 撤销数据更改：rollback
• 提交数据更改：commit
• 语句块：Begin....End
• 条件判断：IF..else

例：创建触发器，保证学生表中的性别仅能取男和女。

create trigger sexUpt
on Student
for insert, update
as if exists(select * from inserted where sex not in('男','女')rollback
/*本例的inserted表结构与Student表结构相同*/

例：创建一个表tablel，其中只有一列a。在表上创 建一个触发器，每次插入操作时，将变量@str的值设为“TRIGGERISWORKING”并显示。 

CREATE TABLE table1(a int)
go
CREATE TRIGGER table1_insert
ON table1 
AFTER INSERT
AS
BEGIN
DECLARE @str char(50)
SET @str='TRIGGER IS WORKING'
PRINT @str
END
/*begin...end是T-SQL语言语句块*/

例:创建触发器，当向CJB表中插入一个学生的成绩时，将XSB
表中该学生的总学分加上添加的课程的学分。

CREATE TRIGGER jb_ insert
ON CIB AFTER INSERT AS
BEGIN
DECLARE @num char(6), @kc_num char(3)
DECLARE @xf int
SELECT @num=学号，@kc_num=课程号from inserted
SELECT @xf=学分 FROM KCBWHERE 课程号=@kc_num
UPDATE XSB SET总学分=总学分+@xfWHERE =@num
PRINT'修改成功'
End;

创建UPDATE触发器。UPDATE触发器在对触发器表执行 UPDATE语句后触发。在执行UPDATE触发器时，将触发器表的原记录保存到deleted时表中，将修改后的记录保存到inserted临时表中。

触发器是对临时表进行操作的。

例：创建触发器，当修改XSB表中的学号时，同时也要将CJB表 中的学号修改成相应的学号（假设XSB表和CJB表之间没有定义外键约束）。

CREATE TRIGGER *sb_update
ON XSB AFTER UPDATE
AS
BEGIN
DECLARE @old_num char(6), @new_num char(6)
SELECT @old_num= 学号 FROM deleted
SELECT @new_num= 学号 FROM inserted
UPDATE CIB SET 学号=@new_numWHERE 学号=@old_num
END;

即更新操作也要经过临时表。

例：在删除XSB表中的一条学生记录时将CJB表中该学生的相应记录也删除。

CREATE TRIGGER xsb_delete
ON XSB AFTER DELETE
AS
BEGINDELETE FROM CJBWHERE 学号 IN（SELECT 学号 FROM deleted)
END;  /*在SQL中本来这里deleted的地方应该是XSB表*/

例：在KCB表中创建UPDATE和DELETE触发器，当修改或删除KCB表中的“课程号”字段时，同时修改或删除CJB表中的该课程号。

CREATE TRIGGER kcb_trig 
ON KCB AFTER UPDATE, DELETE 
AS
BEGINIF（UPDATE（课程号））UPDATE CIB SET 课程号=（ SELECT 课程号 FROM inserted）WHERE 课程号=（SELECT 课程号 FROM deleted)
ELSEDELETE FROM CJB WHERE 课程号 IN（SELECT 课程号 FROM deleted）
END;

例：定义一个触发器，为学生表student定义完整性 规则：学生的年龄不得小于10岁，如果小于10岁则自动修改为10岁。 

create trigger name_change
on Student for insert, update
as 
beginupdate Student set Sage = 10 where Sage <10
end;

例：写一个允许用户一次只删除一条记录的触发器。（这里要用到聚合函数）

create trigger tr_emp 
on employee after delete
as 
declare @row_cnt int 
select @row_cnt=count(*) from deleted
if @row_cnt>1
begin 
print'此删除操作可能回删除多条人事表数据！！！'
rollback transaction
end

创建INSTEAD OF触发器。AFTER触发器 是在触发语句执行后触发的，与AFTER触发器不同 的是，INSTEADOF触发器触发时只执行触发器内部的SQL语句，而不执行激活该触发器的SQL语句。一个表或视图中只能有一个INSTEADOF触发器。

例：写一个触发器，当用户将course表中的学分修改为大于7的值时，不对数据进行修改，同时发出提示信息。

Create trigger not_allowed
On course
Instead of update
As
Begin
Declare @credit int, @cno char(4) Select @cno= cno,@credit=credit from inserted
if @credit>7Printf‘学分不允许大于7’
elseupdate course set credit=@credit where cno =@cno
End