第五部分:构建触发器的SQL指令 - 详解触发器(Trigger)
回顾一下,在Management Studio新建一个触发器的时候,它在查询分析对话框给预设了一些SQL代码,这些代码其实上就是建立触发器的语法提示。现在来看一下完整的触发器语法代码:
CREATE TRIGGER <Schema_Name, sysname, Schema_Name>.<Trigger_Name, sysname, Trigger_Name>
ON <Schema_Name, sysname, Schema_Name>.<Table_Name, sysname, Table_Name>
AFTER <Data_Modification_Statements, , INSERT,DELETE,UPDATE>
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for trigger here
END
GO
用中文改了一下,以上代码就一目了然了:
CREATE TRIGGER 触发器名
ON 数据表名或视图名
AFTER INSERT或DELETE或UPDATE
AS
BEGIN
--这里是要运行的SQL语句
END
GO
现在再对上面的代码进行进一步的说明:
l CREATE TRIGGER 触发器名:这一句声明SQL语句是用来建立一个触发器。其中触发器名在所在的数据库里必须是唯一的。由于触发器是建立中数据表或视图中的,所以有很多人都以为只要是在不同的数据表中,触发器的名称就可以相同,其实触发器的全名(Server.Database.Owner.TriggerName)是必须唯一的,这与触发器在哪个数据表或视图无关。
l ON 数据表名或视图名:这是指定触发器所在的数据表或视图,但是请注意,只有Instead Of触发器才能建立在视图上。并且,有设置为With Check Option的视图也不允许建立Instead Of触发器。
l AFTER INSERT或 DELETE UPDATE:这是指定触发器的类型,是After Insert触发器,还是After Delete触发器,或者是After Update触发器。其中After可以用For来代取,它们的意思都是一样的,代表只有在数据表的操作都已正确完成后才会激活的触发器。INSERT、DELETE和UPDATE至少要指定一个,当然也可以指定多个,若指定多个时,必须用逗号来分开。其顺序可以任意摆放。
l With Encryption:With Encryption是用来加密触发器的,放在“On 数据表名或视图名”的后面,“For”的前面。如果使用了这句话,该触发器将会被加密,任何人都看不到触发器的内容了。
例一:以下是一个包含提醒电子邮件的触发器例子,如果订单表里记录有改动的的话(无论增加订单还是修改、删除订单),则给物流人员张三发送电子邮件:
CREATE TRIGGER 订单_Insert
ON 订单
AFTER INSERT, UPDATE, DELETE
AS
EXEC master..xp_sendmail '张三',
'订单有更改,请查询确定'
GO
例二:在订单明细表里,折扣字段不能大于0.6,如果插入记录时,折扣大于0.6的话,回滚操作。
CREATE TRIGGER 订单明细_Insert
ON 订单明细
AFTER INSERT
AS
BEGIN
if (Select 折扣 from inserted)>0.6
begin
print '折扣不能大于0.6'
Rollback Transaction
end
END
GO
在示例二中运用了两个方法,一个是前面说过的,在Inserted表里查询某个字段,还有一个是用Rollback Transaction来回滚操作。如果用下面的SQL语句来进行Insert操作的话,插入记录将会不成功。
INSERT INTO 订单明细(订单ID,产品ID,单价,数量,折扣)
VALUES (11077,1,18,1,0.7)
运行结果如图7所示:
图7 插入记录不符合触发器里的约束,则回滚操作