数据库-触发器 trigger

说明：本文内容为网站上(博客园或CSDN)收集整理而来，是对相关知识的记录以方便及时复习学习，并非原创。　

　触发器每个数据库的创建方式不同，但作用都是类似的，触发器是一种特殊的存储过程，它不能被显式地调用，而是在往表中插入记录﹑更新记录或者删除记录时被自动地激活。

MySql 触发器

　　1、概念

　　　触发器(trigger)：监视某种情况，并触发某种操作。

　　　触发器的使用场景有：数据确认、实施复杂的安全性检查、做审计，跟踪表上所做的数据操作等、数据的备份和同步；　　

　　　触发器也可以调用存储过程和存储函数。

　　　一个表相同的监听事件只能建一个触发器，比如插入操作引起的触发事件只能有一个，再建立因插入引起的触发会提示触发器创建失败。

　　　对于insert而言，新插入的行用new来表示，行中的每一列的值用new.列名来表示。

　　　对于delete而言：原本有一行,后来被删除，想引用被删除的这一行，用old来表示，old.列名可以引用被删除的行的值。

　　2、语法

　　触发器创建语法四要素：1.监视地点(table) 2.监视事件(insert/update/delete) 3.触发时间(after/before) 4.触发事件(insert/update/delete)　　

create trigger triggerName
after/before 
insert/update/delete on TableName
for each row   --这句话在mysql是固定的
begin
    -- sql语句;
end;

　　3、实例

　　　实例1:订单表t_order插入一条记录，更新商品表t_goods中对应的商品数量；　　　

DELIMITER //  --mysql的定义语句开始标志
create trigger trigger1
after insert on t_order 
for each row
begin 
update t_goods set num=num-new.much where id = new.id;
end //  --语句结束标志

　　  实例2：撤销一个订单的时，将对应商品的数量加回来；

DELIMITER//
create trigger trigger2
after delete on t_order
for each row
begin
update t_goods set num = num + old.much where id = old.g_id ;
end //

　　实例3：修改一个订单的数量时,更新商品表的商品数量（where后面还可以写成：where id = new.g_id）

DELIMITER //
create trigger trigger3
after update on t_order
for each row
begin 
update t_goods set num=num+old.much-new.much where id = old.g_id ; 
end //

　  实例4：插入订单表时同时插入订单日志表，记录订单日志,且日志表需要用到订单表的主键(插入一张表时需要用到另一张表的主键)；

DELIMITER//
create trigger trigger4
after insert on t_order 
for each row
begin
insert into t_order_log (o_id,createtime) values(new.id,now());
end//

---

---

SQL Server 触发器

　　1、概念

　　　　与MySQL不同的是，触发器的触发时机不实用"after/before"而是用"instead of/after";

　　　　一个表或视图的每一个修改动作(Insert、Update和Delete)都可以有一个“Instead of” 触发器，一个表的每个修改动作都可以有多个“After”触发器。

　　2、两张内存中的逻辑表Inserted和Deleted

　　　　SQL Server为每个触发器都创建了两个专用表:Inserted表和Deleted表。　　　　

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

操作	Inserted逻辑表	Deleted逻辑表
增加记录（insert）	存放增加的记录
删除记录（delete）		存放被删除的记录
修改记录（update）	存放更新后的记录	存放更新前的记录

　　3、语法　　　　

--创建触发器
create trigger triggerName 
on  {table_name|view_name} 
{After|Instead of} 
{insert|update|delete}
as 
begin
     --逻辑处理语句
end

--查看所有触发器
select * from sysobjects where xtype='TR'

　　4、实例

　　实例1：

　　实现功能：在删除学生表(students)时，如果借书表(borrowRecord)中仍有记录（未还）则不能删除;　　

Create trigger trig_students_delete
on students
instead of delete
as
begin
    if not exists(select * from borrowRecord b,deleted d where b.studentId = d.id)
        delete students where id in (select id from deleted )        
end

　　说明：line6中用到了deleted这个虚拟表，因为该触发器创建在表students上，所以这里的deleted表结构和students一模一样；当students有删除操作时，会将students中删除的记录写到表deleted中，所以line6中的select语句查询的正是：查询被删除学生是否在借书表borrowRecord中还有记录；

　　注意：当被删学生(id=2)还有借阅记录时，执行delete students where id = 2;删除操作时，语句执行正常，但是实际上该学生记录并没有在students表中删掉。这种情况容易导致bug,所以检查是否还有借阅记录可以先查出来放到程序里判断；

　　实例2：　在订单表(OrderRecord)建立一个插入触发器，在添加一条订单时，减少商品表(prodect)相应的货品记录中的库存。　　

create trigger trigger_OrderRecord_insert
on OrderRecord
after insert
as
begin
    update prodect set number = number - inserted.num
    from prodect , inserted 
    where prodect.pid = inserted.pid
end

　　说明：line6中用到了inserted这个虚拟表，因为该触发器创建在表OrderRecord上，所以这里的inserted表结构和OrderRecord一模一样；当OrderRecord有插入操作时，会将OrderRecord中插入的记录写到表inserted中；

附注：

　　以上内容参考来自博客园园友的宝贵资料汇总整理，详细：http://www.cnblogs.com/rainman/p/3675834.html、http://www.cnblogs.com/zzwlovegfj/archive/2012/07/04/2576989.html