探究SQL Server元数据(二)

搜寻触发器的代码

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中寻找了具备的定义可以找到一个一定的字符串,这种办法非常的慢很暴力,不过它是一蹴而就的!

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

在数据库中列出触发器

那正是说怎么获取触发器列表?下面笔者在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

 

--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.
*/

特定触发器访问依然写入哪些对象?

我们得以列出触发器在代码中援引的具有指标

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

 

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

那就是说什么样找到触发器的数码?

*  以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;
  • 大量导入操作,如:BULK INSERT, bcp/INSERT… SELECT * FROM
    OPENROWSET,都有FIRE_TRIGGERS/IGNORE_T翼虎IGGE本田CR-VS选项,能够设置是还是不是接触触发器;
  • 导入导出向导/SSIS,要是指标是表,也可以有FIRE_T中华VIGGE奥迪Q3S的安装选项;
  • 除此以外truncate操作也不会触发;

总结

  本文商量过触发器,并且你能意识到触发器,以及地下的难题。这里并未指向有关触发器的查询提供三个圆满的工具箱,因为小编只是选用触发器作为示范来呈未来查询系统视图时可能使用的局地技术。在大家上学了目录、列和参数之后,大家将重回触发器,并询问了编辑访谈系统视图和information
schema视图的查询的一对家常用途。表是元数据的非常多地点的根基。它们是几类别型的对象的父类,别的元数据如索引是表的习性。我们正在日渐地拼命去开采持有有关表的消息。期待下一期

 

在具有指标中寻找字符串

自个儿想精通除了触发器之外是不是还应该有别的对象调用那么些进度?大家略微修改查询以搜寻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进度。(见第一列,第二行往下)

触发器能够明白为由特定事件触发的积存进程,
和仓库储存进度、函数相同,触发器也协助CLEvoque,如今SQL
Server共帮助以下两种触发器:

触发器的难点

  触发器是有效的,然则因为它们在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触发器关联,稍后我们将驾驭。

CREATE TRIGGER (Transact-SQL)

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

本身想清楚各样表有多少个触发器,而且什么动静下接触它们。上边大家列出了颇具触发器的表以及各样事件的触发器数量。每一种表大概视图对于触发器行为都有二个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中的文书档案,查看里面包车型客车二个文书档案是不是有利于元数据查询,总是值得检查的。

 

那么些触发器访问了某个对象

在代码中,每一种触发器要访谈多少对象(比方表和函数)?

我们只要求检讨表明式注重项。那些查询利用三个视图来列出“软”信赖项(如触发器、视图和函数)。

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来其实看一下,有哪些信赖。

小心:假使LOGON触发器把全部人都锁在外侧了如何是好?

触发器的多少长度?

相当的多数据库职员不赞同冗长触发器的概念,但他俩大概会开采,依据定义的长短排序的触发器列表是切磋数据库的一种有用艺术。

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

 

行吗,笔者或许太叱责了,不太喜欢太长的,但是逻辑不经常候会不长。事实上,前三名在作者眼里是不可相信赖的,即便笔者老是偏侧于尽只怕少地使用触发器。

SQL Server
二零零五在SP第22中学悄悄引进了LOGON触发器,作为三个实例级的靶子,它的系统视图,定义语句和DDL/DML触发器都以分手的。

触发器里有怎么着代码?

当今让我们通过检查触发器的源代码来确认那或多或少。.

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');

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

背景

  上一篇中,作者介绍了SQL Server
允许采访数据库的元数据,为啥有元数据,怎样运用元数据。这一篇中我会介绍怎么样特别找到各类有价值的音信。以触发器为例,因为它们往往一齐比比较多难点。

 

循环/递归触发器的前提便是嵌套触发器,唯有同意嵌套了才方可递归(递归也正是嵌套并触及本人),递归有直接和直接二种情状:

但是当然三个触发器是首先是三个对象,由此一定在sys.objects?

  在大家选用sys.triggers的音信以前,供给来重新一次,全数的数据库对象都存在于sys.objects中,在SQL
Server 中的对象满含以下:聚合的CLLAND函数,check
约束,SQL标量函数,CLPAJERO标量函数,CLXC90表值函数,SQL内联表值函数,内部表,SQL存款和储蓄进程,CL福特Explorer存款和储蓄进程,安插指南,主键约束,老式法规,复制过滤程序,系统基础表,同义词,连串对象,服务队列,CL大切诺基DML
触发器,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中的。今后将在带大家去继续找找那一个新闻。

三. LOGON 触发器

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

大家得以经过系统视图明白它们啊?嗯,是的。以下是列出服务器触发器及其定义的讲话

 SELECT name, definition

FROM sys.server_SQL_modules m

  INNER JOIN sys.server_triggers t

ON t.object_ID=m.object_ID; 

留神,只好看到有权力看的触发器

那时候,只可以通过DAC登陆SQL
Server去禁止使用LOGON触发器/修改逻辑以允许登陆,DAC登录方式有长途和地点三种,远程登入要求通过sp_configure
开启remote admin connections
,若无事先开启,这就不得不选择地面登陆方式:

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

让我们看一下这么些触发器,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选用相关子查询来查询这几个事件。

 

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

参考:

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

3. 接触条件

 

SQL Server DML Trigger

BEFORE

AFTER

INSTEAD OF

TABLE

N/A

VIEW

N/A

N/A

在SQL Server中,从概念来讲唯有AFTEENCORE/INSTEAD
OF触发器,在表上帮忙AFTE奥德赛触发器,在表/视图上帮忙INSTEAD
OF触发器,对于BEFORE触发器的急需能够尝尝通过INSEAD OF触发器来落到实处;

二. DDL触发器

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

  • INSTEAD OF触发器,能够嵌套,不受那一个参数按键与否影响;
  • AFTE库罗德触发器,就算张开该采纳,也不会友善嵌套本身(即递归),除非展开了RECU昂科威SIVE_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

嵌套触发器,便是二次操作触发了三个触发器,然后触发器里的讲话继续接触别的触发器,假若持续回头触发了团结,那么就是递归触发器。

 

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

小结下来:

exec sp_configure 'nested triggers',0
RECONFIGURE

2. BEFORE/AFTER/INSTEAD OF

 

  1. TRUNCATE不在DDL触发器的平地风波类型中,SQL Server中校Truncate
    归为DML操作语句,尽管它也并不触发DML触发器,仿佛展开按钮的大量导入操作
    (Bulk Import Operations) 一样;

(1) 不可能接触的情形

但那么些参数有多少个其它:

--记录所有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
select * from sys.trigger_event_types
where type_name not like '%CREATE%'
  and type_name not like '%ALTER%'
  and type_name not like '%DROP%'

Database PL/SQL Language Reference, Using Triggers

一. DML触发器

网站地图xml地图