SQLServer之创建DML AFTER UPDATE触发器

dml after update触发器创建原理

触发器触发时，系统自动在内存中创建deleted表或inserted表，inserted表临时保存了插入或更新后的记录行，deleted表临时保存了删除或更新前的记录行，内存中创建的表只读，不允许修改，触发器执行完成后，自动删除。

update触发器工作原理：第一步执行update更新语句，第二步触发update触发器删除原有的数据，将删除的数据备份到deleted表中，第三步再插入新行数据，将新插入的数据备份到inserted表中。

不能使用ssms数据库管理工具直接创建dml添加触发器，可以使用t-sql脚本创建dml添加触发器。

dml after update触发器创建

语法：

--声明数据库引用
use 数据库名;
go

--判断触发器是否存在
if exists(select * from sysobjects where name=触发器名)
drop trigger 触发器名;
go

--创建新的修改触发器
create

--触发器标识符
trigger

--dml 触发器所属架构的名称。 dml 触发器的作用域是为其创建该触发器的表或视图的架构。 不能为 ddl 或登录触发器指定
--[dbo.]

--触发器名称
触发器名称

on
--对其执行 dml 触发器的表或视图，有时称为触发器表或触发器视图。 可以根据需要指定表或视图的完全限定名称。 视图只能被 instead of 触发器引用。 不能对局部或全局临时表定义 dml 触发器。
[架构名称.] { 表名 | 视图名 }

--with
--对create trigger 语句的文本进行模糊处理。使用with encryption可以防止将触发器作为sql server复制的一部分进行发布。不能为 clr 触发器指定 with encryption。(指定此选项将为触发器加密)
--encryption,

--指示触发器已本机编译。 (只能应用于table)
--内存优化表上的触发器需要使用此选项。
--native_compilation

--确保不能删除或更改触发器引用的表。(只能应用于table)
--内存优化表上的触发器需要使用此选项，但此选项不支持传统表上的触发器。
--schemabinding

--execute as (后面可以跟函数，存储过程等)
--指定用于执行该触发器的安全上下文。 允许您控制 sql server 实例用于验证被触发器引用的任意数据库对象的权限的用户帐户。
--内存优化表上的触发器需要使用此选项。
--execute as clause

--for | after
--after 指定 dml 触发器仅在触发 sql 语句中指定的所有操作都已成功执行时才被触发。 所有的引用级联操作和约束检查也必须在激发此触发器之前成功完成。
--如果仅指定 for 关键字，则 after 为默认值。
--不能对视图定义 after 触发器。
{ for | after }

--{ [delete] [,] [insert] [,] [update] }
--指定数据修改语句，这些语句可在 dml 触发器对此表或视图进行尝试时激活该触发器。 必须至少指定一个选项。 在触发器定义中允许使用上述选项的任意顺序组合。
--对于 instead of 触发器，不允许对具有指定级联操作 on delete 的引用关系的表使用 delete 选项。 同样，也不允许对具有指定级联操作 on update 的引用关系的表使用 update 选项。
{ [insert] [,] [update] [,] [delete] }

--指定应该再添加一个现有类型的触发器。 with append 不能与 instead of 触发器一起使用。如果显式声明了 after 触发器，则也不能使用该子句。
--仅当为了向后兼容而指定了 for 时（但没有 instead of 或 after）时，才能使用 with append。 如果指定了 external name（即触发器为 clr 触发器），则不能指定 with append。
--with append

--指示当复制代理修改涉及到触发器的表时，不应执行触发器。
--not for replication

as
begin
　　sql_statement
end

go

示例：

--声明数据库引用
use testss;
go

--判断触发器是否存在
if exists(select * from sysobjects where name='updatetri')
drop trigger updatetri;
go

--创建新的修改触发器
create

--触发器标识符
trigger

--dml 触发器所属架构的名称。 dml 触发器的作用域是为其创建该触发器的表或视图的架构。 不能为 ddl 或登录触发器指定
--[dbo.]

--触发器名称
updatetri

on
--对其执行 dml 触发器的表或视图，有时称为触发器表或触发器视图。 可以根据需要指定表或视图的完全限定名称。 视图只能被 instead of 触发器引用。 不能对局部或全局临时表定义 dml 触发器。
dbo.test1

--with
--对create trigger 语句的文本进行模糊处理。使用with encryption可以防止将触发器作为sql server复制的一部分进行发布。不能为 clr 触发器指定 with encryption。(指定此选项将为触发器加密)
--encryption,

--指示触发器已本机编译。 (只能应用于table)
--内存优化表上的触发器需要使用此选项。
--native_compilation

--确保不能删除或更改触发器引用的表。(只能应用于table)
--内存优化表上的触发器需要使用此选项，但此选项不支持传统表上的触发器。
--schemabinding

--execute as (后面可以跟函数，存储过程等)
--指定用于执行该触发器的安全上下文。 允许您控制 sql server 实例用于验证被触发器引用的任意数据库对象的权限的用户帐户。
--内存优化表上的触发器需要使用此选项。
--execute as clause

--for | after
--after 指定 dml 触发器仅在触发 sql 语句中指定的所有操作都已成功执行时才被触发。 所有的引用级联操作和约束检查也必须在激发此触发器之前成功完成。
--如果仅指定 for 关键字，则 after 为默认值。
--不能对视图定义 after 触发器。
for

--{ [delete] [,] [insert] [,] [update] }
--指定数据修改语句，这些语句可在 dml 触发器对此表或视图进行尝试时激活该触发器。 必须至少指定一个选项。 在触发器定义中允许使用上述选项的任意顺序组合。
--对于 instead of 触发器，不允许对具有指定级联操作 on delete 的引用关系的表使用 delete 选项。 同样，也不允许对具有指定级联操作 on update 的引用关系的表使用 update 选项。
update

--指定应该再添加一个现有类型的触发器。 with append 不能与 instead of 触发器一起使用。如果显式声明了 after 触发器，则也不能使用该子句。
--仅当为了向后兼容而指定了 for 时（但没有 instead of 或 after）时，才能使用 with append。 如果指定了 external name（即触发器为 clr 触发器），则不能指定 with append。
--with append

--指示当复制代理修改涉及到触发器的表时，不应执行触发器。
not for replication

as
begin
declare @oldname nvarchar(100)=null,@newname nvarchar(100)=null;
set @oldname=(select top(1) deleted.name from deleted order by id desc);
set @newname=(select top(1) inserted.name from inserted order by id desc);
if @oldname is not null
begin
update dbo.test2 set name=@newname where id=(select id from dbo.test2 where name=@oldname)
end
end
go

示例结果：

dml after update触发器优缺点

优点：

　　1、触发器可以更快更高效的维护数据，节省人力。

　　2、触发器可以用于加强数据的完整性约束和业务规则。

缺点：　　

　　1、可移植性差。

　　2、占用服务器资源，给服务器造成压力。

　　3、执行速度主要取决于数据库服务器的性能与触发器代码的复杂程度。

　　4、触发器会使编程时源码的结构*打乱，为将程序修改、源码阅读带来困难。