新葡亰496net 新葡亰496net SQL SEPRADOVERAV4中各式触发器的完全语法及参数表达_MsSql_脚本之家

SQL SEPRADOVERAV4中各式触发器的完全语法及参数表达_MsSql_脚本之家



背景

  上焕发青春篇中,小编介绍了SQL Server
允许访谈数据库的元数据,为啥有元数据,怎么着行使元数据。那生机勃勃篇中笔者会介绍怎么样进一层找到各个有价值的音讯。以触发器为例,因为它们往往一同非常多标题。

 

语法:

触发器可以领略为由特定事件触发的仓库储存进程,
和仓储进度、函数相近,触发器也支持CL中华V,近些日子SQL
Server共匡助以下三种触发器:

那么哪些找到触发器的数码?

*  以sys.system_views*is表初叶。让大家查询出数据库中利用触发器的新闻。能够告知您眼下SQL
Server版本中有何触发器。

SELECT schema_name(schema_ID)+'.'+ name

  FROM sys.system_views WHERE name LIKE '%trigger%'

 ----------------------------------------

sys.dm_exec_trigger_stats              

sys.server_trigger_events              

sys.server_triggers                    

sys.trigger_event_types                

sys.trigger_events                     

sys.triggers                           



(6 row(s) affected)

  当中sys.triggers看起来音讯比比较多,它又含宛如何列?下边那一个查询非常轻松查到:

 SELECT Thecol.name+ ' '+ Type_name(TheCol.system_type_id)

  + CASE WHEN TheCol.is_nullable=1 THEN ' NULL' ELSE ' NOT NULL' END as Column_Information

FROM sys.system_views AS TheView

  INNER JOIN sys.system_columns AS TheCol

    ON TheView.object_ID=TheCol.Object_ID

  WHERE  TheView.name = 'triggers'

  ORDER BY column_ID;

结果如下:

 Column_Information

----------------------------------------

name nvarchar NOT NULL

object_id int NOT NULL

parent_class tinyint NOT NULL

parent_class_desc nvarchar NULL

parent_id int NOT NULL

type char NOT NULL

type_desc nvarchar NULL

create_date datetime NOT NULL

modify_date datetime NOT NULL

is_ms_shipped bit NOT NULL

is_disabled bit NOT NULL

is_not_for_replication bit NOT NULL

is_instead_of_trigger bit NOT NULL

 

于是大家多这么些音信有了更加好的明白,有了二个索引的目录。这一个定义有点令人头晕,不过其他方面,它也是至极轻易的。大家能够意识到元数据,再找个查询中,供给做的就是更动那一个单词‘triggers’来搜索你想要的视图名称。.

在二零一三及其未来版本,能够运用三个新的表值函数十分大地简化上述查询,并得以幸免各样连接。在下边的查询中,大家将找寻sys.triggers
视图

中的列。能够利用相像的查询通过更正字符串中的对象名称来获取其余视图的定义。

 SELECT name+ ' '+ system_type_name

  + CASE WHEN is_nullable=1 THEN ' NULL' ELSE ' NOT NULL' END as Column_Information

FROM sys.dm_exec_describe_first_result_set

  ( N'SELECT * FROM sys.triggers;', NULL, 0) AS f

  ORDER BY column_ordinal;

查询结果如下:

 Column_Information

----------------------------------------

name nvarchar(128) NOT NULL

object_id int NOT NULL

parent_class tinyint NOT NULL

parent_class_desc nvarchar(60) NULL

parent_id int NOT NULL

type char(2) NOT NULL

type_desc nvarchar(60) NULL

create_date datetime NOT NULL

modify_date datetime NOT NULL

is_ms_shipped bit NOT NULL

is_disabled bit NOT NULL

is_not_for_replication bit NOT NULL

is_instead_of_trigger bit NOT NULL

 

sys.dm_exec_describe_first_result_set函数的最大优势在于你能见到其余结果的列,不止是表和视图、存款和储蓄进程大概贬值函数。

为了摸清任何列的新闻,你能够应用微微改过的本子,只供给更动代码中的字符串’sys.triggers’就可以,如下:

 Declare @TheParamater nvarchar(255)

Select @TheParamater = 'sys.triggers'

Select @TheParamater = 'SELECT * FROM ' + @TheParamater

SELECT

  name+ ' '+ system_type_name

  + CASE WHEN is_nullable=1 THEN ' NULL' ELSE ' NOT NULL' END as Column_Information

FROM sys.dm_exec_describe_first_result_set

  ( @TheParamater, NULL, 0) AS f

  ORDER BY column_ordinal;
Trigger on an INSERT, UPDATE, or DELETE statement to a table or view CREATE TRIGGER [ schema_name . ]trigger_name ON { table | view } [ WITH  [ ,...n ] ]{ FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } [ WITH APPEND ] [ NOT FOR REPLICATION ] AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME  } ::= [ ENCRYPTION ] [ EXECUTE AS Clause ] ::= assembly_name.class_name.method_nameTrigger on a CREATE, ALTER, DROP, GRANT, DENY, REVOKE, or UPDATE STATISTICS statement CREATE TRIGGER trigger_name ON { ALL SERVER | DATABASE } [ WITH  [ ,...n ] ]{ FOR | AFTER } { event_type | event_group } [ ,...n ]AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier > [ ; ] } ::= [ ENCRYPTION ] [ EXECUTE AS Clause ] ::= assembly_name.class_name.method_nameTrigger on a LOGON event CREATE TRIGGER trigger_name ON ALL SERVER [ WITH  [ ,...n ] ]{ FOR| AFTER } LOGON AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier > [ ; ] } ::= [ ENCRYPTION ] [ EXECUTE AS Clause ] ::= assembly_name.class_name.method_name
  1. DML触发器, 表/视图级有效,可由DML语句 (INSERT, UPDATE, DELETE卡塔尔(قطر‎触发;

  2. DDL 触发器,数据库级有效,可由DDL语句 (CREATE, ALTEHaval, DROP 等卡塔尔(قطر‎ 触发;

  3. 新葡亰496net,LOGON 触发器, 实例级有效,可由客户账号登陆(LOGON卡塔尔国数据库实例时接触;

不过当然二个触发器是首先是一个指标,由此一定在sys.objects?

  在我们使用sys.triggers的音讯以前,须求来重新三遍,全体的数据库对象都留存于sys.objects中,在SQL
Server 中的对象包罗以下:聚合的CLLX570函数,check
限制,SQL标量函数,CL普拉多标量函数,CLXC90表值函数,SQL内联表值函数,内部表,SQL存款和储蓄进度,CL凯雷德存款和储蓄进度,陈设指南,主键约束,老式法则,复制过滤程序,系统根底表,同义词,系列对象,服务队列,CLHighlanderDML
触发器,SQL表值函数,表类型,顾客自定义表,唯生机勃勃限制,视图和强大存款和储蓄进度等。

  触发器是指标所以底工新闻一定保存在sys.objects新葡亰,。不幸运的是,有的时候大家要求额外的音信,这么些新闻方可经过目录视图查询。那些额外数占有是何许啊?

 

  校正大家应用过的查询,来查询sys.triggers的列,此番大家会看见额外消息。那个额外列是源于于sys.objects。

 SELECT coalesce(trigger_column.name,'NOT INCLUDED') AS In_Sys_Triggers,

       coalesce(object_column.name,'NOT INCLUDED') AS In_Sys_Objects

FROM

 (SELECT Thecol.name

  FROM sys.system_views AS TheView

    INNER JOIN sys.system_columns AS TheCol

      ON TheView.object_ID=TheCol.Object_ID

  WHERE  TheView.name = 'triggers') trigger_column

FULL OUTER JOIN

 (SELECT Thecol.name

  FROM sys.system_views AS TheView

    INNER JOIN sys.system_columns AS TheCol

      ON TheView.object_ID=TheCol.Object_ID

  WHERE  TheView.name = 'objects') object_column

ON trigger_column.name=object_column.name

查询结果:

In_Sys_Triggers                In_Sys_Objects

------------------------------ ----------------------

name                           name

object_id                      object_id

NOT INCLUDED                   principal_id

NOT INCLUDED                   schema_id

NOT INCLUDED                   parent_object_id

type                           type

type_desc                      type_desc

create_date                    create_date

modify_date                    modify_date

is_ms_shipped                  is_ms_shipped

NOT INCLUDED                   is_published

NOT INCLUDED                   is_schema_published

is_not_for_replication         NOT INCLUDED

is_instead_of_trigger          NOT INCLUDED

parent_id                      NOT INCLUDED

is_disabled                    NOT INCLUDED

parent_class                   NOT INCLUDED

parent_class_desc              NOT INCLUDED

 

上述这么些让我们领略在sys.triggers的附加消息,然而因为它一向是表的子对象,所以有个别不相干信息是不会显得在此些钦命的视图也许sys.triggers中的。以后快要带大家去继续找找那一个音信。

参数:

 

触发器的问题

  触发器是卓有功效的,不过因为它们在SSMS对象能源微处理器窗格中不是可知的,所以平常用来提醒错误。触发器不常候会略带微妙的地点让其出标题,比如,当导入进度中禁止使用了触发器,并且鉴于一些原因他们未有重启。

上边是四个关于触发器的精练提示:

  触发器能够在视图,表也许服务器上,任何这么些指标上都可以有超越1个触发器。普通的DML触发器能被定义来施行代替一些数码校订(Insert,Update或然Delete)或然在数据校订之后实施。每四个触发器与只与贰个对象管理。DDL触发器与数据库关联恐怕被定义在服务器等级,那类触发器平常在Create,Alter大概Drop那类SQL语句试行后触发。

  像DML触发器雷同,能够有三个DDL触发器被创建在同三个T-SQL语句上。贰个DDL触发器和讲话触发它的言辞在同二个事情中运作,所以除了Alter
DATABASE之外都足以被回滚。DDL触发器运转在T-SQL语句试行完结后,也便是不可能作为Instead
OF触发器使用。

  三种触发器都与事件有关,在DML触发器中,包括INSERT, UPDATE,
和DELETE,可是不菲事变都能够与DDL触发器关联,稍后大家将领悟。

schema_name

一. DML触发器

在数据库中列出触发器

那正是说怎么获取触发器列表?上面小编在AdventureWorks数据库中开展询问,注意该库的视图中从不触发器。

第一个查询全数消息都在sys.triggers 的目录视图中。

SELECT

  name AS TriggerName,

  coalesce(object_schema_name(parent_ID)+'.'

    +object_name(parent_ID),'Database ('+db_name()+')') AS TheParent

FROM sys.triggers;



TriggerName                    TheParent

------------------------------ ----------------------------------------

ddlDatabaseTriggerLog          Database (AdventureWorks2012)          

dEmployee                      HumanResources.Employee                

iuPerson                       Person.Person                          

iPurchaseOrderDetail           Purchasing.PurchaseOrderDetail         

uPurchaseOrderDetail           Purchasing.PurchaseOrderDetail         

uPurchaseOrderHeader           Purchasing.PurchaseOrderHeader         

iduSalesOrderDetail            Sales.SalesOrderDetail                 

uSalesOrderHeader              Sales.SalesOrderHeader                 

dVendor                        Purchasing.Vendor                      

iWorkOrder                     Production.WorkOrder                   

uWorkOrder                     Production.WorkOrder   

  笔者使用元数据函数db_name()使SQL保持轻巧。db_name(卡塔尔告诉本身数据库的名号。object_schema_name澳门葡亰娱乐场手机版,(State of Qatar用来查询object_ID表示的对象的结构,以致object_name**()**查询对象名称。那几个对指标的引用指向触发器的持有者,触发器能够是数据库本人,也可以是表:服务器触发器有本身的种类视图,稍后小编交易会示。

设若想要见到有着触发器,那么大家最棒使用sys.objects 视图:

SELECT name as TriggerName, object_schema_name(parent_object_ID)+'.'

    +object_name(parent_object_ID) AS TheParent

            FROM   sys.objects

           WHERE  OBJECTPROPERTYEX(object_id,'IsTrigger') = 1

 

在乎,输出不含有数据库级其他触发器,因为具备的DML触发器都在sys.objects视图中,可是你会挂大器晚成漏万在sys.triggers视图中的触发器。

上面查询结果:

name                           TheParent

------------------------------ -------------------------------

dEmployee                      HumanResources.Employee

iuPerson                       Person.Person

iPurchaseOrderDetail           Purchasing.PurchaseOrderDetail

uPurchaseOrderDetail           Purchasing.PurchaseOrderDetail

uPurchaseOrderHeader           Purchasing.PurchaseOrderHeader

iduSalesOrderDetail            Sales.SalesOrderDetail

uSalesOrderHeader              Sales.SalesOrderHeader

dVendor                        Purchasing.Vendor

iWorkOrder                     Production.WorkOrder

uWorkOrder                     Production.WorkOrder

 

DML 触发器所属结构的名目。DML
触发器的成效域是为其成立该触发器的表或视图的结构。对于 DDL
或登入触发器,不能够内定schema_name。

1. 语句级触发器/行级触发器

自己的表和视图有多少个触发器?

自家想精晓各样表有多少个触发器,何况什么情状下接触它们。上面大家列出了具备触发器的表以致种种事件的触发器数量。种种表或然视图对于触发器行为都有三个INSTEAD
OF 触发器,恐怕是UPDATE, DELETE, 可能 INSERT

。不过二个表可以有多个AFTEHaval触发器行为。那几个将展现在下边包车型客车查询中(毁灭视图):

SELECT

convert(CHAR(32),coalesce(object_schema_name(parent_ID)+'.'

    +object_name(parent_ID),'Database ('+db_name()+')')) AS 'Table', triggers,[KD1] [AC2] 

convert(SMALLINT,objectpropertyex(parent_ID, N'TABLEDeleteTriggerCount')) AS 'Delete',

convert(SMALLINT,objectpropertyex(parent_ID, N'TABLEInsertTriggerCount')) AS 'Insert',

convert(SMALLINT,objectpropertyex(parent_ID, N'TABLEUpdateTriggerCount')) AS 'Update'

FROM (SELECT count(*) AS triggers, parent_ID FROM sys.triggers

      WHERE objectpropertyex(parent_ID, N'IsTable') =1

         GROUP BY parent_ID

          )TablesOnly;

--查询结果如下:

Table                            triggers    Delete Insert Update

-------------------------------- ----------- ------ ------ ------

Purchasing.Vendor                1           0      0      0

Production.WorkOrder             2           0      1      1

Purchasing.PurchaseOrderDetail   2           0      1      1

Purchasing.PurchaseOrderHeader   1           0      0      1

Sales.SalesOrderDetail           1           1      1      1

HumanResources.Employee          1           0      0      0

Sales.SalesOrderHeader           1           0      0      1

Person.Person                    1           0      1      1



(8 row(s) affected)

风流罗曼蒂克旦赶上一个触发器被触发在二个表上,它们不保险顺序,当然也足以运用sp_settriggerorder来调节顺序。通过接受objectpropertyex()元数据函数,必要依据事件输入参数‘ExecIsLastDeleteTrigger’,
‘ExecIsLastInsertTrigger’ 也许‘ExecIsLastUpdateTrigger’来承认谁是终极贰个实践的触发器
。为了获得第一个触发器,酌情接纳ObjectPropertyEx()
元数据函数,供给输入参数 ‘ExecIsFirstDeleteTrigger’,
‘ExecIsFirstInsertTrigger’ 大概 ‘ExecIsFirstUpdateTrigger’。

为此大家明日知晓了表有如何触发器,哪些事件触发这一个触发器。能够应用objectpropertyex()元数据函数,那一个函数重临非常多两样消息,遵照钦点的参数分化。通过查看MSDN中的文书档案,查看里面包车型大巴四个文书档案是不是有利于元数据查询,总是值得检查的。

trigger_name

在SQL
Server中,从概念来讲唯有语句级触发器,但万意气风发有行级的逻辑要管理,有多个仅在触发器内有效的表
(inserted, deletedState of Qatar,
寄放着受影响的行,能够从那八个表里抽出特定的行并自行定义脚本管理;

触发器曾几何时触发事件?

让大家看一下这一个触发器,DML触发器能够在具备其余时间产生后触发,可是能够在封锁被管理前还要触发INSTEAD
OF触发动作。下边大家就来拜候全部的接触的到底是AFTE福特Explorer 如故INSTEAD OF
触发器,有事什么时直接触了触发器。

/* 列出触发器,无论它们是否启用,以及触发器事件。*/

SELECT

  convert(CHAR(25),name) AS triggerName,

  convert(CHAR(32),coalesce(object_schema_name(parent_ID)+'.'

    +object_name(parent_ID),'Database ('+db_name()+')')) AS TheParent,

       is_disabled,

       CASE WHEN is_instead_of_trigger=1 THEN 'INSTEAD OF ' ELSE 'AFTER ' END

       +Stuff (--get a list of events for each trigger

        (SELECT ', '+type_desc FROM sys.trigger_events te

           WHERE te.object_ID=sys.triggers.object_ID

         FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,2,'') AS events

 FROM sys.triggers;

结果如下:

triggerName               TheParent                        is_disabled events

------------------------- -------------------------------- ----------- ---------

ddlDatabaseTriggerLog     Database (AdventureWorks2012)    1           AFTER CREATE_TABLE, ALTER_TABLE, DROP_TABLE, CREATE_VIEW, ALTER_VIEW, DROP_VIEW, CREATE_INDEX, ALTER_INDEX, DROP_INDEX, CREATE_XML_INDEX, ALTER_FULLTEXT_INDEX, CREATE_FULLTEXT_INDEX, DROP_FULLTEXT_INDEX, CREATE_SPATIAL_INDEX, CREATE_STATISTICS, UPDATE_STAT

t_AB                      dbo.AB                           0           INSTEAD OF INSERT

dEmployee                 HumanResources.Employee          0           INSTEAD OF DELETE

iuPerson                  Person.Person                    0           AFTER INSERT, UPDATE

iPurchaseOrderDetail      Purchasing.PurchaseOrderDetail   0           AFTER INSERT

uPurchaseOrderDetail      Purchasing.PurchaseOrderDetail   0           AFTER UPDATE

uPurchaseOrderHeader      Purchasing.PurchaseOrderHeader   0           AFTER UPDATE

iduSalesOrderDetail       Sales.SalesOrderDetail           0           AFTER INSERT, UPDATE, DELETE

uSalesOrderHeader         Sales.SalesOrderHeader           0           AFTER UPDATE

dVendor                   Purchasing.Vendor                0           INSTEAD OF DELETE

iWorkOrder                Production.WorkOrder             0           AFTER INSERT

uWorkOrder                Production.WorkOrder             0           AFTER UPDATE

 

As you will notice, we used a FOR XML PATH(‘’)
trick
here to make a list of the events for each trigger to make it easier to
read. These events were pulled from the sys.trigger_events view using
a correlated subquery.

只顾到我们利用了FOR XML
PATH(‘’)来列出事件的每一个触发器,更便于读取掌握。sys.trigger_events行使相关子查询来查询这一个事件。

触发器的称呼。trigger_name必需比照
标志符法规,但trigger_name不能以 # 或 ## 开头。

在ORACLE中,
对表做三遍DML操作爆发一次接触,叫语句级触发器,其余还足以由此点名[FOR
EACH
ROW]子句,对于表中受影响的每行数据均触发,叫行级触发器,原有行用:OLD表示,新行用:NEW代表;

触发器的多少长度?

有的是数据库人士不帮忙冗长触发器的概念,但他们恐怕会发觉,根据定义的尺寸排序的触发器列表是切磋数据库的黄金年代种有用艺术。

SELECT convert(CHAR(32),coalesce(object_schema_name(t.object_ID)+'.','')

    +name) AS TheTrigger,

       convert(CHAR(32),coalesce(object_schema_name(parent_ID)+'.'

    +object_name(parent_ID),'Database ('+db_name()+')')) AS theParent,

       len(definition) AS length --the length of the definition

FROM sys.SQL_modules m

  INNER JOIN sys.triggers t

    ON t.object_ID=m.object_ID

ORDER BY length DESC;

访问sys.SQL_modules视图能够查看触发器定义的SQL
DDL,并按大小顺系列出它们,最下边是最大的。

结果:

TheTrigger                       theParent                        length

-------------------------------- -------------------------------- --------

Sales.iduSalesOrderDetail        Sales.SalesOrderDetail           3666

Sales.uSalesOrderHeader          Sales.SalesOrderHeader           2907

Purchasing.uPurchaseOrderDetail  Purchasing.PurchaseOrderDetail   2657

Purchasing.iPurchaseOrderDetail  Purchasing.PurchaseOrderDetail   1967

Person.iuPerson                  Person.Person                    1498

ddlDatabaseTriggerLog            Database (AdventureWorks2012)    1235

Purchasing.dVendor               Purchasing.Vendor                1103

Production.uWorkOrder            Production.WorkOrder             1103

Purchasing.uPurchaseOrderHeader  Purchasing.PurchaseOrderHeader   1085

Production.iWorkOrder            Production.WorkOrder             1011

HumanResources.dEmployee         HumanResources.Employee          604

 

好啊,我说不允许太指责了,不太心仪太长的,不过逻辑有的时候候会非常长。事实上,前三名在笔者眼里是不可信的,固然自身连连趋势于尽或者少地接受触发器。

table|view

 

这么些触发器访谈了微微对象

在代码中,每一种触发器要拜会多少对象(比方表和函数卡塔尔(قطر‎?

我们只须要检查表明式信赖项。这么些查询利用三个视图来列出“软”正视项(如触发器、视图和函数卡塔尔。

SELECT coalesce(object_schema_name(parent_id)

          +'.','')+convert(CHAR(32),name) AS TheTrigger,

          count(*) AS Dependencies

FROM sys.triggers

INNER JOIN sys.SQL_Expression_dependencies

ON [referencing_id]=object_ID

GROUP BY name, parent_id

ORDER BY count(*) DESC;
--结果:

TheTrigger                               Dependencies

---------------------------------------- ------------

Sales.iduSalesOrderDetail                7

Sales.uSalesOrderHeader                  7

Purchasing.iPurchaseOrderDetail          5

Purchasing.uPurchaseOrderDetail          5

Purchasing.uPurchaseOrderHeader          3

Production.iWorkOrder                    3

Production.uWorkOrder                    3

dbo.t_AB                                 2

Purchasing.dVendor                       2

Person.iuPerson                          2

ddlDatabaseTriggerLog                    1

 

以致有四个触发器有7个依附!让我们就Sales.iduSalesOrderDetail来其实看一下,有哪些注重。

对其执行 DML
触发器的表或视图,有时称为触发器表或触发器视图。能够依照供给钦点表或视图的通通限制名称。视图只可以被
INSTEAD OF 触发器援用。无法对有个别或全局不时表定义 DML 触发器。

2. BEFORE/AFTER/INSTEAD OF

特定触发器访问还是写入哪些对象?

大家得以列出触发器在代码中引用的全数目的

SELECT

  convert(char(32),name) as TheTrigger,

  convert(char(32),coalesce([referenced_server_name]+'.','')

            +coalesce([referenced_database_name]+'.','')

       +coalesce([referenced_schema_name]+'.','')+[referenced_entity_name])
     as referencedObject

FROM sys.triggers

INNER JOIN sys.SQL_Expression_dependencies

ON [referencing_id]=object_ID

WHERE name LIKE 'iduSalesOrderDetail';

--查询结果:

TheTrigger                       referencedObject

-------------------------------- --------------------------------

iduSalesOrderDetail              Sales.Customer                 

iduSalesOrderDetail              Person.Person                  

iduSalesOrderDetail              Sales.SalesOrderDetail         

iduSalesOrderDetail              Sales.SalesOrderHeader          

iduSalesOrderDetail              Production.TransactionHistory  

iduSalesOrderDetail              dbo.uspLogError                

iduSalesOrderDetail              dbo.uspPrintError

 

DATABASE

在SQL Server中,从概念来讲只有AFTE科雷傲/INSTEAD
OF触发器,在表上帮助AFTE福特Explorer触发器,在表/视图上支撑INSTEAD
OF触发器,对于BEFORE触发器的供给能够品尝通过INSEAD OF触发器来兑现;

触发器里有啥样代码?

当今让我们由此检查触发器的源代码来认同那一点。.

SELECT OBJECT_DEFINITION ( object_id('sales.iduSalesOrderDetail') ); 

咱俩后边的询问是对的的,扫描源码可以预知全体的依赖项。大量信任项表名对于数据库的重构等须要丰裕小心,比如,修正三个根基表的列。

据须要做哪些,您可能希望检查来自元数据视图的定义,而不是使用OBJECT_DEFINITION函数。

 SELECT definition

FROM sys.SQL_modules m

  INNER JOIN sys.triggers t

    ON t.object_ID=m.object_ID

WHERE t.object_ID=object_id('sales.iduSalesOrderDetail');

将 DDL
触发器的作用域应用于当下数据库
。倘诺指定了此参数,则只要当前数据库中现身event_type或event_group,就能够激起该触发器。

SQL Server DML Trigger

BEFORE

AFTER

INSTEAD OF

TABLE

N/A

VIEW

N/A

N/A

找出触发器的代码

There are always plenty of ways of using the metadata views and
functions. I wonder if all these triggers are executing that
uspPrintError procedure?

有众多采纳元数据视图和函数的诀要。想理解是不是具备那一个触发器都试行uspPrintError存款和储蓄进程?

/* 在颇有触发器中检索字符串 */

 

SELECT convert(CHAR(32),coalesce(object_schema_name(object_ID)+'.','')

    +name) AS TheTrigger, '...'+substring(definition, hit-20,120) +'...'

FROM

  (SELECT name, definition, t.object_ID, charindex('EXECUTE [dbo].[uspPrintError]',definition) AS hit

   FROM sys.SQL_modules m

     INNER JOIN sys.triggers t

       ON t.object_ID=m.object_ID)f

WHERE hit>0; 

 

结果如图:

新葡亰496net 1

 

8个引用正在实行那些进度。大家在sys.SQL_modules中查找了颇负的定义可以找到三个一定的字符串,这种方式超慢很暴力,不过它是卓有功效的!

ALL SERVER

在ORACLE中,在表上支持BEFORE/AFTELacrosse触发器,在视图上支撑INSTEAD
OF触发器,比方ORACLE中不能直接对视图做DML操作,能够通过INSTEAD
OF触发器来变样达成;

在具有指标中搜索字符串

自家想清楚除了触发器之外是或不是还会有别的对象调用那么些进度?大家轻微更改查询以搜寻sys.objects视图,而不是sys.triggers,以搜索全体具有与之提到的代码的对象。大家还供给显示对象的门类

/* 在有着指标中查究字符串 */

 SELECT convert(CHAR(32),coalesce(object_schema_name(object_ID)+'.','')

    +object_name(object_ID)) AS TheObject, type_desc, '...'+substring(definition,hit-20,120)+'...' as TheExtract

FROM

  (SELECT  type_desc, definition, o.object_ID, charindex('uspPrintError',definition) AS hit

   FROM sys.SQL_modules m

     INNER JOIN sys.objects o

       ON o.object_ID=m.object_ID)f

WHERE hit>0; 

询问结果如下图:

新葡亰496net 2

 From this output we can see that, other than the procedure itself where
it is defined, and the triggers, only dbo.uspLogError is executing the
uspPrintError procedure. (see the first column, second line down)

从这些输出中大家得以看见,除了在概念它的进程自个儿之外,还恐怕有触发器,唯有dbo.uspLogError正值推行uspPrintError进度。(见第一列,第二行往下卡塔尔(قطر‎

将 DDL
或登陆触发器的功效域应用于当下服务器。假如钦命了此参数,则只要当前服务器中的任何职分上现身event_type或event_group,就能够慰勉该触发器。

ORACLE DML Trigger

BEFORE

AFTER

INSTEAD OF

TABLE

N/A

VIEW

N/A

N/A

列出劳动器级触发器及其定义

咱俩得以由此系统视图掌握它们啊?嗯,是的。以下是列出服务器触发器及其定义的话语

 SELECT name, definition

FROM sys.server_SQL_modules m

  INNER JOIN sys.server_triggers t

ON t.object_ID=m.object_ID; 

悉心,只可以见到有权力看的触发器

WITH ENCRYPTION

 

总结

  本文商量过触发器,并且你能获悉触发器,以致潜在的标题。这里并不曾指向性有关触发器的查询提供叁个到家的工具箱,因为本身只是使用触发器作为示范来显示在查询系统视图时恐怕选拔的有个别技艺。在大家学习了目录、列和参数之后,大家将回到触发器,并打听了编写制定访谈系统视图和information
schema视图的询问的大器晚成部分常备用处。表是元数据的超多上边的底蕴。它们是三种档案的次序的靶子的父类,别的元数据如索引是表的品质。大家正在日益地质大学力去开采具有有关表的消息。期望上期

对 CREATE TLANDIGGETucson 语句的文件进行模糊管理。使用 WITH ENC奇骏YPTION
可防止卫将触发器作为 SQL Server 复制的一片段开展揭露。不能为 CL智跑触发器钦赐 WITH ENC哈弗YPTION。

3. 接触条件

EXECUTE AS

(1) 无法接触的情景

内定用于实践该触发器的乌兰察布上下文。允许你决定 SQL Server
实例用于注脚被触发器引用的轻巧数据库对象的权限的客商帐户。

对于UPDATE,DELETE操作来说,均会触发触发器;而对此INSERT或许说IMPORT的状态,是能够调节不去接触的。

FOR | AFTER

  • 大量导入操作,如:BULK INSERT, bcp/INSERT… SELECT * FROM
    OPENROWSET,都有FIRE_TRIGGERS/IGNORE_TKugaIGGE奥德赛S选项,可以安装是不是接触触发器;
  • 导入导出向导/SSIS,如若指标是表,也可能有FIRE_THavalIGGE纳瓦拉S的装置选项;
  • 此外truncate操作也不会触发;

AFTE本田CR-V 内定 DML 触发器仅在触发 SQL
语句中钦点的具备操作都已经成功实践时才被触发。全部的引用级联操作和封锁检查也亟须在激情此触发器以前成功做到。

(2) 嵌套触发器 (Nested Triggers卡塔尔国, 循环/递归触发器 (Recursive
Triggers卡塔尔

即使仅钦命 FO奥德赛 关键字,则 AFTEEnclave 为暗中认可值。

嵌套触发器,正是二回操作触发了二个触发器,然后触发器里的言语继续接触别的触发器,尽管一而再再而三回头触发了和煦,那么正是递归触发器。

不可能对视图定义 AFTE安德拉 触发器。

对于AFTELAND触发器有个五个按键分别调节嵌套触发和递归触发:

INSTEAD OF

exec sp_configure 'nested triggers'

钦点施行 DML 触发器实际不是触发 SQL
语句
,由此,其优先级高于触发语句的操作。不能够为 DDL 或登陆触发器指定INSTEAD OF。

那几个参数暗许值为1,
也正是说允许AFTETiguan触发器嵌套,最多嵌套32层,设为0正是分化意AFTERubicon触发器嵌套,如下:

对此表或视图,各种 INSERT、UPDATE 或 DELETE 语句最多可定义四个 INSTEAD
OF 触发器。可是,可感觉具备温馨的 INSTEAD OF 触发器的七个视图定义视图。

exec sp_configure 'nested triggers',0
RECONFIGURE

INSTEAD OF 触发器不得以用来采用 WITH CHECK OPTION 的可更新视图。假如将
INSTEAD OF 触发器加多到内定了 WITH CHECK OPTION 的可更新视图中,则 SQL
Server 将吸引错误。顾客须用 ALTECR-V VIEW 删除该选项后才具定义 INSTEAD OF
触发器。

但以此参数有四个其余:

{ [DELETE] [,] [INSERT] [,] [UPDATE] }

  • INSTEAD OF触发器,能够嵌套,不受这几个参数按钮与否影响;
  • AFTE本田CR-V触发器,就算展开该选取,也不会和谐嵌套本身(即递归卡塔尔,除非展开了RECULX570SIVE_T奇骏IGGE奥德赛S选项,相当于循环/递归触发器;

    –create table, sql server 2016 & higher
    drop table if exists A
    GO
    create table A(id int)
    GO

    –create DML trigger
    drop trigger if exists tri_01
    GO
    create TRIGGER tri_01
    ON A
    AFTER INSERT, UPDATE, DELETE
    as
    begin

    if @@NESTLEVEL = 32
    begin
        return
    end 
    insert A values(0)
    

    end
    GO

    –check nested triggers server option
    exec sp_configure ‘nested triggers’
    –name minimum maximum config_value run_value
    –nested triggers 0 1 1 1

    –test with RECURSIVE_TRIGGERS off
    ALTER DATABASE dba set RECURSIVE_TRIGGERS off
    select is_recursive_triggers_on, from sys.databases
    GO
    insert A values(1)
    select
    from A
    –id
    –1
    –0

    –test with RECURSIVE_TRIGGERS on
    ALTER DATABASE dba set RECURSIVE_TRIGGERS on
    select is_recursive_triggers_on, * from sys.databases
    GO

    truncate table A
    insert A values(1)
    select * from A –32 rows

    –若无加@@NESTLEVEL判别并脱离,会现身32层约束的报错,何况表里不会插入任何数据
    /*
    Msg 217, Level 16, State 1, Procedure tri_01, Line 10
    Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

    select from A –0 rows/

    –删表会级联删除触发器,就如索引
    drop table A

钦点数量改进语句,那一个语句可在 DML
触发器对此表或视图举办尝试时激活该触发器
。必需最少钦定贰个取舍。在触发器定义中允许利用上述选项的人身自由顺序组合。

 

对于 INSTEAD OF 触发器,不容许对拥有钦点级联操作 ON DELETE
的引用关系的表使用 DELETE 选项。同样,也不准对具有内定级联操作 ON
UPDATE 的引用关系的表使用 UPDATE 选项。

循环/递归触发器的前提就是嵌套触发器,唯有同意嵌套了本事够递归(递归也正是嵌套并触及自身卡塔尔国,递归有一直和直接二种情景:

标签:

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图