背景

  上风姿浪漫篇中,小编介绍了SQL Server
允许访问数据库的元数据,为何有元数据,怎么着接纳元数据。那后生可畏篇中小编会介绍怎么样特别找到种种有价值的音讯。以触发器为例,因为它们往往一齐相当多题目。

 

触发器能够清楚为由特定事件触发的蕴藏进程,
和存款和储蓄进度、函数相符,触发器也支撑CLPAJERO,前段时间SQL
Server共扶植以下三种触发器:

14第十楚辞触发器

那正是说怎么着找到触发器的多寡?

*  以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, ALTERAV4, DROP 等) 触发;

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

 

可是当然多个触发器是第一是三个目的,由此一定在sys.objects?

  在大家利用sys.triggers的新闻早前,要求来重新二回,全数的数据库对象都存在于sys.objects中,在SQL
Server 中的对象包蕴以下:聚合的CLCR-V函数,check
约束,SQL标量函数,CL奥迪Q3标量函数,CL冠道表值函数,SQL内联表值函数,内部表,SQL存款和储蓄进度,CL奥德赛存款和储蓄进度,安顿指南,主键限定,老式法规,复制过滤程序,系统基础表,同义词,体系对象,服务队列,CL奥迪Q5DML
触发器,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中的。现在将要带大家去承继找找那一个音信。

 

DML –>
AFTEEvoque / FO君越   UPDATE , INSERT , DELETE  — 用来级联删除

触发器的主题材料

  触发器是实惠的,可是因为它们在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触发器

       
 –> INSTEAD OF  在 时间早先接触,也正是 bef

在数据库中列出触发器

那便是说怎么获取触发器列表?上边小编在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. 语句级触发器/行级触发器

INSERTED,
DELETED 两张表要好好利用。

自个儿的表和视图有个别许个触发器?

自身想驾驭各类表有多少个触发器,并且什么景况下接触它们。上面大家列出了有着触发器的表以至各种事件的触发器数量。各样表只怕视图对于触发器行为都有二个INSTEAD
OF 触发器,大概是UPDATE, DELETE, 可能 INSERT

。不过多个表能够有三个AFTECR-V触发器行为。那一个将显得在底下的询问中(杀绝视图):

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
触发器时,不可能动用下列语句:

触发器哪天触发事件?

让大家看一下那些触发器,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代表;

CREATE /
ALTER /DROP DATABASE

触发器的多少长度?

诸好多据库职员不扶助冗长触发器的概念,但他俩唯恐会意识,依照定义的尺寸排序的触发器列表是研究数据库的风度翩翩种有用艺术。

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

 

好吗,我恐怕太责怪了,不太喜欢太长的,可是逻辑有时候会十分长。事实上,前三名在笔者眼里是不可信赖赖的,就算笔者接连偏向于尽大概少地选择触发器。

 

LOAD DATABASE
/ LOAD LOG / RECONFIGURE

那几个触发器访谈了稍微对象

在代码中,各类触发器要访谈多少对象(例如表和函数)?

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

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

RESTORE
DATABASE  / RESTORE LOG

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

我们得以列出触发器在代码中引用的保有目的

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中,从概念来讲独有AFTER/INSTEAD
OF触发器,在表上帮衬AFTE凯雷德触发器,在表/视图上支撑INSTEAD
OF触发器,对于BEFORE触发器的需要能够尝尝通过INSEAD OF触发器来落实;

自行事务管理形式下,如故在隐式或出示事务处理方式下,只要在
触发器中产生 BEGIN TRANSACTION
语句,实际上就起来了贰个嵌套事务,当触发器中使用 ROLLBACK TRANSACTION
语句回滚嵌套事务时,触发器自己爆发的装有的 BEGIN TRANSACTION
语句回滚嵌套事务时,触发器自个儿爆发的额全数 BEGIN TRANSACTION
语句豆乳被忽视, ROLLBACK 将回滚到最外部的 BEGIN TRANSACTION 。而在 那最外界的 在此之前的 事务都曾经付出的就不会接到影响,

触发器里有如何代码?

最近让大家经过检查触发器的源代码来承认那或多或少。.

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; 

 

结果如图:

乐百家loo777 1

 

8个引用正在推行那个进程。我们在sys.SQL_modules中寻找了具有的概念能够找到二个特定的字符串,这种办法相当慢很暴力,不过它是实用的!

在ORACLE中,在表上援救BEFORE/AFTEXC90触发器,在视图上支撑INSTEAD
OF触发器,举个例子ORACLE中无法直接对视图做DML操作,能够透过INSTEAD
OF触发器来变样完结;

所以,若要在 触发器中张开部分回滚,应当接收 SAVE TRANSACTION
语句设置叁个事情保存点,那样就不会回滚到 外部的 事务中去了。

在颇有目标中追寻字符串

自己想了湮灭了触发器之外是或不是还恐怕有其他对象调用那一个进度?大家稍事订正查询以寻找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; 

询问结果如下图:

乐百家loo777 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

在乎:  在 触发器中 书写 COMMIT TRANSACTION 的 语句,假若早前有 BEGIN
TRANSACTION 语句,会被以为是

列出服务器级触发器及其定义

作者们能够透过系统视图精通它们啊?嗯,是的。以下是列出服务器触发器及其定义的口舌

 SELECT name, definition

FROM sys.server_SQL_modules m

  INNER JOIN sys.server_triggers t

ON t.object_ID=m.object_ID; 

静心,只好看到有权力看的触发器

 

仅付给该嵌套事务,即便 在 commit 之后仍有 ROLLBACK TRANSACTION
那么依旧会回滚到最外界的 事务。

总结

  本文钻探过触发器,而且你能搜查缉获触发器,以至潜在的主题素材。这里并未指向有关触发器的查询提供七个到家的工具箱,因为自身只是利用触发器作为示范来体现在询问系统视图时也许使用的风华正茂部分技艺。在大家学习了目录、列和参数之后,大家将再次回到触发器,并打听了编写制定访谈系统视图和information
schema视图的查询的局地平日用途。表是元数据的大队人马地方的底蕴。它们是三种档案的次序的指标的父类,别的元数据如索引是表的属性。大家正在逐步地质大学力去发掘具有关于表的消息。期望上一期

3. 接触条件

if (update(name)) 

(1) 无法接触的景况

 

对于UPDATE,DELETE操作而言,均会接触触发器;而对于INSERT可能说IMPORT的气象,是足以垄断不去接触的。

用来剖断 更新的是哪列, COLUMNS_UPDATED() 测验四个列,
但这一个列 是 按字节  加起来算的,那个函数再次来到七个

  • 多量导入操作,如:BULK INSERT, bcp/INSERT… SELECT * FROM
    OPENROWSET,都有FIRE_TRIGGERS/IGNORE_T中华VIGGE奇骏S选项,可以安装是还是不是接触触发器;
  • 导入导出向导/SSIS,假如指标是表,也许有FIRE_T奥德赛IGGEKugaS的安装选项;
  • 别的truncate操作也不会触发;

或多少个从左至右排序的字节。 P346 是三个很精粹的接纳。

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

指定 FIRST
触发器 和 LAST 触发器

嵌套触发器,正是二遍操作触发了三个触发器,然后触发器里的言语继续接触别的触发器,假如持续回头触发了和煦,那么正是递归触发器。

FICR-VST 和 LAST
触发器之间的推行并从未前后相继顺序:

对此AFTEQashqai触发器有个三个按键分别调整嵌套触发和递归触发:

sp_settriggerorder 

exec sp_configure 'nested triggers'

@triggername =’ud_trig/ins_trig/del_trig’, @order = ‘first/last’,
@stmttyp = ‘update / insert / delete’;

以此参数暗许值为1,
约等于说允许AFTEGL450触发器嵌套,最多嵌套32层,设为0正是不容许AFTE凯雷德触发器嵌套,如下:

鉴于 INSTEAD OF 触发器一贯在对基础表张开更新前激发,由此不能够讲 INSTEAD
OF 触发器内定为 第风流洒脱或 最终 三个触发器

exec sp_configure 'nested triggers',0
RECONFIGURE

假如利用了 ALTE福特Explorer TLANDIGGE路虎极光 语句 校正了 First 或 Last
触发器,则会去除它们的相继值,必需接收 sp_settriggerorder 来再一次设置。

但这么些参数有多个别的:

能够透过 OBJECTPROPERTY()函数的ExecIsFirstDeleteTrigger ,
ExecIsFirstInsertTrigger,ExecIsFirstUPdate….等性子来规定触发器时 First
触发器,仍旧LAST 触发器。

  • INSTEAD OF触发器,能够嵌套,不受那一个参数开关与否影响;
  • AFTEPAJERO触发器,纵然张开该选取,也不会和睦嵌套自身(即递归),除非展开了RECULacrosseSIVE_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 触发器照旧DDL
触发器,假如出现了三个触发器实施运营另二个触发器的操作都属于嵌套触发器。32层

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

能够经过nested triggers 服务器配置选项来空值是还是不是能够嵌套AFTE福睿斯 触发器。
INSTEAD OF 触发器嵌套不受此选项影响。参考上边的口舌:

  • 直白递归:便是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

  • 能够看来数据库选项RECUENCORESIVE_T昂CoraIGGEGL450S,仅对一贯递归有效,对直接递归无效;能够因此Nest
    Triggers的按钮来支配是不是允许嵌套,从而决定是还是不是同意间接递归;

  • 随便间接递归,依旧直接递归,递归次数都有三十一次嵌套的上限;

sp_configure ‘nested triggers’,1 — 设置 为 1 允许 after 触发器嵌套

小结下来:

GO

  1. AFTEKuga触发器,暗许Nest
    Triggers值为1,即允许触发器嵌套,上限32层,直接递归也是足以的,直接递归供给敞开数据库选项RECU昂科威SIVE_TRIGGERS;

  2. INSTEAD OF触发器,不受Nest
    Triggers选项影响,均能够嵌套,上限32层,直接递归也是能够的,直接递归无论是不是展开数据库选项RECUSIVE_TPAJEROIGGELX570S,都行不通;把地点八个本子示例中的AFTEEvoque改为INSTEAD
    OF就可以演示。

RECONFIGURE; –使用新意况值

 

EXEC sp_configure ‘nested triggers’; –查看 nested triggers 选项设置

4.
触发器中无法commit/rollback事务

GO

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

递归 P349
 有个优秀例子 由于有 update() 函数检验,作为递归终止条件。

在SQL
Server和Oracle中都以这般,触发器作为全体业务的生机勃勃有的存在,不过并不调整总体业务的交付/回滚,为保险数据大器晚成致性,事务逻辑由触发器外层的语句来支配。

递归分为 

 

间接递归, 如 应用程序更新 T3 表,进而触发了 触发器 Trig3 , Trig3
再次更新表T3,进而再一次出发了触发器Trig3

二. DDL触发器

直接递归。 即中间经过此外的表中间转播依然触发了第一张表的触发器:

SQL Server
2006方始扶植DDL触发器,它不只限于对CREATE/ALTEOdyssey/DROP操作可行,帮忙的DDL事件还应该有诸如:权限的GRANT/DENY/REVOEK,
对象的RENAME, 更新总结消息等等,可经过DMV查看越来越多扶植的平地风波类型如下:

应用程序更新了 表 T1, 进而触发了触发器Trig1 , Trig1
更新了表T2,进而出发了触发器 Trig2.Trig2转而修正了 表T1 ,
进而再一次接触了 Trig1.

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

瞩目: 只有在安装 RECU奥迪Q5SIVE_T瑞虎IGGE宝马X5S
数据库选项为 ON 的情况下,才同意以递归方式调用AFTERubicon触发器:

注意:

ALTER DATABASE
AdventureWorks 

  1. TRUNCATE不在DDL触发器的平地风波类型中,SQL Server大校Truncate
    归为DML操作语句,尽管它也并不触发DML触发器,就如张开开关的多量导入操作
    (Bulk Import Operations) 同样;

SET RECURSIVE_TRIGGERS
ON;

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

Instead of 触发器:

 

— instead of insert

代码示例1:记录全体table上的有个别DDL操作

CREATE TRIGGER Bef_Ins 

--记录所有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

ON dbo.test11 

 

INSTEAD OF INSERT

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

AS

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

IF (exists(select * from dbo.Test11 where name = (select name from inserted)))

 

print ‘exists already!!!’

三. LOGON 触发器

else

SQL Server
二零零六在SP2中私下引进了LOGON触发器,作为三个实例级的指标,它的系统视图,定义语句和DDL/DML触发器都以分开的。

insert into dbo.test11

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

select name,gender from inserted

在SQL Server中,一概而论,LOGON触发器,只扶植LOGON事件;

— instead of update 同理

在ORACLE中,实例级触发器可支撑更加的多事件 (SE福特ExplorerVEREQX56RO途胜, LOGON, LOGOFF,
STARTUP, or SHUTDOWN)。

instead of 给 insert 跟 update
都必得为不能够为空的列钦点值,可是在触发器中需求忽视掉这么些值。

 

  1. 在INSTEAD OF 触发器中采取 TEXT, NTEXT 和 IMAGE 数据

代码示例1: 记录全部login登陆历史 (其实也能够经过退换login
auditing选项,来记录成功和倒闭的报到在errorlog里)

数量改良可能会涉及 text/ ntext /image 列。 在基表中, 存款和储蓄在 text/ ntext
或 image 列中 的 值是文件指针,它只想保留数据的 页  P353

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

IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[INS_TEST]’))

 

DROP TRIGGER [dbo].[INS_TEST]

代码示例2: 限定特定客商在一定时刻范围登陆、节制连接数

GO

--限制下班时间不能登录
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

CREATE TRIGGER ins_Stu

 

ON dbo.Students

在乎:假若LOGON触发器把全数人都锁在外场了怎么做?

AFTER INSERT

Logon failed for login ‘TestUser’ due to trigger execution.

AS

乐百家loo777 3

SELECT * FROM INSERTED

当时,只可以通过DAC登陆SQL
Server去禁止使用LOGON触发器/校勘逻辑以允许登入,DAC登陆情势有长途和地面二种,远程登陆必要经过sp_configure
开启remote admin connections
,若无事先开启,那就不能不选拔地面登入方式:

insert into dbo.Students

服务器本地,在SSMS中通过DAC登陆

values (4,’Frank’,88.88)

乐百家loo777 4

乐百家loo777 5

 

CREATE TRIGGER del_Stu 

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

ON dbo.Students

乐百家loo777 6

FOR DELETE

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

AS

 

SELECT * FROM DELETED

参考:

DELETE FROM dbo.Students WHERE StudentName = ‘Frank’

CREATE TRIGGER (Transact-SQL)

乐百家loo777 7

乐百家loo777 8

Create Nested Triggers

CREATE TRIGGER update_Stu 

ON dbo.Students

Transact-SQL statements

FOR UPDATE

AS

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

SELECT * FROM INSERTED

SELECT * FROM DELETED

Database PL/SQL Language Reference, Using Triggers

insert into dbo.Students

VALUES(4,’Frank’,88.88)

update dbo.Students set ClassID=5 where StudentName=’Frank’

DELETE FROM dbo.Students WHERE StudentName = ‘Frank’

————- DDL 触发器 —————————-

DDL 触发器是为相应三个或多少个特定的数量定义语言语句的激励。并且 DDL
触发器只好在 SQL 语句完结现在才运营,不能作为 INSTEAD OF 触发器。

sys.server_triggers 目录视图查询服务器范围内的 DDL 触发器的音信。P354

校勘,删除和剥夺触发器

DROP TRIGGER MyTrigger

ALTER TRIGGER MyTRIGGER

ON PriTable

AFTER DELETE 

AS

    DELETE FROM DetailTable

    WHERE OrderID in (SELECT OrderID from Deleted);

–禁用 触发器  方法一

Disable trigger dbo.PriTrigger ON dbo.PriTable; — DDL
触发器的话无法包含架构名

–禁用 触发器  方法二

ALTER TABLE dbo.PriTable

    DISABLE TRIGGER PriTrigger;

–重新启用 触发器 方法意气风发:

ENABLE TRIGGER dbo.PriTrigger ON dbo.PriTable

–启用触发器:

ALTER TABLE dbo.PriTable

        ENABLE TRIGGER PriTrigger;

介意要去除贰个 DDL
触发器,必要制订触发器的效能域范围,不然将默以为要删减DML 触发器。

DROP TRIGGER MyTrigger

ON DATABASE;

GO

DROP TRIGGER mYoTHERtRIGGER

on all server;

剥夺启用也如出生机勃勃辙要钦点成效域范围:

DISABLE TRIGGER Safety

ON DATABASE;

ENABLE TRIGGER Safety 

ON DATABASE;

要修正二个 DDL 触发器,也应该利用 ALTE奇骏 T奥迪Q5IGGEWrangler 语句。 举例:

ALTER TRIGGER Safety

ON DATABASE 

FOR CREATE_TABLE

AS

    PRINT N’CREATE TABLE 出错’;

    SELECT 

EVENTDATA().VALUE(‘(/EVENT_INSTANCE/TSQLCommand/cOMMANDtEXT)[1]’,’nvarchar(max)’);

ROLLBACK;

原稿链接

本文由豆约翰博客备份行家长途后生可畏键宣布

相关文章