<p>创建触发器的语法看上去和所有其他CREATE语法示例十分相似,只是它必须附加在一个表上——因为触发器不能独立存在。它有一个CREATE <object type> <object name>以及在其他许多对象中可以看到的执行语句——只是添加了ON子句来指出触发器将要附加的表,以及在何时和何种情况下激活这个触发器。</p>
看一下创建触发器的语法:
CREATE TRIGGER <trigger name> ON [<schema name>.]<table or view name> [WITH ENCRYPTION | EXECUTE AS <CALLER | SELF | <user> >] {{{FOR|AFTER} <[DELETE] [,] [INSERT] [,] [UPDATE]>} | INSTEAD OF} [WITH APPEND] [NOT FOR REPLICATION] AS < <sql statements> | EXTERNAL NAME <assembly method specifier> >
一例:
CREATE TRIGGER Sales.SalesOrderDetailNotDiscontinued ON Sales.SalesOrderDetail FOR INSERT, UPDATE AS IF EXISTS ( SELECT 'True' FROM Inserted i JOIN Production.Product p ON i.ProductID = p.ProductID WHERE p.DiscontinuedDate IS NOT NULL ) BEGIN RAISERROR('Order Item is discontinued. Transaction Failed.', 16, 1); ROLLBACK TRAN END
检验此触发器:
先准备一些数据:
USE AdventureWorks;UPDATE Production.Product SET DiscontinuedDate = '01-01-2008' WHERE ProductID = 680
以下语句将引发我们预料到的错误,插入操作被触发器拦截:
INSERT INTO Sales.SalesOrderDetail VALUES (43659, '4911-403C-98', 1, 680, 1, 1431.50, 0.00, NEWID(), GETDATE())