背景

  上一篇中,小编介绍了SQL Server
允许访谈数据库的元数据,为何有元数据,如何运用元数据。这一篇中笔者会介绍怎样进一步找到各个有价值的新闻。以触发器为例,因为它们往往一齐比非常多主题材料。

 

触发器能够领悟为由特定事件触发的存款和储蓄进度,
和存款和储蓄进度、函数相同,触发器也协助CL奥迪Q7,如今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;
  1. DML触发器, 表/视图级有效,可由DML语句 (INSERT, UPDATE, DELETE)
    触发;

  2. DDL 触发器,数据库级有效,可由DDL语句 (CREATE, ALTEENVISION, DROP 等) 触发;

  3. LOGON 触发器, 实例级有效,可由用户账号登陆(LOGON)数据库实例时接触;

只是当然叁个触发器是率先是贰个指标,由此一定在sys.objects?

  在我们运用sys.triggers的新闻此前,须求来重新三遍,全数的数据库对象都留存于sys.objects中,在SQL
Server 中的对象包蕴以下:聚合的CLXC90函数,check
约束,SQL标量函数,CLENCORE标量函数,CLEnclave表值函数,SQL内联表值函数,内部表,SQL存款和储蓄进度,CLLacrosse存款和储蓄进度,陈设指南,主键约束,老式准绳,复制过滤程序,系统基础表,同义词,类别对象,服务队列,CL昂CoraDML
触发器,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触发器关联,稍后大家将明白。

一. 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()用来询问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

 

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

本身的表和视图有多少个触发器?

本人想精通种种表有多少个触发器,并且什么状态下接触它们。下边大家列出了全数触发器的表以及各类事件的触发器数量。各个表也许视图对于触发器行为都有二个INSTEAD
OF 触发器,或者是UPDATE, DELETE, 或许 INSERT

。不过贰个表能够有八个AFTE卡宴触发器行为。这个将浮未来上边包车型大巴询问中(排除视图):

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中的文书档案,查看里面包车型大巴一个文书档案是不是有助于元数据查询,总是值得检查的。

在SQL
Server中,从概念来讲独有语句级触发器,但只要有行级的逻辑要拍卖,有多个仅在触发器内卓有效率的表
(inserted, deleted),
贮存着受影响的行,能够从那多个表里抽出特定的行并自行定义脚本管理;

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

让大家看一下那些触发器,DML触发器能够在具备其余时间发出后触发,可是足以在封锁被拍卖前还要触发INSTEAD
OF触发动作。上面大家就来探访全体的接触的到底是AFTE哈弗 依旧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使用相关子查询来查询这么些事件。

在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

 

好吧,笔者或然太责怪了,不太喜欢太长的,不过逻辑一时候会十分长。事实上,前三名在小编眼里是不可靠的,尽管自身接二连三侧向于尽也许少地行使触发器。

 

这个触发器访谈了多少对象

在代码中,每一种触发器要看望多少对象(举例表和函数)?

作者们只须要检查表达式重视项。那一个查询利用贰个视图来列出“软”正视项(如触发器、视图和函数)。

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来其实看一下,有何正视。

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

 

在SQL Server中,从概念来说只有AFTE本田CR-V/INSTEAD
OF触发器,在表上支持AFTEXC60触发器,在表/视图上扶助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');

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; 

 

结果如图:

4503.com 1

 

8个引用正在实行那一个进程。大家在sys.SQL_modules中查找了有着的定义可以找到两个特定的字符串,这种格局非常慢很暴力,不过它是可行的!

在ORACLE中,在表上接济BEFORE/AFTE宝马X3触发器,在视图上支撑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; 

查询结果如下图:

4503.com 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进度。(见第一列,第二行往下)

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; 

瞩目,只好见到有权力看的触发器

 

总结

  本文研讨过触发器,并且你能搜查缉获触发器,以及潜在的主题材料。这里并未针对性有关触发器的询问提供叁个健全的工具箱,因为自己只是选择触发器作为示范来展现在查询系统视图时只怕选用的有个别本事。在我们学习了目录、列和参数之后,我们将回到触发器,并问询了编写制定访谈系统视图和information
schema视图的查询的片段平日用途。表是元数据的重重上边的基本功。它们是几连串型的靶子的父类,其余元数据如索引是表的天性。大家正在慢慢地质大学力去开掘持有关于表的音信。期待下一期

3. 接触条件

(1) 无法接触的状态

对此UPDATE,DELETE操作来讲,均会接触触发器;而对于INSERT或许说IMPORT的动静,是足以决定不去接触的。

  • 大批量导入操作,如:BULK INSERT, bcp/INSERT… SELECT * FROM
    OPENROWSET,都有FIRE_TRIGGERS/IGNORE_TCRUISERIGGE途胜S选项,能够安装是或不是接触触发器;
  • 导入导出向导/SSIS,假若指标是表,也可能有FIRE_TSportageIGGE中华VS的装置选项;
  • 别的truncate操作也不会接触;

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

嵌套触发器,正是贰回操作触发了贰个触发器,然后触发器里的话语继续接触其余触发器,借使继续回头触发了和煦,那么正是递归触发器。

对于AFTE奥德赛触发器有个七个开关分别调控嵌套触发和递归触发:

exec sp_configure 'nested triggers'

本条参数私下认可值为1,
约等于说允许AFTEPAJERO触发器嵌套,最多嵌套32层,设为0正是不允许AFTE汉兰达触发器嵌套,如下:

exec sp_configure 'nested triggers',0
RECONFIGURE

但以此参数有多少个别的:

  • INSTEAD OF触发器,能够嵌套,不受那一个参数按键与否影响;
  • AFTEPRADO触发器,尽管展开该采纳,也不会友善嵌套自个儿(即递归),除非展开了RECU本田CR-VSIVE_T福特ExplorerIGGERAV4S选项,也正是循环/递归触发器;

    –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

 

循环/递归触发器的前提正是嵌套触发器,独有同意嵌套了才得以递归(递归约等于嵌套并触及自身),递归有间接和间接三种情景:

  • 一贯递归:就是A表的DML触发器再回去对A表实行DML操作,如上例;
  • 直接递归:正是A表DML触发器去操作B表,然后B表上触发器回来操作A表,如下例;

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

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

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

    end
    GO

    create TRIGGER tri_02
    ON B
    AFTER INSERT, UPDATE, DELETE
    as
    begin

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

    end
    GO

    –test with nested triggers server option ON
    exec sp_configure ‘nested triggers’,1
    RECONFIGURE

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

    truncate table A
    truncate table B
    insert A values(1)
    select from A –16 rows
    select
    from B –16 rows

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

    truncate table A
    truncate table B
    insert A values(1)
    select from A –16 rows
    select
    from B –16 rows

    –test with nested triggers server option OFF
    exec sp_configure ‘nested triggers’,0
    RECONFIGURE

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

    truncate table A
    truncate table B
    insert A values(1)
    select from A –1
    select
    from B –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
    truncate table B
    insert A values(1)
    select from A –1
    select
    from B –0

    –删表会级联删除触发器,仿佛索引
    drop table A, B

  • 能够看出数据库选项RECU凯雷德SIVE_T中华VIGGE中华VS,仅对直接递归有效,对直接递归无效;可以因而Nest
    Triggers的开关来决定是或不是允许嵌套,进而决定是不是同意直接递归;

  • 无论间接递归,还是直接递归,递归次数都有叁十二次嵌套的上限;

总计下来:

  1. AFTERubicon触发器,暗中认可Nest
    Triggers值为1,即允许触发器嵌套,上限32层,间接递归也是足以的,直接递归要求展开数据库选项RECUPRADOSIVE_TRIGGERS;

  2. INSTEAD OF触发器,不受Nest
    Triggers选项影响,均可以嵌套,上限32层,直接递归也是足以的,直接递归无论是还是不是开启数据库选项RECUSIVE_T大切诺基IGGE本田UR-VS,都没用;把位置五个本子示例中的AFTE汉兰达改为INSTEAD
    OF就能够演示。

 

4.
触发器中不能够commit/rollback事务

--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)
    commit
end
GO

begin tran
insert A values(1)
/*
Msg 3609, Level 16, State 1, Procedure tri_01, Line 10
The transaction ended in the trigger. The batch has been aborted.
*/

在SQL
Server和Oracle中都以那样,触发器作为一切事情的一某个存在,然则并不调整总体育赛工作的交给/回滚,为保险数据一致性,事务逻辑由触发器外层的语句来调控。

 

二. DDL触发器

SQL Server
2007初阶帮衬DDL触发器,它不只限于对CREATE/ALTEEvoque/DROP操作可行,协助的DDL事件还应该有诸如:权限的GRANT/DENY/REVOEK,
对象的RENAME, 更新总结音信等等,可由此DMV查看更加的多扶助的风云类型如下:

select * from sys.trigger_event_types
where type_name not like '%CREATE%'
  and type_name not like '%ALTER%'
  and type_name not like '%DROP%'

注意:

  1. TRUNCATE不在DDL触发器的事件类型中,SQL Server准将Truncate
    归为DML操作语句,固然它也并不触发DML触发器,仿佛展开开关的大量导入操作
    (Bulk Import Operations) 同样;

2.
DDL触发器中捕获的音讯都由EVENTDATA()函数重返,重回类型为XML格式,须求用XQuery来读取;