SQL语句之触发器

2022-11-20 10:44:46

SQL语句之触发器

SQL语句之触发器

IF NOT EXISTS和 IF EXISTS的区别

介绍
使用

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

INSTEAD OF触发器
AFTER触发器

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

AFTER触发器示例
INSTEAD OF触发器示例

References

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操作时，可使用借助两个表来输出（使用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的简单使用

码农公寓