触发器的使用

触发器实质上是特殊类型的存储过程，当满足触发条件时，就可以触发从而能完成各种不同的管理任务。

概述

触发器和数据表紧密连在一起，只要谈到触发器，一定是说某个数据表的触发器。因此，触发器是在数据表的基础上进行定义，数据表也成为触发器表。触发器作为特殊类型的存储过程，和存储过程的区别是：存储过程可以被调用执行，触发器不可。

触发器的分类

SQL Server 2008中提供3种类型的触发器，分别是DML触发器、DLL触发器和登录触发器。

【DML触发器】当数据库中发生数据操作语言事件调用时执行DML触发器。DML事件包括在指定表或视图中修改数据的insert语句、update语句或delete语句。一个数据表中可以有多个触发器。同一类型的触发器也可以有多个。DML触发器包括以下类型：

• AFTER触发器。在执行insert、update或delete语句操作之后执行after触发器。比如对某个表中的数据进行了更新操作后，要求立即对相关的表进行指定的操作，这时就可以采用AFTER触发器。
• INSTEAD OF触发器。当对表进行insert、update或delete操作时，系统不是直接对表执行这些操作，而是把操作内容交个触发器，让触发器检查所进行的操作是否正确。如果正确才进行相应的操作。INSTAND OF触发器的操作有点类似于完整性约束。
• CLR触发器。CLR触发器可以是AFTER触发器或INSTAND OF触发器。CLR触发器还可以是DLL触发器。CLR触发器将执行在托管代码（在.NET Framework中创建并在SQL Server中上载的程序集的成员）中编写的方法。

【DDL触发器】响应各种数据定义语言事件，这些事件主要与以关键字create、alter和drop开头的语句对应。

【登录触发器】登录触发器在遇到login事件时，用户身份验证阶段完成之后且用户会话实际建立之前触发

DLL触发器的使用

DLL触发器主要用于创建、删除或者修改数据库、数据表等环境。例如，当无意中删除数据库或者数据表时，DLL触发器将提醒操作员，并拒绝删除操作。

create trigger triggername on databasefor drop_table,alter_tableas print '对不起,您不能删除目标数据表！'rollback

INSTAND OF触发器的使用

向表“计0261”插入数据时，检查学号是否存在于表“计026”中，如存在则进行插入操作，否则就不插入。

CREATE TRIGGER [checkid] ON [dbo].[计0261] INSTEAD OF insertASIF NOT EXISTS(SELECT * FROM [计026] WHERE 学号=(SELECT 学号 FROM INSERTED))  BEGIN    ROLLBACK TRANSACTION    PRINT '要处理记录的学号不存在！'  ENDELSE  BEGIN    INSERT INTO 计0261 select * from inserted    PRINT '已经成功处理记录！'  END

AFTER触发器

对订货表设置AFTER触发器，用量在插入记录时自动将统计值计算到订货统计表中。

CREATE TRIGGER [orderinsert] ON [dbo].[订货表]AFTER INSERTASDECLARE @bookid int, @ordernum int, @num intSELECT @bookid = 书籍编号, @ordernum = 数量 FROM INSERTEDSELECT @num = count(书籍编号) FROM 订货统计表 WHERE 书籍编号=@bookidIF @num = 0  --未找到该书，插入记录  INSERT INTO 订货统计表 VALUES(@bookid, @ordernum)ELSE  --找到该书，更新记录  UPDATE 订货统计表  SET 总订货量 = 总订货量 + @ordernum WHERE 书籍编号 = @bookid

DELTED表和INSERTED表

触发器语句中使用了两种特殊的表：deleted表和inserted表

deleted表用于存储delete和update语句所影响的行的原始内容复本。

inserted表用于存储insert和update语句所影响行的新内容的复本。

 

实例：

create trigger syncBdVendor on AkSupplierafter update,insertasdeclare @Id int,@Name nvarchar(100),@ProductType nvarchar(50),@Address nvarchar(100),@Remark nvarchar(500)select @Id=Id,@Name=Name,@ProductType=ProductType,@Address=Address,@Remark=Remark from inserteddeclare @num int--用于判断上面的编号是不是存在于BdVendor表中，来推断添加还是修改select @num=COUNT(*) from BdVendor where cVenCode=CAST(@Id as nvarchar)if @num=0  insert into BdVendor(cVenCode,cVenName,cTrade,cVenAddress,cMemo,dVenCreateDatetime) values (CAST(@Id as nvarchar),@Name,@ProductType,@Address,@Remark,GETDATE())else  update BdVendor   set cVenName=@Name,cVenAddress=@Address,cMemo=@Remark,dModifyDate=GETDATE()  where cVenCode=CAST(@Id as nvarchar)

 

 

删除触发器

drop trigger syncBarcode

 

 

触发器使用场景：

    在记录数据采集内容的时候，采用两张表，一张表记录所有数据，还有一张表来记录单条数据，那么在做关联时，保存最新记录的单条数据表，可以省去不必要的麻烦。记录单条数据的表通过所有数据的表触发添加。

ALTER trigger [dbo].[ivUpdateAfterInsert] on [dbo].[AkIv]after insertas--变量declare        @Id bigint=null,--流水号    @BarCode nvarchar(100)=null,--条码    @DateTime datetime=null,--时间    @Eff decimal(18, 5)=null,--电池转换效率    @Isc decimal(18, 5)=null,--短路电流    @Voc decimal(18, 5)=null,--开路电压    @Rs decimal(18, 5)=null,--串联电阻    @Rsh decimal(18, 5)=null,--并联电阻    @Pmax decimal(18, 5)=null,--最大功率    @Vpm decimal(18, 5)=null,--最大功率时的电压    @Ipm decimal(18, 5)=null,--最大功率时的电流     @FF decimal(18, 5)=null,--填充因子    @Sun decimal(18, 5)=null,--光强    @Temp decimal(18, 5)=null,--温度     @Class nvarchar(50)=null,--档位    @Employee nvarchar(50)=null,--人员    @LineTitle nvarchar(50)=null,--线别    @StationTitle nvarchar(50)=null,--工位    @OrderNumber nvarchar(50)=null,--工单号    @BatterySupplier nvarchar(50)=null,--电池片厂家代码    @Power decimal(18, 2)=null,--电池片功率    @InterconnectId nvarchar(50)=null,--互连条代码    @InterconnectSpec nvarchar(500)=null--互连条规格--iv数据select     @Id=Id,    @BarCode=BarCode,    @DateTime=DateTime,    @Eff=Eff,    @Isc=Isc,    @Voc=Voc,    @Rs=Rs,    @Rsh=Rsh,    @Pmax=Pmax,    @Vpm=Vpm,    @Ipm=Ipm,    @FF=FF,    @Sun=Sun,    @Temp=Temp,    @Class=Class,    @Employee=Employee,    @LineTitle=LineTitle,     @StationTitle=StationTitlefrom inserted--获取工单select @OrderNumber=OrderNumber from AkBarcodeCompInfo where Barcode=@BarCode--获取电池片信息select @BatterySupplier=SupplierCode,@Power=[POWER]from AkOnLineDetail where AkOnLineDetail.ProductBarcode=@BarCode and MaterialType='电池片'--获取互连条信息select @InterconnectId=MaterialNumber,@InterconnectSpec=MaterialSpecfrom AkOnLineDetail where AkOnLineDetail.ProductBarcode=@BarCode and MaterialType='互连条'--更新    update AkIv  set     OrderNumber=@OrderNumber,    BatterySupplier=@BatterySupplier,    Power=@Power,    InterconnectId=@InterconnectId,    InterconnectSpec=@InterconnectSpec    where Id=@Id    --插入到单条记录表中if not exists (select * from AkIvSingle where BarCode=@BarCode)  begin  insert into AkIvSingle(BarCode,DateTime,Eff,Isc,Voc,Rs,Rsh,Pmax,Vpm,Ipm,FF,Sun,Temp,Class,Employee,LineTitle,StationTitle,OrderNumber,BatterySupplier,Power,InterconnectId,InterconnectSpec)  values (@BarCode,GETDATE(),@Eff,@Isc,@Voc,@Rs,@Rsh,@Pmax,@Vpm,@Ipm,@FF,@Sun,@Temp,@Class,@Employee,@LineTitle,@StationTitle,@OrderNumber,@BatterySupplier,@Power,@InterconnectId,@InterconnectSpec)endelsebegin      update AkIvSingle set     BarCode=@BarCode,    DateTime=GETDATE(),    Eff=@Eff,    Isc=@Isc,    Voc=@Voc,    Rs=@Rs,    Rsh=@Rsh,    Pmax=@Pmax,    Vpm=@Vpm,    Ipm=@Ipm,    FF=@FF,    Sun=@Sun,    Temp=@Temp,    Class=@Class,    Employee=@Employee,    LineTitle=@LineTitle,    StationTitle=@StationTitle,    OrderNumber=@OrderNumber,    BatterySupplier=@BatterySupplier,    Power=@Power,    InterconnectId=@InterconnectId,    InterconnectSpec=@InterconnectSpec    where BarCode=@BarCodeend