>

深究SQL Server元数据(二)

- 编辑:金沙国际平台登录 -

深究SQL Server元数据(二)

背景

  上一篇中,作者介绍了SQL Server 允许访谈数据库的元数据,为啥有元数据,怎么样使用元数据。这一篇中笔者会介绍怎么样进一步找到各个有价值的音讯。以触发器为例,因为它们往往一齐相当多标题。

 

触发器可以领会为由特定事件触发的寄放进度, 和仓库储存进度、函数同样,触发器也支持CLKoleos,这几天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’来搜寻你想要的视图名称。.

在2013会同现在版本,能够动用三个新的表值函数比相当大地简化上述查询,并得避防止各类连接。在下边包车型地铁查询中,大家将追寻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, ALTELX570, DROP 等) 触发;

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

不过当然一个触发器是第一是八个指标,因此一定在sys.objects?

  在大家利用sys.triggers的信息此前,须求来再次壹遍,全数的数据库对象都设有于sys.objects中,在SQL Server 中的对象包含以下:聚合的CL奥迪Q5函数,check 约束,SQL标量函数,CLMurano标量函数,CL宝马X5表值函数,SQL内联表值函数,内部表,SQL存款和储蓄进度,CL中华V存款和储蓄进程,陈设指南,主键约束,老式准绳,复制过滤程序,系统基础表,同义词,体系对象,服务队列,CLTiguanDML 触发器,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

。可是多少个表能够有几个AFTETiguan触发器行为。那么些将显示在底下的查询中(排除视图):

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触发动作。上面大家就来拜会全部的触及的到底是AFTEENCORE 依旧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中,从概念来讲唯有AFTEEnclave/INSTEAD OF触发器,在表上协理AFTEPRADO触发器,在表/视图上补助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; 

 

结果如图:

图片 1

 

8个援引正在进行那几个进度。大家在sys.SQL_modules中寻觅了全体的定义可以找到贰个一定的字符串,这种办法不快很暴力,不过它是立见作用的!

在ORACLE中,在表上协助BEFORE/AFTE瑞鹰触发器,在视图上协理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; 

询问结果如下图:

图片 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_TSportageIGGE卡宴S选项,能够设置是不是接触触发器;
  • 导入导出向导/SSIS,借使目的是表,也会有FIRE_TRubiconIGGELANDS的安装选项;
  • 除此以外truncate操作也不会触发;

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

嵌套触发器,正是一回操作触发了叁个触发器,然后触发器里的讲话继续接触别的触发器,假使继续回头触发了协和,那么就是递归触发器。

对于AFTE本田CR-V触发器有个四个开关分别调整嵌套触发和递归触发:

exec sp_configure 'nested triggers'

这一个参数暗中同意值为1, 也正是说允许AFTEHighlander触发器嵌套,最多嵌套32层,设为0就是不容许AFTE凯雷德触发器嵌套,如下:

exec sp_configure 'nested triggers',0
RECONFIGURE

但那一个参数有七个另外:

  • INSTEAD OF触发器,能够嵌套,不受那些参数开关与否影响;
  • AFTE福特Explorer触发器,纵然展开该选拔,也不会本人嵌套本身(即递归),除非打开了RECU奥迪Q3SIVE_T大切诺基IGGELacrosseS选项,也正是循环/递归触发器;

    --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_TWranglerIGGE奥迪Q5S,仅对直接递归有效,对直接递归无效;能够因而Nest Triggers的开关来决定是还是不是允许嵌套,从而决定是不是允许间接递归;

  • 任由直接递归,还是间接递归,递归次数都有21次嵌套的上限;

计算下来:

  1. AFTE奥迪Q5触发器,暗许Nest Triggers值为1,即允许触发器嵌套,上限32层,直接递归也是足以的,间接递归须求敞开数据库选项RECU途锐SIVE_TRIGGERS;

  2. INSTEAD OF触发器,不受Nest Triggers选项影响,均能够嵌套,上限32层,间接递归也是足以的,直接递归无论是不是开启数据库选项RECUSIVE_T翼虎IGGERAV4S,都不行;把地点五个本子示例中的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 贰零零柒从头帮助DDL触发器,它不只限于对CREATE/ALTELAND/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来读取;

 

代码示例1:记录所有table上的一点DDL操作

--记录所有create table操作
if OBJECT_ID('ddl_log','U') is not null
    drop table ddl_log
GO

create table ddl_log
(
LogID        int identity(1,1),
EventType    varchar(50), 
ObjectName   varchar(256),
ObjectType   varchar(25),
TSQLCommand  varchar(max),
LoginName    varchar(256)
)
GO

if exists(select * from sys.triggers where name = 'TABLE_DDL_LOG' and parent_class_desc = 'DATABASE')
    drop trigger TABLE_DDL_LOG on database;
GO

create trigger TABLE_DDL_LOG
on database
for create_table
as
begin
    set nocount on 

    declare @data xml
    set @data = EVENTDATA()

    insert into ddl_log
    values
    (@data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)'), 
    @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'), 
    @data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(25)'), 
    @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)'), 
    @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)')
    )
end
GO

drop table if exists test_dll_trigger;
create table test_dll_trigger (id int)
select * from ddl_log

 

代码示例2:禁止特定剧中人物的顾客对特定的表做DROP操作

IF exists(select * from sys.triggers where name = 'NO_DROP_TABLE' and parent_class_desc = 'DATABASE')
    DROP TRIGGER [NO_DROP_TABLE] ON DATABASE;
GO

CREATE TRIGGER NO_DROP_TABLE
ON DATABASE
FOR DROP_TABLE
AS
BEGIN
    DECLARE @x                XML,
            @user_name        varchar(100),
            @db_name          varchar(100),  
            @schema_name      varchar(100),
            @object_name      varchar(200)

    --select eventdata()
    SET @x = EVENTDATA();
    SET @user_name = @x.value('(/EVENT_INSTANCE/UserName)[1]','varchar(100)');
    SET @db_name = @x.value('(/EVENT_INSTANCE/DatabaseName)[1]','varchar(100)');
    SET @schema_name = @x.value('(/EVENT_INSTANCE/SchemaName)[1]','varchar(100)');
    SET @object_name = @x.value('(/EVENT_INSTANCE/ObjectName)[1]','varchar(100)');

    --PRINT 'Current User: '     + @user_name
    --PRINT 'Current Database: ' + @db_name
    --PRINT 'Schema Name: '      + @schema_name
    --PRINT 'Table Name: '       + @object_name

    IF is_rolemember('disallow_modify_tables',@user_name) = 1
       AND @db_name = 'YOUR_DB_NAME'
       AND @schema_name = 'YOUR_SCHEMA_NAME'
       AND @object_name like 'YOUR_TABLE_NAME%'
    BEGIN 
        PRINT 'Dropping tables is not allowed'
        ROLLBACK
    END
END
GO

 

三. LOGON 触发器

SQL Server 二零零七在SP第22中学私自引进了LOGON触发器,作为叁个实例级的指标,它的类别视图,定义语句和DDL/DML触发器都是分开的。

select * from sys.server_triggers where name = 'login_history_trigger'
select * from sys.server_trigger_events
select OBJECT_ID('login_history_trigger') --无法获取

在SQL Server中,看名称就会想到其意义,LOGON触发器,只扶助LOGON事件;

在ORACLE中,实例级触发器可支撑更加的多事件 (SEHighlanderVEREEvoqueROPAJERO, LOGON, LOGOFF, STARTUP, or SHUTDOWN)。

 

代码示例1: 笔录全数login登陆历史 (其实也得以通过修改login auditing选项,来记录成功和挫败的登陆在errorlog里)

IF OBJECT_ID('login_history','U') is not null
    DROP TABLE login_history
GO

CREATE TABLE login_history
(
FACT_ID         bigint IDENTITY(1,1) primary key,
LOGIN_NAME      nvarchar(1024),
LOGIN_TIME      datetime
)
GO

IF EXISTS(select 1 from sys.server_triggers where name = 'login_history_trigger')
    DROP TRIGGER login_history_trigger ON ALL SERVER
GO

CREATE TRIGGER login_history_trigger
ON ALL SERVER
FOR LOGON
AS
BEGIN
    --IF SUSER_NAME() NOT LIKE 'NT AUTHORITY%' AND 
    --   SUSER_NAME() NOT LIKE 'NT SERVICE%'
    IF ORIGINAL_LOGIN() NOT LIKE 'NT AUTHORITY%' AND
       ORIGINAL_LOGIN() NOT LIKE 'NT SERVICE%'
    BEGIN
        INSERT INTO DBA..login_history
        VALUES(ORIGINAL_LOGIN(),GETDATE());
    END;
END;
GO

--view login history after logon
SELECT * FROM login_history

 

代码示例2: 限定特定客户在特按期间限定登陆、限制连接数

--限制下班时间不能登录
DROP TRIGGER IF EXISTS limit_user_login_time ON ALL SERVER
GO
CREATE TRIGGER limit_user_login_time
ON ALL SERVER FOR LOGON 
AS
BEGIN
    IF ORIGINAL_LOGIN() = 'TestUser' 
       AND (DATEPART(HOUR, GETDATE()) < 9 OR DATEPART (HOUR, GETDATE()) > 18)
    BEGIN
        PRINT 'TestUser can only login during working hours!'
        ROLLBACK
    END
END
GO

--限制连接数
DROP TRIGGER IF EXISTS limit_user_connections ON ALL SERVER
GO
CREATE TRIGGER limit_user_connections
ON ALL SERVER 
WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
    IF ORIGINAL_LOGIN() = 'TestUser' 
       AND (SELECT COUNT(*) FROM   sys.dm_exec_sessions
            WHERE  Is_User_Process = 1 
            AND Original_Login_Name = 'TestUser') > 2
    BEGIN
        PRINT 'TestUser can only have 1 active session!'
        ROLLBACK
    END
END

 

瞩目:倘若LOGON触发器把全数人都锁在外部了如何做?

Logon failed for login 'TestUser' due to trigger execution.

图片 3

这会儿,只可以通过DAC登入SQL Server去禁止使用LOGON触发器/修改逻辑以允许登陆,DAC登入格局有长途和地面三种,远程登陆要求通过sp_configure 开启remote admin connections ,若无优先开启,这就只能采纳本地登入方式:

服务器本地,在SSMS中经过DAC登入

图片 4

 

服务器本地,在cmd中经过DAC登入

图片 5

--禁用/启用LOGON触发器
DISABLE TRIGGER limit_user_connections ON ALL SERVER
ENABLE TRIGGER limit_user_connections ON ALL SERVER

 

参考:

CREATE TRIGGER (Transact-SQL)

Create Nested Triggers

Transact-SQL statements

Why we can‘t use commit in trigger, can anyone give proper explanation

Database PL/SQL Language Reference, Using Triggers

本文由 数据库发布,转载请注明来源:深究SQL Server元数据(二)