SQLServer之创建AFETER DELETE触发器

DML AFTER DELETE触发器创建原理

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

delete触发器工作原理：第一步执行delete删除语句，删除表中的数据行，第二步触发delete删除触发器，向系统临时表的deleted表中插入被删除的副本，第三步触发器检查deleted中被删除的数据，确定是否需要回滚或执行其他操作。

不能使用SSMS数据库管理工具直接创建DML添加触发器，可以使用T-SQL脚本创建DML添加触发器。

DML AFTER DELETE触发器创建

语法：

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

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

----创建新的删除触发器
create

--触发器标识符
trigger

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

--触发器名称
[架构名.]触发器名

on

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

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
end ;
go

示例：

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

--判断是否存在触发器，如果存在则删除
if exists(select * from sysobjects where name='deletetri')
drop trigger deletetri;
go

----创建新的删除触发器
create

--触发器标识符
trigger

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

--触发器名称
dbo.deletetri

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 选项。
delete
as
begin
declare @sss nvarchar(100)=null;
set @sss=(select top(1) name from deleted order by id desc)
if @sss is not null
begin
delete from dbo.test2 where id=(select id from dbo.test2 where name=@sss);
end
end ;
go

示例结果：

DML AFTER DELETE触发器优缺点

优点：

　　1、实现数据的级联删除操作。

　　2、不需要参数，不需要显示调用。

缺点：

　　1、如果不知道表中存在删除器，删除表中数据会造成不必要的级联操作，造成数据丢失。

　　2、可移植性差。

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

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

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