SQL触发器

SQL server业务规则和数据的完整性机制：约束和触发器

1. 定义：一种特殊类型的存储过程。与表相关的、存储的pl/sql程序可以在特定的数据操作语句后(insert,delete,update)自动执行。(关键字trigger)可以执行其他表的语句，复杂的T-sql语句等。触发器和触发语句可以作为单个事物对待，当遇到重大错误的时候，即整个失误回滚。

2. 触发器分类

①DML（ 数据操纵语言 Data Manipulation Language）触发器：是指触发器在数据库中发生DML事件时将启用。DML事件即指在表或视图中修改数据的insert、update、delete语句。

[1]在SQL SERVER 2008中，DML触发器的实现使用两个逻辑表DELETED和INSERTED。这两个表是建立在数据库服务器的内存中，我们只有只读的权限。 DELETED和INSERED表的结构和触发器所在的数据表的结构是一样的。当触发器执行完成后，它们也就会被自动删除：INSERED表用于存放你在 操件insert、update、delete语句后，更新的记录。比如你插入一条数据，那么就会把这条记录插入到INSERTED表：DELETED表 用于存放你在操作 insert、update、delete语句前，你创建触发器表中数据库。

[2]触发器可通过数据库中的相关表实现级联更改，可以强制 比用CHECK约束定义的约束更为复杂的约束。与 CHECK 约束不同，触发器可以引用其它表中的列，例如触发器可以使用另一个表中的 SELECT 比较插入或更新的数据，以及执行其它操作。触发器也可以根据数据修改前后的表状态，再行采取对策。一个表中的多个同类触发器（INSERT、UPDATE 或 DELETE）允许采取多个不同的对策以响应同一个修改语句。

[3]与此同时，虽然触发器功能强大，轻松可靠地实现许多复杂的功能，为什么又要慎用？过多触发器会造成数据库及应用程序的维护困难，同时对触发器过分的依赖，势必影响数据库的结构，同时增加了维护的复杂程序。

②DDL（数据定义语言 Data Definition Language）触发器：是指当服务器或数据库中发生(DDL事件时将启用。DDL事件即指在表或索引中的create、alter、drop语句也。

③登陆触发器：是指当用户登录SQL SERVER实例建立会话时触发（不支持SQL Server 2000 ）。

3、应用场景：① 安全性检查

②数据确认

③数据审计功能

④数据的备份和同步

4、  常见的触发器事件    有三种：分别应用于Insert , Update , Delete 事件。 

5、语法

    创建触发器：

create trigger 名称 类型 after insert on (指定的触发器名称) 表名for each row

begin(开始)

          sql语句

end(结束)

删除触发器：  drop trigger 触发器名称;

1. 触发器的名称要唯一不能重复

2. after 在指定的操作后执行触发语句

3. 相关连得触发数据的类型要相同

4. #应用新行中每一列的值    new.列名 #应用刚删除的一列的值    old.列名

5. 总结  触发器四步骤：监视地点  监视事件 触发事件 触发时间

6、优点

触发器通过数据库中的相关表实现级联更改，通过级联引用完整性约束可以更有效地执行这些更改。触发器可以强制用比check约束定义的约束更为复杂的约束。与CHECK约束不同，触发器可以引用其它表中的列。例如，触发器可以使用另一个表中的select比较插入或更新的数据，以及执行其它操作，如修改数据或显示用户定义错误信息。触发器也可以评估数据修改前后的表状态，并根据其差异采取对策。一个表中的 多个同类触发器（INSERT、UPDATE 或 DELETE）允许采取多个不同的对策以响应同一个修改语句。

7、缺点

    触发器本身强大，但滥用触发器会造成数据库及应用程序的维护困难。在数据库操作中，通过关系、触发器、存储过程、应用程序等来实现数据操作，同时规则、约束、数据完整性。如果我们对触发器过分的依赖，势必影响数据库的结构，同时增加了维护的复杂程度。

8、两种机制的区别

    比较触发器与约束

约束和触发器在特殊情况各有优势.触发器的主要好处在于可以包含使用Transact-SQL 代码的复杂处理逻辑。触发器可以支持约束的所有功能；但触发器给出的功能并不总是最好的方法。

实体完整性应在最低级别通过索引进行强制,索引PRIMARY KEY(主键表)和UNIQUE约束(唯一标识数据库表中的每条记录)的一部分，或是在约束之外独立创建的。假设功能可以满足应用程序的功能需求，域完整性应通过 CHECK 约束进行强制，而引用完整性 (RI) 则应通过 FOREIGN KEY 约束进行强制。

约束所支持的功能无法满足应用程序的功能要求时，触发器就极为有用。例如：除非 REFERENCES子句定义了级联引用操作，否则 FOREIGN KEY(外键) 约束只能以与另一列中的值完全匹配的值来验证列值。

CHECK 约束只能根据逻辑表达式或同一表中的另一列来验证列值。如果应用程序要求根据另一个表中的列验证列值，则必须使用触发器。

约束只能通过标准的系统错误信息传递错误信息。如果应用程序要求使用（或能从中获益）自定义信息和较为复杂的错误处理，则必须使用触发器。

触发器可通过数据库中的相关表实现级联更改；通过级联引用完整性约束可以更有效地执行这些更改。

触发器可以禁止或回滚违反引用完整性的更改，实现取消数据修改。当更改外键且新值与主键不匹配时，此类触发器就可能发生作用。例如，可以在 titleauthor.title_id 上创建一个插入触发器，使它在新值与 titles.title_id 中的某个值不匹配时回滚一个插入。不过，通常使用 FOREIGN KEY 来达到这个目的。

如果触发器表上存在约束，则在 INSTEAD OF触发器执行后但在 AFTER触发器执行前检查这些约束。如果约束破坏，则回滚INSTEAD OF触发器操作并且不执行 AFTER触发器。

注：

1. references是在为表创建外键时的一个固定语法里的词

2. CHECK 约束是指约束表中某一个或者某些列中可接受的数据值或者数据格

//添加

create trigger guo BEFORE insert on o for each row

BEGIN
    if(new.much>(select gnum from g where gid=new.gid)) then
    set new.much = (select gnum from g where gid=new.gid);
    update g set gnum = gnum-gnum where gid=new.gid;
    else
    update g set gnum = gnum-new.much where gid=new.gid;
    end if;
END
drop trigger b;
insert into o values(null,'1','4');

//删除

create trigger guo1 after delete on o for each row
BEGIN
    update g set gnum = gnum+old.much where gid=old.gid;
END
delete from o where oid='7';

create trigger guo2 before update on o for each row
BEGIN
    if new.much>(select gnum from g where gid=new.gid)+old.much then
    set new.much = (select gnum from g where gid=new.gid)+old.much;
    update g set gnum = gnum-gnum where gid=new.gid;
    else
    update g set gnum = gnum+old.much-new.much where gid=new.gid;
    end if;
END