SqlServer的触发器

浏览数：16 / 时间：2015年06月12日

触发器是Sqlserver响应以下任意语句而自动执行的一天T-SQL语句。delete,insert,update.表和视图支持触发器。（但是临时表不支持）。

我们在哪些业务场景需要用到触发器呢？

1.每当订购一个产品时，都从库存数量中减去订购的数量。

2.每当增加一个顾客到某个数据库表时，都检查其电话号码格式是否正确，州的缩写是否为大写。

1.首先，创建触发器。触发器用create trigger语句创建。下面是一个简单的例子:

create trigger newproduct_trigger on products
after insert
as 
select ‘product added‘

触发器定义为after insert,所以此触发器将在insert语句成功执行后执行。触发器按每个表每个事件每次地定义，因此每个表最多支持3个触发器（insert,update,delete各一个触发器）

2.删除触发器。我们用drop trigger语句删除一个触发器。如下所示:

drop trigger newproduct_trigger;

3.启用和禁用触发器,我们用disable trigger和enable trigger语句:

--禁用触发器
disable trigger newproduct_trigger on products;


--重新启用触发器
enable trigger newproduct_trigger on products;

4.我们如何确定这个表有没有触发器呢？我们可以使用内建的存储过程sp_helptrigger：

--sp_helptrigger取一个表名并返回触发器的一个列表。
SP_HELPTRIGGER products;

5.使用触发器。
5.1 insert触发器

insert触发器在insert语句执行之后执行。在insert触发器代码内，可引用一个名为inserted的虚拟表，访问被插入的行。

--创建一个insert触发器，当插入一个新订单时，生成一个新订单号保存到order_num中
create trigger neworder_trigger on orders
after insert
as
select @@identity as order_num;


--为测试这个触发器，试着插入一下新行。
insert into orders(order_date,cust_id) values(GetDate(),10001)

--输出     order_num
--            20010

5.2 delete触发器
delete触发器在delete语句执行之后执行。在delete触发器代码内，可引用一个名为deleted的虚拟表，访问被删除的行。

--使用delete触发器保存将要被删除的行到一个存档表中
create trigger deleteorder_trigger on orders
after delete
as
begin
    insert into orders_archive(order_num,order_date,cust_id)  select order_num,order_date,cust_id from deleted;
end;

此触发器在从表orders中删除行时知行。它使用insert select语句保存deleted中的行到一个名为orders_achive的存档表中。

5.3 update触发器

update触发器在update语句执行之后执行。在update触发器代码中，你可以引用名为deleted的虚拟表访问以前的值，引用名为inserted的虚拟表访问新更新的值。

--下面的例子中保证州名缩写总是大写。
create trigger vendor_trigger on vendors
after insert,update
as
begin
    update vendors
    set vend_state=upper(vend_state)
    where vend_id in (select vend_id from inserted);
end;