创建触发器的语法看上去和所有其他CREATE语法示例十分相似,只是它必须附加在一个表上——因为触发器不能独立存在。它有一个CREATE <object type> <object name>以及在其他许多对象中可以看到的执行语句——只是添加了ON子句来指出触发器将要附加的表,以及在何时和何种情况下激活这个触发器。
看一下创建触发器的语法:
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())