关于 MS SQL 触发器 的一些用法

/********************************************************************

 进行商品下架操作的时候，同时更新库存信息表和架存商品信息表中的相关信息

********************************************************************/

create trigger insert_reduce

on 下架记录after insert

as begin

       declare @newsq int,@oldsq int,@newpq int,@oldpq int,@NewNo int,@ShopId int,@ReduceQuantiry int,@dispose char;

       set @NewNo=(select 条形码from inserted); --获得新插入的条形码

       set @ShopId=(select 商店编号from inserted); --获得新插入的商店编号

       set @ReduceQuantiry = (select 数量from inserted); --获得新插入的数量

       set @dispose =(select 处理方式from inserted); --获得下架商品的处理方式

 

    if @dispose = 1 --返回仓库

       begin

       select @oldsq = 库存数量from 库存信息where 条形码= @NewNo;

       select @oldpq = 架存数量from 架存商品信息where 条形码= @NewNo;

 

       set @newsq=@oldsq+@ReduceQuantiry;--从库存数量中加上刚下架货物的数量

       set @newpq=@oldpq-@ReduceQuantiry;--架存商品数量减去刚下架货物的数量

 

       if(@newpq <= 0)/*如果用户输入的下架货物的数量大于架存商品的数量*/

          begin

           set @newpq=0;/* 将架存商品数量更新为0,即,将现有架存全部下架返回仓库*/

           /* set @temp2=abs(temp1);  /*abs() 求绝对值的函数*/ */

           set @newsq = @oldsq+@oldpq;/*新库存=旧库存+旧架存*/

           declare @NewEmpl int @time datetime;

           set @NewEmpl =(select 店员编号from inserted);

           set @time =(select 时间from inserted);

           /*将实际的下架数量更新到下架记录中*/

           update 下架记录set 数量= @oldpq where 条形码= @NewNo and 店员编号= @NewEmpl and 时间= @time;

          end

       if(@newsq <= 0)

          begin

            set @newsq=0;

          end

           update 库存信息set 库存数量= @newsq where 条形码= @NewNo and 商店编号= @ShopId;

           update 架存商品信息set 架存数量= @newpq where 条形码= @NewNo and 商店编号= @ShopId;

       end

    if @dispose = 0 --销毁

       begin

       select @oldpq = 架存数量from 架存商品信息where 条形码= @NewNo;

       set @newpq=@oldpq-@ReduceQuantiry;--架存商品数量减去要销毁货物的数量

       if(@newpq <= 0)/*如果用户输入的下架数量多于现有架存数量，则全部下架销毁*/

          begin

           set @newpq=0;

          end

       update 架存商品信息set 架存数量= @newpq where 条形码= @NewNo and 商店编号=@ShopId;

       end

end;

/********************************************************************

 进行上架操作的时候，同时更新库存信息表和架存商品信息表中的相关信息

********************************************************************/

create trigger insert_add

on 上架记录after insert

as begin

       declare @newsq int,@oldsq int,@newpq int,@oldpq int,@AddQuantiry int,@NewNo int,@ShopId int;

       set @NewNo=(select 条形码from inserted); --获得新插入的条形码

       set @ShopId=(select 商店编号from inserted); --获得新插入的商店编号

       set @AddQuantiry = (select 数量from inserted); --获得新插入的数量 deleted

 

       set @oldsq = (select 库存数量from 库存信息where 条形码= @NewNo and 商店编号=@ShopId);

       set @oldpq = (select 架存数量from 架存商品信息where 条形码= @NewNo and 商店编号=@ShopId);

 

       set @newsq=@oldsq-@AddQuantiry;--从库存数量中减去上架货物的数量

       set @newpq=@oldpq+@AddQuantiry;--架存商品数量加上新上架货物的数量

 

       if(@newsq <= 0)/*如果用户输入的上架货物的数量大于库存商品的数量*/

          begin

           set @newsq=0;/* 将新库存数量更新为0,即，将现有库存全部上架*/

           set @newpq=@oldpq+@oldsq;/*新的架存数量=旧架存数量+旧库存数量*/

           declare @NewEmpl int @time datetime;

           set @NewEmpl =(select 店员编号from inserted);

           set @time =(select 时间from inserted);

           /*将实际的上架数量更新到上架记录中*/

           update 上架记录set 数量= @oldsq where 条形码= @NewNo and 店员编号= @NewEmpl and 时间= @time;

          end

       update 库存信息set 库存数量= @newsq where 条形码= @NewNo and 商店编号=@ShopId;

       update 架存商品信息set 架存数量= @newpq where 条形码= @NewNo and 商店编号=@ShopId;

end;

/*******************************************************************/

比如，这么两个表：

 

     Create Table Student(             --学生表

       StudentID int primary key,      --学号

       ....

      )

 

     Create Table BorrowRecord(              --学生借书记录表

       BorrowRecord  int identity(1,1),      --流水号

       StudentID     int ,                   --学号

       BorrowDate    datetime,               --借出时间

       ReturnDAte    Datetime,               --归还时间

       ...

     )

 

    用到的功能有:

       1.如果我更改了学生的学号,我希望他的借书记录仍然与这个学生相关(也就是同时更改借书记录表的学号);

       2.如果该学生已经毕业，我希望删除他的学号的同时，也删除它的借书记录。

    等等。

 

    这时候可以用到触发器。对于，创建一个Update触发器：

 

    Create Trigger truStudent

      On Student                        --在Student表中创建触发器

      for Update                         --为什么事件触发

    As                                       --事件触发后所要做的事情

      if Update(StudentID)          

      begin

 

        Update BorrowRecord

          Set StudentID=i.StudentID

          From BorrowRecord br , Deleted  d ,Inserted i     --Deleted和Inserted临时表

          Where br.StudentID=d.StudentID

 

      end      

               

    理解触发器里面的两个临时的表：Deleted , Inserted 。注意Deleted 与Inserted分别表示触发事件的表“旧的一条记录”和“新的一条记录”。

    一个数据库系统中有两个虚拟表用于存储在表中记录改动的信息，分别是：

                            虚拟表Inserted                    虚拟表Deleted

在表记录新增时   存放新增的记录                       不存储记录

        修改时         存放用来更新的新记录                 存放更新前的记录

        删除时         不存储记录                           存放被删除的记录

 

    一个Update 的过程可以看作为：生成新的记录到Inserted表，复制旧的记录到Deleted表，然后删除Student记录并写入新纪录。

 

    对于，创建一个Delete触发器

    Create trigger trdStudent

      On Student

      for Delete

    As

      Delete BorrowRecord

        From BorrowRecord br , Delted d

        Where br.StudentID=d.StudentID

/*******************************************************************/

Create trigger DeleteWareInfor On 商品信息 for Delete

As begin

     Delete 库存信息From 库存信息a,Deleted b Where a.条形码= b.条形码;

     Delete 架存商品信息From 架存商品信息a,Deleted b Where a.条形码= b.条形码;

     Delete 销售记录From 销售记录a,Deleted b Where a.条形码= b.条形码;

     Delete 下架记录From 下架记录a,Deleted b Where a.条形码= b.条形码;

     Delete 上架记录From 上架记录a,Deleted b Where a.条形码= b.条形码;

end;

 

/*创建触发器脚本 因为有外键约束 这个触发器没有作用，后面的删除操作是在Delete事件之后执行的，所以会触发外键约束的冲突。*/

     Create trigger DeleteWareInfor On 商品信息

       AFTER Delete

     As begin

       Delete 库存信息From 库存信息a,Deleted b Where a.条形码= b.条形码;

       Delete 架存商品信息From 架存商品信息a,Deleted b Where a.条形码= b.条形码;

       Delete 销售记录From 销售记录a,Deleted b Where a.条形码= b.条形码;

       Delete 下架记录From 下架记录a,Deleted b Where a.条形码= b.条形码;

       Delete 上架记录From 上架记录a,Deleted b Where a.条形码= b.条形码;

     end;