SQL语法 - 使用触发器自动执行复杂操作

触发器是一种特殊的存储过程，它会在数据库中某个特定的事件发生时自动执行。这些事件可以是INSERT、UPDATE或DELETE操作。触发器可以在事件发生之前（BEFORE）或之后（AFTER）执行，甚至可以阻止某些不符合条件的操作。下面给出MySQL与SQL Server的触发器的语法对比。

MySQL

MySQL的触发器允许在执行增删改操作的之前或之后执行触发器内定义的语句，即增删改操作总是生效，与SQL Server的After类型的触发器类似。

但MySQL的触发器是针对行级别的，即使一条DML语句影响了多行，也总是一行一行的触发去处理，这一点与SQL Server的After类型的触发器并不一样。

语法：

CREATE
    [DEFINER = { user | CURRENT_USER }]
TRIGGER trigger_name
trigger_time trigger_event
ON tbl_name FOR EACH ROW
　　[trigger_order]
trigger_body

trigger_time: { BEFORE | AFTER }

trigger_event: { INSERT | UPDATE | DELETE }

trigger_order: { FOLLOWS | PRECEDES } other_trigger_name

-----------------------------------------------------------

eg:
create trigger trigger_name 
after insert on table_name for each row
begin
...
end;

• NEW与OLD
• MySQL 中定义了 NEW 和 OLD，用来表示触发器的所在表中，触发了触发器的那一行数据，来引用触发器中发生变化的记录内容，具体地：
  1、在INSERT型触发器中，NEW用来表示将要（BEFORE）或已经（AFTER）插入的新数据；
  2、在UPDATE型触发器中，OLD用来表示将要或已经被修改的原数据，NEW用来表示将要或已经修改为的新数据；
  3、在DELETE型触发器中，OLD用来表示将要或已经被删除的原数据；
• 使用方法：NEW.columnName | OLD.columnName
• 另外，OLD是只读的，而NEW则可以在触发器中使用 SET 赋值，这样不会再次触发触发器，造成循环调用（如每插入一个学生前，都在其学号前加“2013”）
• 注意：在某个表上定义的触发器的执行块中，不能继续对该表执行增删改的操作。
• 注意：若想在插入或更新数据前修改最新的数据，则需要使用BEFORE类型的触发器，不能是AFTER

示例：

mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
mysql> INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00);

mysql> delimiter $$
mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON account
    -> FOR EACH ROW
    -> BEGIN
    -> 　　IF NEW.amount < 0 THEN
    -> 　　　　SET NEW.amount = 0;
    -> 　　ELSEIF NEW.amount > 100 THEN
    -> 　　　　SET NEW.amount = 100;
    -> 　　END IF;
    -> END$$
mysql> delimiter ;

mysql> update account set amount=-10 where acct_num=137;

mysql> select * from account;
+----------+---------+
| acct_num | amount  |
+----------+---------+
|      137 |    0.00 |
|      141 | 1937.50 |
|       97 | -100.00 |
+----------+---------+

mysql> update account set amount=200 where acct_num=137;

mysql> select * from account;
+----------+---------+
| acct_num | amount  |
+----------+---------+
|      137 |  100.00 |
|      141 | 1937.50 |
|       97 | -100.00 |
+----------+---------+

触发器的查看与删除：

#触发器的查看：
SHOW TRIGGERS;                                  #方式一：查看所有触发器
SHOW CREATE TRIGGER trigger_name;               #方式二：查看指定触发器
SELECT * FROM information_schema.TRIGGERS;      #方式三：从系统库中查看

-------------------------------------------------------------------

#触发器的删除：
DROP TRIGGER [IF EXISTS] trigger_name;

SQL Server

SQL Server的触发器根据是否真正执行增删改操作分为INSTEAD OF与AFTER两类。

INSTEAD OF触发器又称为替代触发器，替代触发器会将触发触发器的增删改命令替换为触发器内部定义的语句，即触发触发器的增删改命令并不会真正执行，比如：以用户的 update 操作为例，首先获取永久表中相关数据的副本保存到 Deleted 临时表中，对副本数据执行update操作，将结果保存到 Inserted 临时表中，而此时永久表没有任何变化，触发器开始执行，如果触发器没有对永久表操作的相关代码，那么用户的操作将无法作用到永久表。即触发触发器的增删改命令仅仅只是激活了触发器，并不会真正对表进行操作，对表执行操作的反而是触发器内部定义的语句。执行顺序：先根据用户提交的 DML 操作（ insert、update、delete ），将意欲操作的相关数据填充到 Inserted 表和 Deleted 表，但不会对SQL物理表进行修改，最后执行触发器内部定义的语句。

After触发器可由 DDL操作或 DML操作执行触发。该触发器按语句触发而不是按行触发，即一条语句不管影响了多少行，After 触发器只能触发一次，因此在设计时需要考虑到影响多行的情况，比如一条INSERT语句里带有多行的情况。执行顺序：先执行用户提交的 DML、DML操作，对实际的物理表做出修改，约束检查通过后将相关数据填充到 Inserted 表和 Deleted 表，最后执行触发器内部定义的语句。

语法：

CREATE TRIGGER trigger_name
ON {table|view} 
[WITH ENCRYPTION|EXECUTE AS] 
{FOR|AFTER|INSTEAD OF} {[CREATE|ALTER|DROP|INSERT|UPDATE|DELETE ]} 
[NOT FOR REPLICATION] 
AS 
sql_statement [1...n ] 

1. trigger_name
这是触发器的名字，它应该遵守SQL Server中标识符规则。

2. table|view
触发器创建在这个表/视图上。

3. ENCRYPTION
这个选项是可选的。如果设置了这个选项，那么这个CREATE TRIGGER语句的原文本将会被加密。

4. EXECUTE AS
这个选项是可选的，这个选项设置了，那么这个触发器就会在安全上下文之下执行。

5. FOR/AFTER
FOR/AFTER指定了触发器是After触发器。如果只仅仅指定了FOR关键字，那么AFTER是默认的。AFTER触发器不能在视图上定义。

6. INSTEAD OF
INSTEAD OF指定了这个触发器是INSTEAD OF触发器

7.  CREATE|ALTER|DROP|INSERT|UPDATE|DELETE
这些关键字指定了哪个操作将在触发器中触发。可以使用这些关键字中的一个或者任何顺序的关键字组合。

8. NOT FOR REPLICATION
这个参数说明了这个触发器不会在响应进程修改表(即使)卷入了这个触发器中触发。

9. AS
在这个之后，我们可以指定触发器执行时的操作和条件

10. sql_statement
这些是触发器的条件和操作。这个触发器这些操作指定了在T-SQL语句之中。

-----------------------------------------------------------

eg:
-- after类型的触发器：
create trigger trigger_name 
on table_name for insert as     --这里的for等价于after
begin
...
end;

-- instead of类型的触发器
create trigger trigger_name 
on table_name instead of insert as
begin
...
end;

• Inserted与Deleted
• 这两个表由系统来维护，它们存在于内存中而不是在数据库中，可以理解为一个虚拟的表。
• 这两个表的结构总是与被该触发器作用的表的结构相同。
• 触发器执行完成后，与该触发器相关的这两个表也被删除。
• Deleted表存放由于执行Delete或Update语句而要从表中删除的所有行。
• Inserted表存放由于执行Insert或Update语句而要向表中插入的所有行。
• 使用方法与普通的表一致。

示例：

-- 在“商品”表建立删除触发器，实现“商品”表和“订单”表的级联删除。
create trigger goodsdelete_trigger_商品_delete
on 商品
after delete
as
    delete from 订单 where 订单.pid in (select pid from deleted);

-- 在删除学生表时，如果该学生仍有借书记录（未还）则不能删除
create trigger trigger_学生_Delete
on 学生
instead of Delete
as
begin
    if not exists(select * from 借书记录, deleted where 借书记录.学号 = deleted.学号)
        delete from 学生 where 学生.学号 in (select 学号 from deleted)
end;

触发器的查看、修改与删除：

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

--查看指定触发器的内容
use 数据库名 
go 
exec sp_helptext '触发器名称' 

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

--修改触发器语法 
ALTER TRIGGER trigger_name  
 ON table_name  
 [ WITH ENCRYPTION ]  
 FOR {[DELETE][,][INSERT][,][UPDATE]} 
 AS 
 sql_statement; 

--触发器的删除
DROP TRIGGER trigger_name