触发器的使用
触发器实质上是特殊类型的存储过程,当满足触发条件时,就可以触发从而能完成各种不同的管理任务。
概述
触发器和数据表紧密连在一起,只要谈到触发器,一定是说某个数据表的触发器。因此,触发器是在数据表的基础上进行定义,数据表也成为触发器表。触发器作为特殊类型的存储过程,和存储过程的区别是:存储过程可以被调用执行,触发器不可。
触发器的分类
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 database for drop_table,alter_table as print '对不起,您不能删除目标数据表!' rollback
INSTAND OF触发器的使用
向表“计0261”插入数据时,检查学号是否存在于表“计026”中,如存在则进行插入操作,否则就不插入。
CREATE TRIGGER [checkid] ON [dbo].[计0261] INSTEAD OF insert AS IF NOT EXISTS(SELECT * FROM [计026] WHERE 学号=(SELECT 学号 FROM INSERTED)) BEGIN ROLLBACK TRANSACTION PRINT '要处理记录的学号不存在!' END ELSE BEGIN INSERT INTO 计0261 select * from inserted PRINT '已经成功处理记录!' END
AFTER触发器
对订货表设置AFTER触发器,用量在插入记录时自动将统计值计算到订货统计表中。
CREATE TRIGGER [orderinsert] ON [dbo].[订货表] AFTER INSERT AS DECLARE @bookid int, @ordernum int, @num int SELECT @bookid = 书籍编号, @ordernum = 数量 FROM INSERTED SELECT @num = count(书籍编号) FROM 订货统计表 WHERE 书籍编号=@bookid IF @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 AkSupplier after update,insert as declare @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 inserted declare @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 insert as --变量 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=StationTitle from 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=MaterialSpec from 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) end else begin 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=@BarCode end