赣州网站制作较好的公司,深圳深度网站建设,seo是什么专业,专题网站搭建一、如何创建一个触发器呢
触发器的定义语言如下#xff1a; CREATE [ OR ALTER ] TRIGGER trigger_nameon {table_name | view_name}{for | After | Instead of }[ insert, update,delete ]assql_statement从这个定义语言我们可以知道如下信息#xff1a; trigger_name CREATE [ OR ALTER ] TRIGGER trigger_nameon {table_name | view_name}{for | After | Instead of }[ insert, update,delete ]assql_statement从这个定义语言我们可以知道如下信息 trigger_name触发器的名称。 trigger_name 必须遵循标识符规则但 trigger_name 不得以 # 或 ## 开头。table | view触发器可以作用于表或视图只有 INSTEAD OF 触发器才能引用视图FOR | AFTERFOR 或 AFTER 指定仅当触发 SQL 语句中指定的所有操作都已成功启动时DML 触发器才触发。INSTEAD OF指定 DML 触发器而不是触发 SQL 语句启动因此替代触发语句的操作。 从定义中我们可以发现DML触发器总体有两种类型AFTER 触发器和INSTEAD OF 触发器
二、AFTER 触发器和INSTEAD OF 触发器的区别
直接区别它们可能晦涩难懂我们先举个例子
1建一张表
--作家表
CREATE TABLE my_test.dbo.author (id bigint IDENTITY(0,1) NOT NULL,name varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL )2在author创建after触发器
CREATE TRIGGER authorTrigger ON author AFTER INSERT AS
declare count int
BEGINselect count count(*) from authorprint count
END
该触发器作用就是统计insert后的表数量测试结果如下
我们插入前数据条数 执行insert语句后触发触发器输出的条数是 3修改author的触发器为INSTEAD OF
alter TRIGGER authorTrigger ON author INSTEAD OF INSERT AS
declare count int
BEGINselect count count(*) from authorprint count
END 该触发器作用就是统计insert后的表数量测试结果如下
我们插入前数据条数 执行insert语句后触发触发器输出的条数是 看到这里读者可能怀疑数据错了第二种触发器INSTEAD OF为何插入后还是七条呢不应该是八条吗 根据这个实验引出结论 after 触发器insert、update、delete触发器内的语句是在操作执行之后已经作用在表上才触发执行的 instead of 触发器并不会执行操作不会影响实际的表它更像一个指令遇到条件中的指令就触发了就会执行触发器内的语句。在执行 INSERT、UPDATE、MERGE 或 DELETE 语句的操作之后执行 AFTER 触发器INSTEAD OF 触发器可用于对一个或多个列执行错误或值检查然后在插入、更新或删除行之前执行其他操作 无论after 触发器还是instead of 触发器他们的作用都是在更新或更新后对表中数据操作那么更新的旧数据该保存到哪里呢sqlserver提供了两张临时表inserted 和 deleted 表
三、inserted 和 deleted 表 inserted表和deleted表对照 修改操作记录inserted表deleted表增加(insert)记录存放新增的记录............删除(deleted)记录..............存放被删除的记录修改(update)记录存放更新后的记录存放更新前的记录 inserted表保存更新后的记录副本deleted表保存更新前的记录副本。INSTEAD OF触发器和AFTER 触发器都可以使用inserted表和deleted表SQL Server 会自动创建和管理这两种表用户使用它们作为条件但是不能修改表中的数据 四、项目实战 开发中有这么一个需求当表中某些字段发生修改或者新增一条记录时会自动更新表中modifyTime字段为当前时间如何采用触发器实现呢 分析这里要注意是某些字段有更新才更新modifyTime字段如果你更新的字段不在指定的字段里是不会更新modifyTime字段答案如下 CREATE TRIGGER modifMeterTrigger ON
Meter after UPDATE,INSERT AS
declare upflag int
BEGINselectupflag casewhen d.IsAddSecurityPlan ! i.IsAddSecurityPlan then 1when d.MeterVersion ! i.MeterVersion then 1when d.SystemNo ! i.SystemNo then 1when d.IsCancel ! i.IsCancel then 1when d.UseDate ! i.UseDate then 1when d.HankDate ! i.HankDate then 1when d.UseYear ! i.UseYear then 1when d.AddressCode ! i.AddressCode then 1when d.MeterType ! i.MeterType then 1when d.UserGasType ! i.UserGasType then 1else 0endfrom inserted i left join deleted d on d.id i.idif(upflag 0)UPDATE Meter SET ModifTime GETDATE( )FROM Meter t INNER JOIN Inserted i ON t.idi.id
END 看这个触发器定义语句比之前的要复杂好多我慢慢解读 1首先定义的After类型的触发器 2定义了一个局部变量upflag来标志更新的字段是否在指定的字段内 3将inserted和deleted采用左连接通过case when来判断如果inserted字段的值不等于deleted中的值说明指定字段有更新然后更新标志为upflag1 4最后判断upflag是否为1为1执行更新 5采用原表和Inserted内连接主要为了既可以批量更新也可以单条更新 注意一点我们在更新可能会影响多行数据如果我们在更新时采用id作为条件
比如如下触发器定义来源于官方
CREATE TRIGGER NewPODetail
ON Purchasing.PurchaseOrderDetail
AFTER INSERT AS UPDATE PurchaseOrderHeader SET SubTotal SubTotal LineTotal FROM inserted WHERE PurchaseOrderHeader.PurchaseOrderID inserted.PurchaseOrderID ; 对于多行插入示例的 DML 触发器可能不会正确运行,SQL官方提供了ROWCOUNT 函数来区分单行插入和多行插入
CREATE TRIGGER NewPODetail3
ON Purchasing.PurchaseOrderDetail
FOR INSERT AS
IF ROWCOUNT 1
BEGIN UPDATE Purchasing.PurchaseOrderHeader SET SubTotal SubTotal LineTotal FROM inserted WHERE PurchaseOrderHeader.PurchaseOrderID inserted.PurchaseOrderID
END
ELSE
BEGIN UPDATE Purchasing.PurchaseOrderHeader SET SubTotal SubTotal (SELECT SUM(LineTotal) FROM inserted WHERE PurchaseOrderHeader.PurchaseOrderID inserted.PurchaseOrderID) WHERE PurchaseOrderHeader.PurchaseOrderID IN (SELECT PurchaseOrderID FROM inserted)
END;
