【SQL】- 基础知识梳理（五） - 触发器

触发器的概念

触发器对表进行插入、更新、删除的时候会自动执行的特殊存储过程

触发器的语法

create trigger tgr_name

on table_name

with encrypion –加密触发器

for  [DELETE, INSERT, UPDATE]

as Transact-SQL

触发器的分类

触发器可以分为两类：DML触发器和DDL触发器。

DML触发器分为：
    1、 after触发器（之后触发）
        a、 insert触发器
        b、 update触发器
        c、 delete触发器
    2、 instead of 触发器 （之前触发）

  其中after触发器要求只有执行某一操作insert、update、delete之后触发器才被触发，且只能定义在表上。

  而instead of触发器表示并不执行其定义的操作（insert、update、delete）而仅是执行触发器本身。往往这种触发器会有很多分支判断语句在里面，根据不用的条件做不同的动作。既可以在表上定义instead of触发器，也可以在视图上定义。

触发器有两个特殊的表：插入表（instered表）和删除表（deleted表）。
这两张是逻辑表也是虚表。有系统在内存中创建者两张表，不会存储在数据库中。而且两张表的都是只读的，只能读取数据而不能修改数据。这两张表的结果总是与被改触发器应用的表的结构相同。当触发器完成工作后，这两张表就会被删除。
Inserted表的数据是插入或是修改后的数据，而deleted表的数据是更新前的或是删除的数据。
Update数据的时候就是先删除表记录，然后增加一条记录。这样在inserted和deleted表就都有update后的数据记录了。

实战创建简单的触发器

上图中上面是table3，下面是table2，请编写一个插入类型的触发器

if (object_id('tgr_table3_insert', 'tr') is not null)
    drop trigger tgr_table3_insert
go
create trigger tgr_table3_insert
on table3
    for insert --插入触发
as
    --定义变量
    declare @id int, @name varchar(20), @temp int;
    --在inserted表中查询已经插入记录信息
    select @id = id, @name = name from inserted;
    set @name = @name + convert(varchar, @id);
    set @temp = @id / 2;    
    insert into table2 (id,name,value) values( @id,@name,  @temp);
    print '添加table2中成功！';
go

测试下编写的触发器

insert into table3 (id,name) values(10,'饿了');

执行结果如下

 请创建一个instead of触发器

if (object_id('tgr_table3_delete', 'tr') is not null)
    drop trigger tgr_table3_delete
go
create trigger tgr_table3_delete
on table3
    instead of delete --删除触发
as
 IF NOT EXISTS(SELECT * FROM table2 WHERE id=( select id from deleted))
  BEGIN
    ROLLBACK TRANSACTION
    PRINT '要处理table3的id不在table2中！'
  END
 ELSE
  BEGIN
    DELETE FROM TABLE3 WHERE ID IN (select ID from deleted)
    PRINT '已经成功处理table3记录！'
  END
go

测试下上面这个触发器

DELETE FROM table3 WHERE ID=5

执行结果如下：

 

参考：http://www.cnblogs.com/hoojo/archive/2011/07/20/2111316.html