SQL之trigger触发器

触发器是一种数据库对象，一般与一个表相关联，当对该表做出某种修改操作（INSERT、DELETE、UPDATE）时，它会自动地予以执行。触发器可以查询其它表，并可以包含复杂的SQL（或Transact-SQL）语句。

触发器经常用于加强数据的完整性约束和业务规则等。

由于每个厂商对trigger的实现不同，这里以SQL Server为例。

特点

1）它与表紧密相连，可以看作表定义的一部分

2）它不能通过名称被直接调用，更不允许带参数，而是当用户对表中的数据进行修改时，自动执行

3）它可以用于SQL Server约束、默认值和规则的完整性检查，实施更为复杂的数据完整性约束

一个触发器定义包含以下三个部分（ECA）

（1）事件（Event）：激活触发器执行的数据库改变事件，可能是在某个特定表上执行的UPDATE、DELETE、INSERT等操作

（2）条件（Condition）：是触发器能够执行必须满足的条件

（3）动作（Action）：当触发器被激活且条件为真时，DBMS要执行的过程

SQL Server中trigger的类型

（1）INSERT触发器可以完成对输入数据的审核，用来修改或拒绝接受正在插入的记录

（2）DELETE触发器用于约束用户能够从数据库中删除的数据

（3）UPDATE触发器约束用户对表中数据的update操作

（4）以上几种类型的组合触发器

trigger用途

（1）当某些表修改时，可以读取或修改其他的表或数据库，保证数据库的一致性

（2）比较数据前后版本的不同，提供高级的审计和透明事件记录

（3）自动地生成导出列（通过表达式计算出的列）的值

（4）施行复杂的安全性确认和事务约束

（5）维护同步表

触发器与约束

1.在特殊情况下各有优势。

2.触发器的主要好处在于它们可以包含使用 T-SQL 代码的复杂处理逻辑。

3.在约束所支持的功能无法满足应用程序的功能要求时，触发器就极为有用。

创建trigger

基本格式为：

CREATE TRIGGER trigger_name
ON {table | view} 
{FOR | INSTEAD OF} {[INSERT] [,] [UPDATE] [,] [DELETE]}
AS
	sql_statement [ ...n ] 
	[RETURN]

其中，

1.trigger_name是触发器的名称，同样在数据库中必须唯一。而Table | view是在其上执行触发器的表或视图，有时称为触发器表或触发器视图

2.FOR指定触发器只有在触发SQL语句（INSERT、UPDATE、DELETE）中指定的所有操作都已成功执行后才激发，而且只能在表上定义。所有的引用级联操作和约束检查也必须成功完成后，才能执行此触发器

3.INSTEAD OF 指定执行触发器而不是执行触发 SQL 语句，从而替代触发语句的操作。在表或视图上，每个INSERT、UPDATE或DELETE语句最多可以定义一个INSTEAD OF触发器

4.{ [DELETE] [,] [INSERT] [,] [UPDATE] }是指定在表或视图上执行哪些数据修改语句时将激活触发器的关键字。必须至少指定一个选项。在触发器定义中允许使用以任意顺序组合的这些关键字。如果指定的选项多于一个，需用逗号分隔这些选项

5.sql_statement是触发器的条件和动作。触发器条件指定其它准则，以确定 DELETE、INSERT 或 UPDATE 语句是否导致执行触发器操作。当尝试 DELETE、INSERT 或 UPDATE 操作时，T-SQL语句中指定的触发器操作将生效，对每个语句触发器只执行一次，即使此语句修改了好几行

triggers on Views

正常情况下，我们无法修改一个虚拟的视图，因为这个视图是不存在的，但是我们可以通过触发器来对视图进行修改。

如：首先创建一个视图

CREATE VIEW Synergy
AS
	SELECT Likes.drinker, Likes.beer, Sells.bar
	FROM Likes, Sells, Frequents
	WHERE Likes.drinker = Frequents.drinker
		AND Likes.beer = Sells.beer
		AND Sells.bar = Frequents.bar;

然后我们对该视图创建一个触发器

CREATE TRIGGER ViewTrig
ON Synergy
INSTEAD OF INSERT 
AS
	INSERT INTO Likes SELECT drinker, beer FROM INSERTED;
	INSERT INTO Sells(bar, beer) SELECT bar, beer FROM INSERTED;
	INSERT INTO Frequents SELECT drinker, bar FROM INSERTED;

创建完以后，我们就可以对视图进行INSERT操作

INSERT INTO Synergy VALUES('Tom', '科罗娜', '3DArtBar');

删除trigger

DROP TRIGGER { 触发器名称 } [ ,...n ]

当某个表被删除后，该表上的所有触发器将同时被删除，但是删除触发器不会对表中的数据有影响。

trigger应用实例

准备数据

create table student(
	sno char(5) primary key,
	sname char(8) not null,
	age smallint check(age >= 15 and age <= 35),
	sex nchar(1) check(sex = '男' or sex = '女')
)
create table course(
	cno char(4) primary key,
	cname char(20) not null,
	teacher char(8)
)
create table study(
	sno char(5) foreign key references student(sno),
	cno char(4) foreign key references course(cno),
	score smallint,
	primary key(sno, cno)
)

应用

1.为study表创建一个UPDATE触发器，当更新成绩时，要求更新后的成绩不能低于原来的成绩。

create trigger update_score
on study
instead of update
as
	declare @sno char(5), @cno char(4), @score1 smallint, @score2 smallint
	select @sno = sno, @cno = cno, @score1 = score from inserted
	select @score2 = score
	from deleted
	if(@score1 >= @score2)
	update study set score = @score1
	where study.cno = @cno and study.sno = @sno;
go  --批处理的标志，把程序分为一个个代码块，每个被go分隔的语句都是独立的事务
update study
set score = 80
where sno = '98601' and cno = 'C601';  --因为更新的分数小于原分数，所以更新失败

2.为study表创建一个DELETE触发器，要求一次只能从study表中删除一条记录。

create trigger del_one 
on study
instead of delete
as
	declare @num int, @sno char(5), @cno char(4)
	select @num = count(*) from deleted
	if @num = 1
	begin 
		select @sno = sno, @cno = cno from deleted
		delete from study where @sno = study.sno and @cno = study.cno
	end
	else print'要删除的记录多于1条';
go
delete from study
where sno = '98601';  --因为删除多条记录，所以输出打印信息'要删除的记录多于1条'

3.为course表创建一个INSERT触发器，要求插入的课程记录中任课教师不能为空。

create trigger insert_course on course
instead of insert
as
	declare @cno char(4), @cname char(20), @teacher char(8)
	select @teacher = teacher from inserted
	if(@teacher is null) print'教师名不能为空';
	else 
	begin
		select @cno = cno, @cname = cname from inserted
		insert into course values(@cno, @cname, @teacher)
	end;
go
insert into course(cno, cname) values('C606', '计算机组成原理');