图片 6

14第十四章触发器

背景

  上一篇中,我介绍了SQL Server
允许访问数据库的元数据,为什么有元数据,如何使用元数据。这一篇中我会介绍如何进一步找到各种有价值的信息。以触发器为例,因为它们往往一起很多问题。

 

触发器可以理解为由特定事件触发的存储过程,
和存储过程、函数一样,触发器也支持CLR,目前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’来查找你想要的视图名称。.

在2012及其以后版本,可以使用一个新的表值函数极大地简化上述查询,并可以避免各种连接。在下面的查询中,我们将查找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, ALTER, DROP 等) 触发;

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

 

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

  在我们使用sys.triggers的信息之前,需要来重复一遍,所有的数据库对象都存在于sys.objects中,在SQL
Server 中的对象包括以下:聚合的CLR函数,check
约束,SQL标量函数,CLR标量函数,CLR表值函数,SQL内联表值函数,内部表,SQL存储过程,CLR存储过程,计划指南,主键约束,老式规则,复制过滤程序,系统基础表,同义词,序列对象,服务队列,CLR
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中的。现在就要带大家去继续找找这些信息。

 

DML –>
AFTER / FOR   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

。但是一个表可以有多个AFTER触发器行为。这些将展示在下面的查询中(排除视图):

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触发动作。下面我们就来看看所有的触发的到底是AFTER 还是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触发器,在表上支持AFTER触发器,在表/视图上支持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; 

 

结果如图:

图片 1

 

8个引用正在执行这个过程。我们在sys.SQL_modules中搜索了所有的定义可以找到一个特定的字符串,这种方式很慢很暴力,但是它是有效的!

在ORACLE中,在表上支持BEFORE/AFTER触发器,在视图上支持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; 

查询结果如下图:

图片 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_TRIGGERS选项,可以设置是否触发触发器;
  • 导入导出向导/SSIS,如果目标是表,也有FIRE_TRIGGERS的设置选项;
  • 另外truncate操作也不会触发;

或多个从左至右排序的字节。 P346 是一个很经典的应用。

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

指定 FIRST
触发器 和 LAST 触发器

嵌套触发器,就是一次操作触发了一个触发器,然后触发器里的语句继续触发其他触发器,如果继续回头触发了自己,那么就是递归触发器。

FIRST 和 LAST
触发器之间的执行并没有先后顺序:

对于AFTER触发器有个两个开关分别控制嵌套触发和递归触发:

sp_settriggerorder 

exec sp_configure 'nested triggers'

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

这个参数默认值为1,
也就是说允许AFTER触发器嵌套,最多嵌套32层,设为0就是不允许AFTER触发器嵌套,如下:

由于 INSTEAD OF 触发器一直在对基础表进行更新前激发,因此不能讲 INSTEAD
OF 触发器指定为 第一或 最后 一个触发器

exec sp_configure 'nested triggers',0
RECONFIGURE

如果使用了 ALTER TRIGGER 语句 更改了 First 或 Last
触发器,则会删除它们的顺序值,必须使用 sp_settriggerorder 来重新设置。

但这个参数有两个另外:

可以通过 OBJECTPROPERTY()函数的ExecIsFirstDeleteTrigger ,
ExecIsFirstInsertTrigger,ExecIsFirstUPdate….等属性来确定触发器时 First
触发器,还是LAST 触发器。

  • INSTEAD OF触发器,可以嵌套,不受这个参数开关与否影响;
  • AFTER触发器,即使打开该选项,也不会自己嵌套自己(即递归),除非打开了RECURSIVE_TRIGGERS选项,也就是循环/递归触发器;

    –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 服务器配置选项来空值是否可以嵌套AFTER 触发器。
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

  • 可以看出数据库选项RECURSIVE_TRIGGERS,仅对直接递归有效,对间接递归无效;可以通过Nest
    Triggers的开关来控制是否允许嵌套,从而控制是否允许间接递归;

  • 不论直接递归,还是间接递归,递归次数都有32次嵌套的上限;

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

总结下来:

GO

  1. AFTER触发器,默认Nest
    Triggers值为1,即允许触发器嵌套,上限32层,间接递归也是可以的,直接递归需要开启数据库选项RECURSIVE_TRIGGERS;

  2. INSTEAD OF触发器,不受Nest
    Triggers选项影响,均可以嵌套,上限32层,间接递归也是可以的,直接递归无论是否开启数据库选项RECUSIVE_TRIGGERS,都无效;把上面两个脚本示例中的AFTER改为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
2005开始支持DDL触发器,它不只限于对CREATE/ALTER/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%'

注意: 只有在设置 RECURSIVE_TRIGGERS
数据库选项为 ON 的情况下,才允许以递归方式调用AFTER
触发器:

注意:

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
2005在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中,实例级触发器可支持更多事件 (SERVERERROR, 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

图片 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)

图片 4

图片 5

 

CREATE TRIGGER del_Stu 

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

ON dbo.Students

图片 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)

图片 7

图片 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 触发器,也应当使用 ALTER TRIGGER 语句。 例如:

ALTER TRIGGER Safety

ON DATABASE 

FOR CREATE_TABLE

AS

    PRINT N’CREATE TABLE 出错’;

    SELECT 

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

ROLLBACK;

原文链接

本文由豆约翰博客备份专家远程一键发布

发表评论