SQL触发器学习笔记

介绍

触发器是一种特殊的存储过程它不能被显式地调用，而是insert，update，delete之前或之后被自动地激活。所以触发器可以用来实现对表实施复杂的完整性约束，这种特性可以协助应用系统在数据库端确保数据的完整性，日志记录，数据校验等操作，通过使用别名NEW和OLD来引用触发器中发生变化的内容记录。

SQL Server触发器

SQL Server为每个触发器都创建了两个专用表:Inserted表和Deleted表。这两个表由系统来维护，它们存在于内存中而不是在数据库中。这两个表的结构总是与被该触发器作用的表的结构相同。 触发器执行完成后﹐与该触发器相关的这两个表也被删除。

Deleted表存放由于执行Delete或Update语句而要从表中删除的所有行。在delete或update语句执行时，这些相关行从trigger表移到了deleted表。 Inserted表存放由于执行Insert或Update语句而要向表中插入的所有行。在insert或update语句执行时，这些相关行从trigger表移到了deleted表。

触发器与存储过程的区别

主要区别在于运行方式，存储过程需要用户、应用程序或者触发器来显式地调用并执行，而触发器是当特定事件出现的时候，自动执行。触发器在数据库是独立的对象存储，与存储过程不同的是，存储过程通过其他程序来启动运行，而触发器是由一个事件来启动运行。即当某个事件发生时，触发器自动地隐式运行。并且，触发器不能接收参数。

触发器的优点

1.触发器自动执行，对表或对数据库的数据进行修改拿值等操作就会触发。

2.数据库中的相关表可以级联修改触发器。它可以通过数据库中的相关表进行层叠更改，这比把代码写在前台的做法更安全合理。

3.触发器可以实施限制，可以强制限制数据的完整性。这些限制比检查约束定义的限制更复杂。与CHECK约束不同，触发器可以引用其他表中的列。

注意事项

对相同的数据表、相同的事件只能创建一个触发器

比如对表 account创建了一个AFTER INSERT触发器，那么如果对表 account 再次创建一个 AFTER >INSERT触发器，SQL Server将会报错，此时，只可以在表 account上创建 AFTER INSERT 或者>INSTEAD OF UPDATE类型的触发器。灵活地运用触发器将为操作省去很多麻烦。

不再使用的触发器记得及时删除。

触发器的种类

1.DDL触发器（针对数据库级别）

当服务器或者数据库中发生数据的定义语言事件时就会调用DDL触发器，执行以下操作，可以使用DDL触发器。

防止对数据库架构的某些内容的更改 防止对数据库中的数据进行删除等操作。 或者记录数据库架构中的事件或修改操作。

2.DML触发器（数据操作）

DML触发器是当前数据库或者服务器中发生操作语言事件时就会执行的操作。DML触发器可以查询其他表的内容，还可以包含复杂的T-SQL语句。

触发器将触发它的语句作为可在触发器内回滚的单个事务对待，如果检测到错误。则整个事务自动回滚。

分类:insert型触发器、update型触发器、delete型触发器

3.登录触发器

与SQL Server实例建立用户会话时将引发此事件。 注意：登录触发器将在登录的身份验证阶段完成之后且用户会话实际建立之前激发。

MySQL创建DML触发器

delimiter $
create trigger 触发器名称
before|after insert|update|delete
on 表名
for each row
begin
触发器要执行的功能;
end$
delimiter ;

---

-- 前置操作
CREATE TABLE account(
	id INT PRIMARY KEY auto_increment, -- 账户id
	name varchar(20), -- 姓名
	money DOUBLE -- 余额
);
-- 添加数据
INSERT INTO account VALUES (NULL,'张三',1000),(NULL,'李四',1000);
 
-- 创建日志表account_log
CREATE TABLE account_log (
			id INT PRIMARY KEY auto_increment, -- 日志id
			operation VARCHAR(20), -- 操作类型（insert，update，delete）
			operation_time datetime, -- 操作时间
			operation_id INT, -- 操作表的id
			operation_params VARCHAR(200) -- 操作参数
);
 

insert型触发器

-- 创建insert型触发器，用于对account表新增数据进行日志的记录
delimiter $
CREATE TRIGGER account_insert
AFTER INSERT
ON account
FOR EACH ROW
BEGIN
		INSERT INTO account_log VALUES (NULL,'INSERT',NOW(),new.id,CONCAT('插入后{id=',new.id,'money=',new.money,'}'));
END$
delimiter ;
 
-- 向account表添加一条记录
INSERT INTO account VALUES (NULL,'王五',2000);
-- 查询account表
SELECT * FROM account;

update型触发器

-- 创建update型触发器，用于对account表修改数据进行日志的记录
delimiter $
CREATE TRIGGER account_update
AFTER UPDATE
ON account
FOR EACH ROW
BEGIN
		INSERT INTO account_log VALUES (NULL,'UPDATE',NOW(),new.id,CONCAT('更新前{id=',old.id,',name=',old.name,',money=',old.money,'}','更新后{id=',new.id,',name=',new.name,',money=',new.money,'}'));
END$
delimiter ;
 
-- 修改account表中李四的金额为2000
UPDATE account SET money=2000 WHERE id=2;
-- 查询account表
SELECT * FROM account;

delete型触发器

-- 创建delete型触发器，用于对account表删除数据进行日志的记录
delimiter $
CREATE TRIGGER account_delete
AFTER DELETE
ON account
FOR EACH ROW
BEGIN
		INSERT INTO account_log VALUES (NULL,'DELETE',NOW(),old.id,CONCAT('删除前{id=',old.id,',name=',old.name,',money=',old.money,'}'));
END$
delimiter ;
 
-- 删除account表中李四
DELETE FROM account WHERE id=2;
-- 查询account表
SELECT * FROM account;

查看和删除触发器

查看触发器
show triggers;
删除触发器
drop trigger 触发器名称;

查看当前数据库下面的所有触发器

--查看数据库下的所有触发器--
select * from sysobjects where xtype='TR'

查看某个触发器的内容

--查看触发器的具体定义--
select definition from sys.sql_modules where object_id =OBJECT_ID('Tri_insetUser')

SQLServer触发器语法

trigger_name：为用户要创建的触发器的名字，触发器的名字必须符合SQLServer的命名规则，且其名字在当前数据库中必须是惟一的。 Table、view：与触发器相关联的表或视图的名字，并且该表或视图必须已经在数据库中存在。 WITHENCRYPTION：表示对含有CREATETRIGGER文本的syscomments表进行加密，防止用户通过查询syscomments表获取触发器的代码。 AFTER：表示只有执行了指定的操作（INSERT、DELETE、或UPDATE）之后，触发器才被激活，执行触发器中的SQL语句。 FOR：表示为AFTER触发器，且该触发器仅能在表上创建。 INSTEADOF：指定触发器为INSTEADOF触发器 小注：每个表最多只能有一个INSTEADOF（INSERT、UPDATE、DELETE）触发器。然而可以为每个表创建多个视图，对每个视图都可以有不同的INSTEADOF触发器。 DELETE、INSERT、UPDATE：指明执行哪种操作，将激活触发器。至少要包含3种操作类型种的一种，也可以是3种操作语句的任意组合。其中三者的顺序不受限制，且各选项要用逗号隔开。 NOTFORREPLICATION：告诉DBMS，当复制表时，触发器不能被执行。AS：后面列出触发器将要执行的动作。 IFUPDATEcolumn：用来测定对某一确定列是INSERT操作还是UPDATE操作。如果要测试INSERT还是UPDATE操作的列多于一列，可用AND或OR逻辑连接向IFUPDATE子句添加所希望的附加列名。 IFCOLUMNS_UPDATED()：仅在INSERT和UPDATE类型的触发器中使用，检查列是被更新还是被插入。 bitwise_operator：代表位逻辑运算符，常用“&”。 updated_bitmask：表示列的整位掩码。其中最右边的位表示表或视图的第1列，左边第2位代表第2列，依此类推。 comparison_operator：表示比较操作符。可以是“=”或者“>”。“=”表示检查在updated_bitmask中定义的所有列是否都被更新，用“>”表示检查是否在updated_bitmask小注： 为了便于理解，这里给出一个使用IFCOLUMNS_UPDATED()子句的例子。如果表T包括C1、C2、C3、C4、C5和C66列，为了检查C2、C4或者C6列是否更新过，可使用42（二进制表示为“101010”）作为掩码，表示为：IF（COLUMNS_UPDATED()&42）>0；如果检查C2、C4和C63列是否都被更新过，表示为：IF（COLUMNS_UPDATED()&42）=42 sql_statement：代表包含在触发器中的处理语句。

当不再需要触发器时，可用DROPTRIGGER语句删除触发器。语法如下：

DROP  TRIGGER  trigger_name[...n]