SqlServer 2005 触发器

一、触发器的定义:

触发器在本质上是一种特殊的存储过程，一种当执行你特定SQL语句时被自动调用的存储过程。

二、触发器的作用

在SQL Server 2005里，可以用两种方法来保证数据的有效性和完整性：约束（check）和触发器（Trigger）。约束是直接设置于数据表内，只能现实一些比较简单的功能操作，如：实现字段有效性和唯一性的检查、自动填入默认值、确保字段数据不重复（即主键）、确保数据表对应的完整性（即外键）等功能。

触发器是针对数据表（库）的特殊的存储过程，当这个表发生了 Insert、Update或Delete操作时，会自动激活执行的，可以处理各种复杂的操作。在SQL Server 2005中，触发器有了更进一步的功能，在数据表（库）发生Create、Alter和Drop操作时，也会自动激活执行。

三、触发器的常用功能

        1、完成比约束更复杂的数据约束：触发器可以实现比约束更为复杂的数据约束

2、 检查所做的SQL是否允许：触发器可以检查SQL所做的操作是否被允许。例如：在产品库存表里，如果要删除一条产品记录，在删除记录时，触发器可以检查该产品库存数量是否为零，如果不为零则取消该删除操作。

3、  修改其它数据表里的数据：当一个SQL语句对数据表进行操作的时候，触发器可以根据该SQL语句的操作情况来对另一个数据表进行操作。例如：一个订单取消的时候，那么触发器可以自动修改产品库存表，在订购量的字段上减去被取消订单的订购数量。

4、  调用更多的存储过程：约束的本身是不能调用存储过程的，但是触发器本身就是一种存储过程，而存储过程是可以嵌套使用的，所以触发器也可以调用一个或多过存储过程。

5、  发送SQL Mail：在SQL语句执行完之后，触发器可以判断更改过的记录是否达到一定条件，如果达到这个条件的话，触发器可以自动调用SQL Mail来发送邮件。例如：当一个订单交费之后，可以物流人员发送Email，通知他尽快发货。

6、  返回自定义的错误信息：约束是不能返回信息的，而触发器可以。例如插入一条重复记录时，可以返回一个具体的友好的错误信息给前台应用程序。

7、  更改原本要操作的SQL语句：触发器可以修改原本要操作的SQL语句，例如原本的SQL语句是要删除数据表里的记录，但该数据表里的记录是最要记录，不允许删除的，那么触发器可以不执行该语句。

8、  防止数据表构结更改或数据表被删除：为了保护已经建好的数据表，触发器可以在接收到Drop和Alter开头的SQL语句里，不进行对数据表的操作。

 

 四、触发器的种类

    在SQL Server 2005中，触发器可以分为两大类：DML触发器和DDL触发器

l  DML触发器：DML触发器是当数据库服务器中发生数据操作语言（Data Manipulation Language）事件时执行的存储过程。DML触发器又分为两类：After触发器和Instead Of触发器

l  DDL触发器：DDL触发器是在响应数据定义语言（Data Definition Language）事件时执行的存储过程。DDL触发器一般用于执行数据库中管理任务。如审核和规范数据库操作、防止数据库表结构被修改等。

五、  DML触发器的分类

SQL Server 2005的DML触发器分为两类：

l  After触发器：这类触发器是在记录已经改变完之后（after），才会被激活执行，它主要是用于记录变更后的处理或检查，一旦发现错误，也可以用Rollback Transaction语句来回滚本次的操作。

2  Instead Of触发器：这类触发器一般是用来取代原本的操作，在记录变更之前发生的，它并不去执行原来SQL语句里的操作（Insert、Update、Delete），而去执行触发器本身所定义的操作。

六、  DML触发器的工作原理

在SQL Server 2005里，为每个DML触发器都定义了两个特殊的表，一个是插入表，一个是删除表。这两个表是建在数据库服务器的内存中的，是由系统管理的逻辑表，而不是真正存储在数据库中的物理表。对于这两个表，用户只有读取的权限，没有修改的权限。

这两个表的结构与触发器所在数据表的结构是完全一致的，当触发器的工作完成之后，这两个表也将会从内存中删除。

插入表里存放的是更新前的记录：对于插入记录操作来说，插入表里存放的是要插入的数据；对于更新记录操作来说，插入表里存放的是要更新的记录。

删除表里存放的是更新后的记录：对于更新记录操作来说，删除表里存放的是更新前的记录（更新完后即被删除）；对于删除记录操作来说，删除表里存入的是被删除的旧记录。

下面看一下触发器的工作原理。

七、  After触发器的工作原理

After触发器是在记录更变完之后才被激活执行的。以删除记录为例：当SQL Server接收到一个要执行删除操作的SQL语句时，SQL Server先将要删除的记录存放在删除表里，然后把数据表里的记录删除，再激活After触发器，执行After触发器里的SQL语句。执行完毕之后，删除内存中的删除表，退出整个操作。

还是举上面的例子：在产品库存表里，如果要删除一条产品记录，在删除记录时，触发器可以检查该产品库存数量是否为零，如果不为零则取消删除操作。看一下数据库是怎么操作的：

（1）接收SQL语句，将要从产品库存表里删除的产品记录取出来，放在删除表里。

（2）从产品库存表里删除该产品记录。

（3）从删除表里读出该产品的库存数量字段，判断是不是为零，如果为零的话，完成操作，从内存里清除删除表；如果不为零的话，用Rollback Transaction语句来回滚操作。

八、  Instead Of触发器的工作原理

Instead Of触发器与After触发器不同。After触发器是在Insert、Update和Delete操作完成后才激活的，而Instead Of触发器，是在这些操作进行之前就激活了，并且不再去执行原来的SQL操作，而去运行触发器本身的SQL语句。

九、 基本语法

        create trigger trigger_name
           on {table_name | view_name}
           {for | After | Instead of }
           [ insert, update,delete ]
           as
           sql_statement

十、示例

1﹕在Orders表中建立触发器﹐当向Orders表中插入一条订单记录时﹐检查goods表的货品状态status是否为1(正在整理)﹐是﹐则不能往Orders表加入该订单。

create trigger orderinsert
on orders
after insert
as                                                                                                                                                                    

begin transaction
if (select status from goods,inserted
where goods.name=inserted.goodsname)=1
begin
print 'the goods is being processed'
print 'the order cannot be committed'
rollback transaction   --回滚﹐避免加入
end

2﹕在Orders表建立一个插入触发器﹐在添加一条订单时﹐减少Goods表相应的货品记录中的库存。
create trigger orderinsert1
on orders
after insert
as

 

 

begin transaction
update goods set storage=storage-inserted.quantity
from goods,inserted
where
goods.name=inserted.goodsname

3﹕在Goods表建立删除触发器﹐实现Goods表和Orders表的级联删除。
create trigger goodsdelete
on goods
after delete
as

begin transaction
delete from orders
where goodsname in
(select name from deleted)
4﹕在Orders表建立一个更新触发器﹐监视Orders表的订单日期(OrderDate)列﹐使其不能手工修改.
create trigger orderdateupdate
on orders
after update
as

begin transaction
if update(orderdate)
begin
raiserror(' orderdate cannot be modified',10,1)
rollback transaction
end
5﹕在Orders表建立一个插入触发器﹐保证向Orders表插入的货品名必须要在Goods表中一定存在。
create trigger orderinsert3
on orders
after insert
as

begin transaction
if (select count(*) from goods,inserted where goods.name=inserted.goodsname)=0
begin
print ' no entry in goods for this order'
rollback transaction
end

6.

CREATE TRIGGER [tri_Basic_PL5]
ON  [dbo].[Basic_PL5]
For Update
AS
IF Update(DateNumber) or Update(PrizeNumber)
BEGIN
 declare @c int
 select @c = count(*)
 from inserted
 IF @c > 0
BEGIN
 Declare @DateNumber nvarchar(50),@PrizeNumber nvarchar(50),@PrizeTime DateTime
 select @DateNumber = DateNumber,@PrizeNumber=PrizeNumber,@PrizeTime = PrizeTime
 from inserted

    Update Integrate_PL5 Set PrizeNumber=@PrizeNumber
 Where DateNumber = @DateNumber

 declare @GeWei int,@ShiWei int,@BaiWei int ,@QianWei int ,@WanWei int;
 select @GeWei = substring(@PrizeNumber,5,1),
   @ShiWei = substring(@PrizeNumber,4,1),
   @BaiWei = substring(@PrizeNumber,3,1),
            @QianWei=substring(@PrizeNumber,2,1),
            @WanWei=substring(@PrizeNumber,1,1)

end

end

7.

CREATE TRIGGER [tri_Insert_Basic_PL5]
On [dbo].[Basic_PL5]
for Insert
AS
BEGIN
 Insert Into Integrate_PL5(DateNumber,PrizeNumber,PrizeTime)
 select DateNumber,PrizeNumber,PrizeTime from inserted
END