1创建触发器

create trigger 触发器名称
on 表名
for delete,update,insert    --触发条件可多选或单选
as
    T-SQL语句

2删除触发器

drop trigger 触发器名称[,...]

3重命名触发器

用查询分析器重命名
exec sp_rename 原名称, 新名称
sp_rename 是 SQL Server 自带的一个存储过程，用于更改当前数据库中用户创建的对象的名称，如表名、列表、索引名等。
exec 是 EXECUTE 的缩写。功能是 执行一个存储过程.或者是 执行一个动态SQL。

4禁用、启用触发器

禁用：alter table 表名 disable trigger 触发器名称
启用：alter table 表名 enable trigger 触发器名称
 
如果有多个触发器，则各个触发器名称之间用英文逗号隔开。
 
如果把“触发器名称”换成“ALL”，则表示禁用或启用该表的全部触发器。

5instead of 触发条件

INSTEAD OF
执行触发器语句，但不执行触发触发器的 SQL 语句，比如试图删除一条记录时，将执行触发器指定的语句，此时不再执行 delete 语句。例如：
create trigger f
on table1
instead of delete
as
    T-SQL语句

6查看数据库中所有触发器

use 数据库名
go
select * from sysobjects where xtype='TR'
sysobjects 保存着数据库的对象，其中 xtype 为 TR 的记录即为触发器对象。在 name 一列，我们可以看到触发器名称。

7查看某触发器内容

use 数据库名
go
exec sp_helptext '触发器名称'

8查看触发器属性

use 数据库名
go
exec sp_helptrigger tbl
sp_helptrigger 有两个参数：第一个参数为表名；第二个为触发器类型，为 char(6) 类型，可以是 INSERT、UPDATE、DELETE，如果省略则显示指定表中所有类型触发器的属性。

9递归、嵌套

递归分两种，间接递归和直接递归。我们举例解释如下，假如有表1、表2名称分别为 T1、T2，在 T1、T2 上分别有触发器 G1、G2。
 
间接递归：对 T1 操作从而触发 G1，G1 对 T2 操作从而触发 G2，G2 对 T1 操作从而再次触发 G1...
直接递归：对 T1 操作从而触发 G1，G1 对 T1 操作从而再次触发 G1... 
嵌套触发器
 
类似于间接递归，间接递归必然要形成一个环，而嵌套触发器不一定要形成一个环，它可以 T1->T2->T3...这样一直触发下去，最多允许嵌套 32 层。
 
设置直接递归
默认情况下是禁止直接递归的，要设置为允许有两种方法：
T-SQL：exec sp_dboption 'dbName', 'recursive triggers', true
EM：数据库上点右键->属性->选项。 

设置间接递归、嵌套
默认情况下是允许间接递归、嵌套的，要设置为禁止有两种方法：
T-SQL：exec sp_configure 'nested triggers', 0 --第二个参数为 1 则为允许
EM：注册上点右键->属性->服务器设置。

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的简单使用

一、触发器定义

触发器是一种机制，当某个事物满足某个条件，触发某个功能

当某个事物满足某个条件触发某个功能

用法：

1、安全考虑，考虑到数据被修改的权限问题，用触发器代替用户来添加修改数据。

2、审计功能，对前端操作进行审计

3、实现业务规则

4、同步数据

5、用于科学计算，做数学统计

二、触发器编写

1、编写格式

注意：DELIMITER  是定界符，分隔符的意思

声明mysql的结束符，默认结束符是；

DELIMITER ||
CREATE TRIGGER triggerName AFTER/BEFORE insert ON tableName 
FOR EACH ROW
BEGIN
ISNERT INTO tableName(column1,column2,...)VALUE(....);
END
DELIMITER ;

2.触发器效果的语句

Create 创建

Trigger 触发器

Tb2_auto_add 触发器名称，必须有意义

AFTER 触发时间

AFTER在触发事件之后

BEFOR 在触发器之前

INSERT 触发的条件

UPDATE 更新事件

DELETE 删除事件

INSERT 插入事件

EACH ROW 任意一行

BEGIN 开始编写触发事件

Insert into tb2(name,age) value(“老王”,18); 触发的事件

END 触发事件结束

3、举例分析

以两个表 ta1  和 ta2为例

1.创建table1,table2 表：
create table table1(name char(30),age int)charset="utf8";
create table table2(name char(30),age int)charset="utf8";
2.创建触发器：
DELIMITER ||
create trigger tb2_auto_add after insert on table1 
for each row
begin
insert into table2(name,age)value("老旧",33);
end
DELIMITER ;
insert into table1(name,age)value("老刘"，23)；

测试结果：

三、查看触发器

触发器的信息都被存放在information_schema数据库当做trigger表当中

查看information_schema中所有信息

select * from information_schema.triggers\G;

四、删除触发器

DROP TRIGGER triggerName;

在触发器中，new 变量代表触发表，可以生成动态的

结果：