说明
触发器是一种特殊的存储过程,它不能被显式地调用,而是在往表中插入记录﹑更新记录或者删除记录时被自动地激活。 所以触发器可以用来实现对表实施复杂的完整性约束。
分类使用
“Instead of”触发器在执行真正“插入”之前被执行。除表之外,“Instead of” 触发器也可以用于视图,用来扩展视图可以支持的更新操作。
使用:INSTEAD OF INSERT
“After”触发器在Insert、Update或Deleted语句执行之后被触发。“After”触发器只能用于表。
使用:AFTER INSERT
示例(after)
insert触发器
drop trigger [dbo].[Trigger_Insert_Test]--不能插入临时表CREATE TABLE sal_order_test(FID INT IDENTITY (1,1),FBILLNO VARCHAR(20) NOT NULL DEFAULT(''),FDATE VARCHAR(20) NOT NULL DEFAULT(''))CREATE TABLE sal_order_testLog(FID INT IDENTITY (1,1),Direction VARCHAR(2000) NOT NULL DEFAULT(''),FDATE VARCHAR(200) NOT NULL DEFAULT(''))drop TABLE sal_order_testLog drop TABLE sal_order_test select *from sal_order_testLogselect *from sal_order_test--Insert 触发器Create TRIGGER [dbo].[Trigger_Insert_Test]ON [dbo].[T_SAL_ORDER]AFTER INSERTAS BEGINSET NOCOUNT ON;Declare @fid int;Select @fid=FID From insertedDeclare @fbillno varchar(2000);Select @fbillno=FBILLNO From insertedif ( (select COUNT(*) from sal_order_test where FBILLNO = @fbillno)=0)BeginInsert into sal_order_test( FBILLNO, FDATE)Select FBILLNO, FDATE From insertedinsert into sal_order_testLog(Direction,Fdate) values( @fbillno+'执行成功',convert(varchar,getdate(),21))EndELSEBegininsert into sal_order_testLog(Direction,Fdate) values( @fbillno+'执行失败',convert(varchar,getdate(),21))--rollback transaction--数据回滚ENDEND
update 触发器
--UPDATE 触发器Create TRIGGER [dbo].[Trigger_UPDATE_Test]ON [dbo].[T_SAL_ORDER]AFTER UPDATEAS BEGINSET NOCOUNT ON;--写自己要执行的操作,这里只是记录一下日志Insert Into sal_order_testLog( Direction, FDATE)Select FBILLNO+'更新成功!', GETDATE() From deletedEND
delete触发器
--Delete 触发器Create TRIGGER [dbo].[Trigger_Delete_Test]ON [dbo].[T_SAL_ORDER]AFTER DELETEAS BEGINSET NOCOUNT ON;Insert Into sal_order_testLog( Direction, FDATE)Select FBILLNO+'删除成功!', GETDATE() From deletedEND