SQLServer之修改触发器

程序员文章站 2022-07-03 18:46:52

修改触发器规则修改CREATE TRIGGER语句以前创建的 DML、DDL 或登录触发器的定义。触发器是通过使用CREATE TRIGGER创建的。这些触发器可以由Transact-SQL语句直接创建，也可以由程序集方法创建，这些方法在Microsoft .NET Framework公共语言运行 ......

修改触发器规则

修改create trigger语句以前创建的 dml、ddl 或登录触发器的定义。触发器是通过使用create trigger创建的。这些触发器可以由transact-sql语句直接创建，也可以由程序集方法创建，这些方法在microsoft .net framework公共语言运行时 (clr)中创建并上传到sql server的实例。

dml 触发器

通过表和视图上的instead of触发器，alter trigger支持可手动更新的视图。sql server以相同的方式对所有类型的触发器（after、instead-of）应用alter trigger。

可以使用sp_settriggerorder来指定要对表执行的第一个和最后一个after触发器。对一个表只能指定第一个和最后一个after触发器。如果在同一个表上还有其他after触发器，这些触发器将随机执行。

如果alter trigger语句更改了第一个或最后一个触发器，将删除所修改触发器上设置的第一个或最后一个属性，并且必须使用sp_settriggerorder重置顺序值。

只有在成功执行触发sql语句之后，才会执行after触发器。判断执行成功的标准是：执行了所有与已更新对象或已删除对象相关联的引用级联操作和约束检查。 after 触发器操作要检查触发语句的效果，也包括所有由触发语句引起的 update 和 delete 引用级联操作。

如果一个子表或引用表上的 delete 操作是由于父表的 cascade delete 操作所引起的，并且子表上定义了 delete 的 instead of 触发器，那么将忽略该触发器并执行 delete 操作。

ddl 触发器

与dml触发器不同，ddl触发器的作用域不是架构。因此，在查询有关ddl触发器的元数据时，不能使用object_id、object_name、objectproperty 和 objectproperty(ex)。请改用目录视图。有关详细信息，请参阅获取有关 ddl 触发器的信息。

登录触发器

azure sql database 不支持针对登录事件的触发器。

permissions

若要更改dml触发器，需要对于定义该触发器所在的表或视图拥有 alter 权限。

若要更改定义了服务器范围 (on all server) 的ddl触发器或者更改登录触发器，需要对该服务器拥有control server权限。若要更改定义了数据库范围 (on database) 的ddl触发器，需要对当前数据库拥有alter any database ddl trigger权限。

触发器修改

语法：

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

--修改触发器
alter

--触发器标识符
trigger

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

on

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

--database

--将 ddl 触发器的作用域应用于当前数据库。如果指定了此参数，则只要当前数据库中出现 event_type 或 event_group，就会激发该触发器。

--all server
--适用范围： sql server 2008 到 sql server 2017。

--将 ddl 或登录触发器的作用域应用于当前服务器。如果指定了此参数，则只要当前服务器中的任何位置出现 event_type 或 event_group，就会激发该触发器。

{ [架构名].表名| [架构名].视图 } { database| all server } { all server }

--with
--此属性适用于所有触发器类型
--加密包含alter trigger语句文本的sys.syscomments和sys.sql_modules项。使用with encryption可以防止将触发器作为sql server复制的一部分进行发布。不能为clr触发器指定with encryption。
--encryption,

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

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

--此属性适用于所有触发器类型
--{ exec | execute } as { self | owner | 'user_name' }
--caller
--指定模块内的语句在模块调用方的上下文中执行。执行模块的用户不仅必须对模块本身拥有适当的权限，还要对模块引用的任何数据库对象拥有适当权限。
--caller 是除队列外的所有模块的默认值，与 sql server 2005 行为相同。
--caller 不能在 create queue 或 alter queue 语句中指定。
--self
--execute as self 与 execute as user_name 等价，其中指定用户是创建或更改模块的用户。创建或更改模块的用户的实际用户 id 存储在 sys.sql_modules 或 sys.service_queues 目录视图的 execute_as_principal_id 列中。
--self 是队列的默认值。
--owner
--指定模块内的语句在模块的当前所有者上下文中执行。如果模块没有指定的所有者，则使用模块架构的所有者。不能为 ddl 或登录触发器指定 owner。
--' user_name '
--指定模块内的语句在 user_name 指定的用户的上下文中执行。将根据 user_name 来验证对模块内任意对象的权限。不能为具有服务器作用域的 ddl 触发器或登录触发器指定 user_name。请改用 login_name。
--user_name 必须存在于当前数据库中，并且必须是单一实例帐户。 user_name 不能为组、角色、证书、密钥或内置帐户，如 nt authority\localservice、nt authority\networkservice 或 nt authority\localsystem。
--执行上下文的用户 id 存储在元数据中，可以在 sys.sql_modules 或 sys.assembly_modules 目录视图的 execute_as_principal_id 列查看。
--内存优化表上的触发器需要使用此选项。
--execute as { caller | self | owner | 'username'}

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

--instead of

--指定执行 dml 触发器而不是触发 sql 语句，因此，其优先级高于触发语句的操作。不能为 ddl 或登录触发器指定 instead of。

--对于表或视图，每个 insert、update 或 delete 语句最多可定义一个 instead of 触发器。但是，可以为具有自己的 instead of 触发器的多个视图定义视图。

--不允许在使用 with check option 创建的视图上定义 instead of 触发器。将 instead of 触发器添加到为其指定了 with check option 的视图时， sql server 将引发错误。用户必须用 alter view 删除该选项后才能定义 instead of 触发器。

{ for | after | instead of }

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

--event_type

--执行之后将导致激发 ddl 触发器的 transact-sql 语言事件的名称。 ddl 事件中列出了 ddl 触发器的有效事件。

--event_group
--预定义的 transact-sql 语言事件分组的名称。执行任何属于 event_group 的 transact-sql 语言事件之后，都将激发 ddl 触发器。 ddl 事件组中列出了 ddl 触发器的有效事件组。 alter trigger 运行完成后，event_group 还将充当宏，将它涉及的事件类型添加到 sys.trigger_events 目录视图中。

{ [insert] [,] [update] [,] [delete] } { event_type [ ,...n ] | event_group }

--此属性应用于dml触发器的表和视图

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

as
begin

　　sql_statement;

end;

示例：本示例演示修改dml插入触发器。

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

--修改触发器
alter

--触发器标识符
trigger

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

on

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

with
--此属性适用于所有触发器类型
--加密包含alter trigger语句文本的sys.syscomments和sys.sql_modules项。使用with encryption可以防止将触发器作为sql server复制的一部分进行发布。不能为clr触发器指定with encryption。
--encryption,

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

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

--此属性应用于dml触发器的表和视图
--指示当复制代理修改涉及到触发器的表时，不应执行触发器。
not for replication

as
begin
declare @sname nvarchar(100)=null,@sclassid int=null;
set @sname=(select name from inserted);
set @sclassid=(select classid from inserted);

--判断是否需要向test插入name
if @sname is not null
begin
if(select id from dbo.test2 where name=@sname) is null
begin
insert into dbo.test2(name) values(@sname);
end;
end;

--判断是否需要向test3插入classid
if @sclassid is not null
begin

if (select id from dbo.test3 where id=@sclassid) is not null
begin
print(@sclassid);
--insert into dbo.test1(name,sex,age,classid,height,xml1,xml2)
--select name,sex,age,classid,height,xml1,xml2 from inserted;
end;
end;
end;