SQL Server 成立触发器(trigger)

让我们查询出数据库中使用触发器的信息,Server版本中有什么触发器,语句级触发器/行级触发器,Server共支持以下几种触发器,它是与表事件相关的特殊的存储过程,DML触发器、替代触发器和系统触发器,DML事件是指在表或视图中对数据进行的 insert、update、delete,DDL事件是指在表或索引中的 create、alter、drop 操作语句

金沙国际唯一官网网址 9

那么哪些找到触发器的多寡?

*  以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;

嵌套触发器,正是三回操作触发了多个触发器,然后触发器里的言语继续接触其余触发器,倘诺持续回头触发了协调,那么正是递归触发器。

Oracle数据库之PL/SQL触发器

1. 介绍

触发器(trigger)是数据库提供给程序员和数量分析员来保障数据完整性的一种格局,它是与表事件相关的奇特的仓库储存进程,它的施行不是由程序调用,也不是手工业运维,而是由事件来触发,比如当对二个表进行操作(insert,delete,update)时就会激活它执行。触发器平日用来压实数据的完整性约束和业务规则等。

Oracle触发器有二种档次,分别是:DML触发器、替代触发器和系统触发器。

DML触发器

顾名思义,DML触发器是由DML语句触发的。例如数据库的INSE奥迪Q叁T、UPDATE、DELETE操作都得以触发该品种的触发器。它们得以在那一个言辞在此以前或现在触发,可能在行级上接触(便是说对于每一个受影响的行都触发一遍)。

取而代之触发器

取代触发器只可以选择在视图上,与DML分裂的是,DML触发器是运营在DML之外的,而代表触发器是顶替激发它的DML语句运转。替代触发器是行触发器。

系统触发器

这种触发器是发出在如数据库运行或关闭等体系事件时,不是在进行DML语句时发生,当然也得以在DDL时接触。

触发器效能强大,轻松可信地达成广大繁杂的效劳,但是我们也理应慎用。为何又要慎用呢?触发器本身并没有偏差,但若是大家滥用,会导致数据库及应用程序的珍重困难。在数据库操作中,大家得以经过关系、触发器、存款和储蓄进程、应用程序等来贯彻数据操作,同时约束、缺省值也是保险数据完整性的重中之重保证。假诺大家对触发器过分的重视性,势必影响数据库的构造,同时扩充了保卫安全的复杂程度。

2. 触发器组成

触发器首要由以下多少个因素构成:

  1. 接触事件:引起触发器被触发的事件。
  2. 接触时间:触发器是在接触事件发生此前(BEFORE)照旧之后(AFTE瑞虎)触发,也正是接触事件和该触发器的操作顺序。
  3. 接触操作:触发器被触发之后的指标和意图,是触发器自身要做的工作。
  4. 接触对象:包罗表、视图、格局、数据库。唯有在那些目的上爆发了符合触发条件的触及事件,才会实施触发操作。
  5. 接触条件:由WHEN子句内定3个逻辑表达式。唯有当该表明式的值为TRUE时,碰着触发事件才会自动执行触发器,使其实施触发操作。
  6. 触发频率:表明触发器钦命义的动作被执行的频率。即语句级(STATEMENT)触发器和行级(ROW)触发器: 
    语句级(STATEMENT)触发器:是指当某触发事件发生时,该触发器只进行三次; 
    行级(ROW)触发器:是指当某触发事件时有产生时,对饱受该操作影响的每壹行数据,触发器都单身实施1回。

3. 创造触发器

语法:

CREATE [ OR REPLACE ] TRIGGER plsql_trigger_source

plsql_trigger_source ::=

[schema.] trigger_name
  { simple_dml_trigger
  | instead_of_dml_trigger
  | compound_dml_trigger
  | system_trigger
  }

simple_dml_trigger ::=

{ BEFORE | AFTER } dml_event_clause [ referencing_clause ] [ FOR EACH ROW ]
  [ trigger_edition_clause ] [ trigger_ordering_clause ]
    [ ENABLE | DISABLE ] [ WHEN ( condition ) ] trigger_body

instead_of_dml_trigger ::=

INSTEAD OF { DELETE | INSERT | UPDATE } [ OR { DELETE | INSERT | UPDATE } ]...
ON [ NESTED TABLE nested_table_column OF ] [ schema. ] noneditioning_view
[ referencing_clause ] [ FOR EACH ROW ]
[ trigger_edition_clause ] [ trigger_ordering_clause ]
[ ENABLE | DISABLE ] trigger_body

system_trigger ::=

{ BEFORE | AFTER | INSTEAD OF }
{ ddl_event [OR ddl_event]...
| database_event [OR database_event]...
}
ON { [schema.] SCHEMA
   | DATABASE
   }
[ trigger_ordering clause ]

dml_event_clause ::=

{ DELETE | INSERT | UPDATE [ OF column [, column ]... ] }
[ OR { DELETE | INSERT | UPDATE [ OF column [, column]... ] }...
ON [ schema.] { table | view }

referencing_clause ::=

REFERENCING
 { OLD [ AS ] old
 | NEW [ AS ] new
 | PARENT [ AS ] parent
 }...

trigger_body ::=

{ plsql_block | CALL routine_clause }

全体的语法结构见:

说明:

BEFORE和AFTEPAJERO提出触发器的接触时间分别为前触发和后触发模式,前触发是在推行触发事件在此之前接触当前所开创的触发器,后触发是在执行触发事件之后触发当前所创办的触发器。

REFERENCING子句表达有关称号,在行触发器的PL/SQL块和WHEN子句中得以应用有关称号参照当前的新、旧列值,私下认可的连锁称号为OLD和NEW。触发器的PL/SQL块中采取相关称号时,必须在它们在此之前加冒号(:),但在WHEN子句中则不能加冒号。

NEW只在UPDATE、INSE陆风X8T的DML触发器内可用,它蕴涵了改动发生后被潜移默化行的值。

OLD只在UPDATE、DELETE的DML触发器内可用,它蕴涵了修改发生前被潜移默化行的值。

FOLAND EACH
ROW选项表达触发器为行触发器。行触发器和话语触发器的差异表今后:行触发器需要当八个DML语句操走影响数据库中的多行数据时,对于里边的各种数据行,只要它们符合触发约束规范,均激活1遍触发器;而说话触发器将全部讲话操作作为触发事件,当它符合约束规范时,激活3次触发器。当省略FOR
EACH ROW 选项时,BEFORE和AFTEKuga触发器为语句触发器,而INSTEAD
OF触发器则只好为行触发器。

WHEN子句表明触发约束原则。Condition为二个逻辑表明时,个中必须包涵相关称号,而不可能包括查询语句,也不能够调用PL/SQL函数。WHEN子句钦命的触及约束原则只好用在BEFORE和AFTE帕杰罗行触发器中,无法用在INSTEAD
OF行触发器和其余种类的触发器中。

INSTEAD
OF选项(成立替代触发器)使ORACLE激活触发器,而不实施触发事件。只可以对视图和指标视图建立INSTEAD
OF触发器,而无法对表、形式和数据库建立INSTEAD OF触发器。

ddl_event:3个或多少个DDL事件,事件间用O猎豹CS四分开。

database_event:一个或多少个数据库事件,事件间用O瑞鹰分开。

以身作则一,在插入数据时,自动使用体系编号:

CREATE OR REPLACE TRIGGER EMP_INSERT_ID
BEFORE INSERT ON employee FOR EACH ROW
BEGIN
   SELECT SEQ_ID.NEXTVAL INTO :NEW.ID FROM DUAL;
END;

示例二,在多表联接的视图中插入数据:

-- 创建视图
CREATE OR REPLACE VIEW vw_emp AS
SELECT e.name ename, e.address, d.name dname
FROM employee e, dept d
WHERE e.did = d.id;

-- 创建触发器
CREATE TRIGGER emp_insert_trigger
   INSTEAD OF INSERT ON vw_emp
DECLARE
   v_did dept.id%TYPE;
BEGIN
   SELECT id INTO v_did FROM dept WHERE name = :NEW.dname;
   INSERT INTO emp (name, address, did) VALUES (:NEW.ename, :NEW.address, v_did);
END emp_insert_trigger;

示例叁,成立实例运转触发器:

-- 创建记录操作事件的表
CREATE TABLE event_table(
   event VARCHAR2(50),
   time DATE
);

-- 创建触发器
CREATE OR REPLACE TRIGGER tr_startup
   AFTER STARTUP
   ON DATABASE
BEGIN
   INSERT INTO event_table(event, time)
    VALUES(ora_sysevent, SYSDATE);
END;

4. DML触发器

DML触发器对大家开发职员来说是最常用的。DML触发器是由数据库的INSERAV四T、UPDATE、DELETE操作触发,该类触发器能够在上述讲话在此以前或今后执行,也足以每一种受影响的行执行贰回。

标准谓词:当在触发器中包涵四个触发事件(INSEBMWX3T、UPDATE、DELETE)的咬合时,为了分别指向分化的事件开展不一样的拍卖,供给利用ORACLE提供的规则谓词:

  1. INSEPRADOTING:当触发事件是INSEPRADOT时,取值为TRUE,不然为FALSE。
  2. UPDATING
    [(column_1,column_2,…,column_x)]:当触发事件是UPDATE时,若是改动了column_x列,则取值为TRUE,不然为FALSE。
  3. DELETING:当触发事件是DELETE时,则取值为TRUE,否则为FALSE。

示例:

CREATE OR REPLACE TRIGGER emp_sal_trigger
   BEFORE UPDATE OF salary OR DELETE
   ON employee FOR EACH ROW
   WHEN (old.did = 1)
BEGIN
  CASE
     WHEN UPDATING ('salary') THEN
        IF :NEW.salary < :old.salary THEN
           RAISE_APPLICATION_ERROR(-20001, '部门1的员工工资不能降');
        END IF;
     WHEN DELETING THEN
          RAISE_APPLICATION_ERROR(-20002, '不能删除部门1的员工记录');
  END CASE;
END emp_sal_trigger;

5. 替代触发器

INSTEAD
OF用于对视图的DML触发,由于视图有十分的大希望是由七个表联结(JOIN)而成,由此不用全部的视图都以可更新的,但足以依据所需的方法实施更新。

创造INSTEAD OF触发器要求专注以下几点:

  1. 不得不被创制在视图上,并且该视图未有点名WITH CHECK OPTION选项。
  2. 不可能钦赐BEFORE或AFTECRUISER选项。
  3. FO冠道 EACH ROW子句是可选的。
  4. 从未有过供给在针对2个表的视图上创立INSTEAD
    OF触发器,只要创建DML触发器就能够了。

示例:

CREATE OR REPLACE TRIGGER emp_delete_trigger
   INSTEAD OF DELETE ON vw_emp FOR EACH ROW
DECLARE
   v_did dept.id%TYPE;
BEGIN
   SELEC id INTO v_did FROM dept WHERE name=:OLD.dname;
   DELETE FROM employee WHERE did= v_did;
END emp_delete_trigger;

6. 系统触发器

系统触发器能够在DDL或数据库系统上被触发,数据库系统事件包罗数据库服务器的启航或关闭,用户的登录与脱离、数据库服务错误等。

系统事件触发器既能够创设在一个方式上,又足以建立在整个数据库上。当建立在情势(SCHEMA)之上时,唯有模式所内定用户的DDL操作和它们所造成的不当才激活触发器,默许时为当下用户格局。当建立在数据库(DATABASE)之上时,该数据库全数用户的DDL操作和他们所造成的荒谬,以及数据库的运行和倒闭均可激活触发器。

系统触发器的类型和事件出现的机遇:

事件 触发时机 说明
STARTUP AFTER 启动数据库实例之后触发
SHUTDOWN BEFORE 关闭数据库实例之前触发
SERVERERROR AFTER 数据库服务器发生错误之后触发
LOGON AFTER 成功登录到数据库后触发
LOGOFF BEFORE 断开数据库连接之前触发
DDL BEFORE,AFTER 在执行大多数DDL语句之前、之后触发
CREATE / ALTER / DROP BEFORE,AFTER 在执行CREATE或ALTER或DROP语句创建数据库对象之前、之后触发
RENAME BEFORE,AFTER 执行RENAME语句更改数据库对象名称之前、之后触发
GRANT / REVOKE BEFORE,AFTER 执行GRANT语句授予权限或REVOKE撤销权限之前、之后触发
AUDIT / NOAUDIT BEFORE,AFTER 执行AUDIT或NOAUDIT进行审计或停止审计之前、之后触发

示例:

-- 创建记录用户登录注销日志的表
CREATE TABLE log_on_off_log
(user_name VARCHAR2(20),
 logon_date timestamp,
 logoff_date timestamp);

-- 创建登录触发器
CREATE OR REPLACE TRIGGER logon_trigger
   AFTER LOGON ON DATABASE
BEGIN
   INSERT INTO log_on_off_log (user_name, logon_date) VALUES (ora_login_user, systimestamp);
END logon_trigger;

-- 创建退出触发器
CREATE OR REPLACE TRIGGER logoff_trigger
   BEFORE LOGOFF ON DATABASE
BEGIN
   INSERT INTO log_on_off_log (user_name, logoff_date) VALUES (ora_login_user, systimestamp);
END logoff_trigger;

 

一、DML( 数据操纵语言 Data Manipulation
Language)触发器:是指触发器在数据库中发生 DML
事件时将启用。DML事件是指在表或视图中对数据开始展览的 insert、update、delete
操作的语句。

本身的表和视图有稍许个触发器?

本人想精通各样表有多少个触发器,并且什么境况下接触它们。上边大家列出了独具触发器的表以及各样事件的触发器数量。各类表大概视图对于触发器行为都有三个INSTEAD
OF 触发器,或许是UPDATE, DELETE, 或许 INSEPRADOT

。可是一个表能够有三个AFTEXC60触发器行为。那几个将浮以往上面包车型客车查询中(排除视图):

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’来承认哪个人是终极3个履行的触发器
。为了得到第多个触发器,酌情接纳ObjectPropertyEx()
元数据函数,须求输入参数 ‘ExecIsFirstDeleteTrigger’,
‘ExecIsFirstInsertTrigger’ 大概 ‘ExecIsFirstUpdateTrigger’。

故而大家今天清楚了表有如何触发器,哪些事件触发那几个触发器。能够采取objectpropertyex()元数据函数,这么些函数再次回到很多见仁见智音讯,依照内定的参数差异。通过查阅MSDN中的文书档案,查看里面包车型地铁1个文书档案是还是不是有助于元数据查询,总是值得检查的。

但以此参数有八个其它:

参数:

触发器的多少长度?

过多数据库职员不赞同冗长触发器的概念,但他俩大概会发现,依据定义的长短排序的触发器列表是探究数据库的1种有用艺术。

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

 

好啊,笔者说不定太挑剔了,不太喜欢太长的,可是逻辑有时候会相当短。事实上,前3名以作者之见是离谱的,尽管小编接连倾向于尽恐怕少地利用触发器。

 

SCHEMABINDING:
点名触发器引用的表无法被删去或转移。

触发器几时触发事件?

让大家看一下那么些触发器,DML触发器能够在享有别的时间产生后触发,但是可以在封锁被处理前还要触发INSTEAD
OF触发动作。下边我们就来探视全数的接触的到底是AFTE猎豹CS陆 依然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(‘’)来列出事件的每2个触发器,更易于读取精晓。sys.trigger_events采纳相关子查询来查询这一个事件。

SQL Server DML Trigger

BEFORE

AFTER

INSTEAD OF

TABLE

N/A

VIEW

N/A

N/A

那个触发器访问了不怎么对象

在代码中,各个触发器要访问多少对象(比如表和函数)?

我们只须求检查表明式正视项。这一个查询利用3个视图来列出“软”正视项(如触发器、视图和函数)。

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

 

竟然有四个触发器有几个依靠!让大家就Sales.iduSalesOrderDetail来实在看一下,有怎样依赖。

  1. DML触发器, 表/视图级有效,可由DML语句 (INSE大切诺基T, UPDATE, DELETE)
    触发;

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

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

金沙国际唯一官网网址 1

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

大家得以经过系统视图掌握它们啊?嗯,是的。以下是列出服务器触发器及其定义的口舌

 SELECT name, definition

FROM sys.server_SQL_modules m

  INNER JOIN sys.server_triggers t

ON t.object_ID=m.object_ID; 

在意,只可以看看有权力看的触发器

在ORACLE中,在表上支持BEFORE/AFTECR-V触发器,在视图上辅助INSTEAD
OF触发器,比如ORACLE中不可能间接对视图做DML操作,能够通过INSTEAD
OF触发器来变样达成;

instead of 触发器:

然则当然三个触发器是首先是1个目的,因而一定在sys.objects?

  在大家运用sys.triggers的新闻从前,要求来重新三回,全体的数据库对象都留存于sys.objects中,在SQL
Server 中的对象包罗以下:聚合的CLBMWX3函数,check
约束,SQL标量函数,CLWrangler标量函数,CL奥迪Q7表值函数,SQL内联表值函数,内部表,SQL存款和储蓄进程,CL奥迪Q7存款和储蓄进度,计划指南,主键约束,老式规则,复制过滤程序,系统基础表,同义词,类别对象,服务队列,CLCR-VDML
触发器,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触发器
DISABLE TRIGGER limit_user_connections ON ALL SERVER
ENABLE TRIGGER limit_user_connections ON ALL 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; 

 

结果如图:

金沙国际唯一官网网址 2

 

九个引用正在实行那么些进程。大家在sys.SQL_modules中摸索了富有的定义能够找到一个特定的字符串,那种方法非常的慢很暴力,可是它是有效的!

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

将 DDL
或登录触发器的功用域应用于近期服务器。假使钦命,触发器会在现阶段服务器的任啥地点方时有产生 event_type 或 event_group 时触发。

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

我们能够列出触发器在代码中援引的持有指标

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

 

二.
DDL触发器中抓获的音信都由EVENTDATA()函数再次来到,再次来到类型为XML格式,要求用XQuery来读取;

金沙国际唯一官网网址 3

背景

  上1篇中,小编介绍了SQL Server
允许访问数据库的元数据,为啥有元数据,怎样利用元数据。这壹篇中小编会介绍怎么着特别找到各样有价值的消息。以触发器为例,因为它们往往一起很多难题。

 

 

以下是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');

循环/递归触发器的前提便是嵌套触发器,唯有同意嵌套了才足以递归(递归也正是嵌套并触及本身),递归有平素和直接三种状态:

 1 CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name   
 2 ON { table }   
 3 [ WITH <dml_trigger_option> [ ,...n ] ]  
 4 { FOR | AFTER }   
 5 { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }   
 6 AS { sql_statement  [ ; ] [ ,...n ] }  
 7 
 8 <dml_trigger_option> ::=  
 9     [ NATIVE_COMPILATION ]  
10     [ SCHEMABINDING ]  
11     [ EXECUTE AS Clause ]

在颇具目的中找寻字符串

自作者想驾驭除了触发器之外是还是不是还有其它对象调用那一个进度?大家有点修改查询以寻找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; 

查询结果如下图:

金沙国际唯一官网网址 4

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

 

修改触发器:

触发器的题材

  触发器是卓有成效的,可是因为它们在SSMS对象能源管理器窗格中不是可见的,所以1般用来唤起错误。触发器有时候会有个别微妙的地点让其出标题,比如,当导入进程中禁止使用了触发器,并且由于一些原因他们未有重启。

上边是二个有关触发器的简易提醒:

  触发器能够在视图,表只怕服务器上,任何这一个目标上都得以有跨越3个触发器。普通的DML触发器能被定义来执行替代一些数额修改(Insert,Update恐怕Delete)恐怕在数据修改今后执行。每2个触发器与只与二个目的管理。DDL触发器与数据库关联或许被定义在服务器级别,那类触发器1般在Create,Alter可能Drop那类SQL语句执行后触发。

  像DML触发器1样,能够有多少个DDL触发器被创造在同3个T-SQL语句上。2个DDL触发器和语句触发它的话语在同贰个工作中运转,所以除了Alter
DATABASE之外都得以被回滚。DDL触发器运转在T-SQL语句执行完结后,也正是不能够作为Instead
OF触发器使用。

  三种触发器都与事件有关,在DML触发器中,包罗INSE福特ExplorerT, UPDATE,
和DELETE,但是无数事件都足以与DDL触发器关联,稍后我们将精晓。

参考:

触发器简介:

总结

  本文商量过触发器,并且你能搜查捕获触发器,以及地下的题材。那里并不曾指向有关触发器的询问提供八个到家的工具箱,因为小编只是使用触发器作为示范来浮以后查询系统视图时或许行使的有的技艺。在我们上学了目录、列和参数之后,我们将再次回到触发器,并询问了编写访问系统视图和information
schema视图的查询的①对普普通通用途。表是元数据的无数方面包车型客车功底。它们是几连串型的指标的父类,其余元数据如索引是表的属性。我们正在日益地努力去发现拥有有关表的消息。期待下期

代码示例二:禁止特定角色的用户对特定的表做DROP操作

金沙国际唯一官网网址 5

在数据库中列出触发器

这就是说怎么获取触发器列表?下边我在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

 

在SQL
Server中,从概念来说唯有语句级触发器,但如若有行级的逻辑要拍卖,有七个仅在触发器内行之有效的表
(inserted, deleted),
存放着受影响的行,能够从那多个表里取出特定的行并自行定义脚本处理;

 1 if(OBJECT_ID('trigger_Cou_Update') is not null)        -- 判断名为 trigger_Cou_Update 的触发器是否存在
 2 drop trigger trigger_Cou_Update        -- 删除触发器
 3 go
 4 create trigger trigger_Cou_Update
 5 on Course        -- 指定创建触发器的表
 6 for update        -- update 触发器,也可以写为 after update
 7 as
 8 
 9 declare @C_Id    int
10 
11 select @C_Id=C_Id from deleted        
12 
13 select * from deleted        -- 修改前的数据就存在 deleted 这个表中
14 
15 select * from inserted        -- 修改后的数据就存在 inserted 这个表中
16 
17 update Student set C_S_Id=@C_Id where C_S_Id is null 
18 go
19 
20 update Course set C_Name='C#' where C_Id='4'
21 
22 select * from Student
23 select * from Course

金沙国际唯一官网网址 6

insert 触发器:

以此参数暗中同意值为1,
约等于说允许AFTE福睿斯触发器嵌套,最多嵌套3贰层,设为0便是差别意AFTETiguan触发器嵌套,如下:

启用/禁用触发器:

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

金沙国际唯一官网网址 7

  • INSTEAD OF触发器,能够嵌套,不受这些参数开关与否影响;
  • AFTE卡宴触发器,就算打开该接纳,也不会融洽嵌套本人(即递归),除非打开了RECURAV4SIVE_TPAJEROIGGE哈弗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判断并退出,会产出3二层限制的报错,并且表里不会插入任何数据
    /*
    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

加密 CREATE TOdysseyIGGEXC60 语句的文书。使用 WITH ENCCRUISERYPTION 能够预防触发器作为
SQL Server 复制的一片段开始展览公布。不可能为 CL福睿斯 触发器钦点 WITH ENC福特ExplorerYPTION。

金沙国际唯一官网网址 8

里面 DML 触发器相比较常用,依照 DML
触发器触发的措施各异又分为以下二种情状:

三. LOGON 触发器

DDL 触发器语法:

 

金沙国际唯一官网网址 9