SQL语句之触发器

SQL语句之触发器

在学习触发器之前，我们先要明确几个概念：

IF NOT EXISTS和 IF EXISTS的区别

介绍

if not exists 即如果不存在，if exists 即如果存在

使用

a.判断数据库不存在时

if not exists（select * from sys.databases where name = 'database_name'）

b.判断表不存在时

if not exists (select * from sysobjects where id = object_id('table_name') and OBJECTPROPERTY(id, 'IsUserTable') = 1)

c.判断列不存在

if not exists （select * from syscolumns where id=object_id('table_name') and name='column_name'）

当判断的表不存时，我可以执行创建数据库，创建表，增加列，可以执行相应的SQL语句；

而if exists同理判断，首先判断查询结果是否存在，如果存在执行判断后面的语句， 查询的数据库，表，列的方法相同由此可见，“IF　EXISTS“可以避免删除不存在的数据库出现的MySQL错误信息。另外需要注意的是：使用DROP DATABASE 或DROP SCHEMA 语句会删除指定的整个数据库，该数据库中的所有表（包含其中的数据）也将永远删除。因此使用时需谨慎，以免错误删除。¹

AFTER触发器与INSTEAD OF触发器的区别

INSTEAD OF触发器

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

AFTER触发器

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

Inserted和Deleted在insert、update、detele的简单使用

Inserted和Deleted在insert、update、detele的简单使用

Inserted表和Deleted表,仅仅在触发器运行时存在。当insert、update、detele操作时，可使用借助两个表来输出（使用OUTPUT关键字）操作前后的数据的变化。³

ROLLBACK

rollback 回滚的意思。 就是数据库里做修改后(update ,insert , delete)未commit 之前 使用rollback 可以恢复数据到修改之前。

示例

AFTER触发器示例

限制sc表考试成绩必须在0~100范围内。

CREATE TRIGGER tri_1
	ON sc AFTER INSERT,UPDATE
AS
	IF EXISTS(
		SELECT * FROM INSERTED 
		WHERE grade NOT BETWEEN 0 AND 100
	)
	BEGIN
		ROLLBACK
		PRINT '成绩必须在0-100范围内'
	END

限制student表学生所在系的取值必须在{计算机系，信息系，物理系，数学系}范围内。

CREATE TRIGGER tri_2
	ON student AFTER INSERT,UPDATE
AS
	IF EXISTS(
		SELECT * FROM INSERTED
		WHERE sdept NOT IN ('计算机系','信息系','物理系','数学系','数学系')
	)
	BEGIN
		ROLLBACK
		PRINT '学生所在系的取值必须在{计算机系，信息系，物理系，数学系}范围内'
	END

限制学生的选课总门数不能超过8门。

CREATE TRIGGER tri_3
	ON sc AFTER INSERT,UPDATE
AS
	IF EXISTS (
	SELECT * FROM student s
	INNER JOIN sc ON sc.sno = s.sno
	GROUP BY s.sno
	HAVING COUNT(sno) > 8
	)
	BEGIN
		ROLLBACK
		PRINT '学生的选课总门数不能超过8门。'
	END

INSTEAD OF触发器示例

限制不能删除考试成绩不合格学生的考试记录。

CREATE TRIGGER tri_4
	ON sc INSTEAD OF DELETE
AS
	IF EXISTS (
	SELECT * FROM DELETED
	WHERE grade < 60
	)
	BEGIN
		ROLLBACK
		PRINT '不能删除考试成绩不合格学生的考试记录。'
	END

References

[1] IF NOT EXISTS和 IF EXISTS的区别

[2] AFTER触发器与INSTEAD OF触发器的区别

[3] Inserted和Deleted在insert、update、detele的简单使用