Oracle数据库之PL/SQL触发器

Oracle数据库之PL/SQL触发器

背景

  上一篇中,笔者介绍了SQL Server
允许访问数据库的元数据,为何有元数据,怎样行使元数据。这一篇中笔者会介绍怎样越发找到种种有价值的信息。以触发器为例,因为它们往往一起很多题材。

 

触发器能够领略为由特定事件触发的仓库储存进程,
和存款和储蓄进程、函数一样,触发器也援助CLPRADO,近年来SQL
Server共扶助以下三种触发器:

Oracle数据库之PL/SQL触发器

1. 介绍

触发器(trigger)是数据库提供给程序员和数目分析员来保障数据完整性的一种方法,它是与表事件相关的特种的积存进度,它的履行不是由程序调用,也不是手工业运营,而是由事件来触发,比如当对1个表进行操作(insert,delete,update)时就会激活它实施。触发器日常用来进步数据的完整性约束和事务规则等。

Oracle触发器有三种类型,分别是:DML触发器、替代触发器和种类触发器。

DML触发器

顾名思义,DML触发器是由DML语句触发的。例如数据库的INSERAV4T、UPDATE、DELETE操作都能够触发该项目标触发器。它们可以在这一个讲话从前或之后触发,或然在行级上接触(正是说对于各样受影响的行都触发二遍)。

取而代之触发器

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

系统触发器

那种触发器是发生在如数据库运维或关闭等系统事件时,不是在实施DML语句时发出,当然也足以在DDL时接触。

触发器作用强大,轻松可靠地贯彻无数错综复杂的作用,不过大家也相应慎用。为何又要慎用呢?触发器自身并未过错,但万一我们滥用,会导致数据库及应用程序的保证困难。在数据库操作中,大家得以经过关系、触发器、存储进度、应用程序等来贯彻多少操作,同时约束、缺省值也是保险数据完整性的首要性保障。若是我们对触发器过分的依靠,势必影响数据库的构造,同时扩展了保证的复杂程度。

2. 触发器组成

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

  1. 接触事件:引起触发器被触发的风云。
  2. 接触时间:触发器是在触及事件时有产生在此以前(BEFORE)依然后来(AFTEEvoque)触发,也正是接触事件和该触发器的操作顺序。
  3. 接触操作:触发器被触发之后的指标和企图,是触发器本人要做的事情。
  4. 接触对象:包涵表、视图、形式、数据库。唯有在这么些目的上发生了符合触发条件的触发事件,才会实施触发操作。
  5. 接触条件:由WHEN子句钦命2个逻辑表明式。唯有当该表达式的值为TRUE时,遭逢触发事件才会自动执行触发器,使其推行触发操作。
  6. 触发频率:表明触发器内定义的动作被执行的频率。即语句级(STATEMENT)触发器和行级(ROW)触发器: 
    语句级(STATEMENT)触发器:是指当某触发事件发生时,该触发器只进行3回; 
    行级(ROW)触发器:是指当某触发事件时有发生时,对遭到该操作影响的每一行数据,触发器都单身实施二回。

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和AFTESportage建议触发器的接触时间独家为前触发和后触发方式,前触发是在实施触发事件在此之前接触当前所创制的触发器,后触发是在进行触发事件今后触发当前所创造的触发器。

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

NEW只在UPDATE、INSERAV4T的DML触发器内可用,它包罗了改动产生后被潜移默化行的值。

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

FO兰德酷路泽 EACH
ROW选项表明触发器为行触发器。行触发器和言语触发器的分别表未来:行触发器供给当1个DML语句操走影响数据库中的多行数据时,对于内部的各类数据行,只要它们符合触发约束原则,均激活二回触发器;而说话触发器将整个讲话操作作为触发事件,当它适合约束原则时,激活三回触发器。当省略FOR
EACH ROW 选项时,BEFORE和AFTER触发器为语句触发器,而INSTEAD
OF触发器则不得不为行触发器。

WHEN子句表达触发约束规范。Condition为三个逻辑表达时,在那之中必须包罗相关称号,而不能够包罗查询语句,也不能调用PL/SQL函数。WHEN子句钦定的触发约束规范只可以用在BEFORE和AFTEKuga行触发器中,不可能用在INSTEAD
OF行触发器和别的类型的触发器中。

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

ddl_event:三个或四个DDL事件,事件间用O酷路泽分开。

database_event:3个或多少个数据库事件,事件间用O大切诺基分开。

演示1,在插入数据时,自动使用系列编号:

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;

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

-- 创建视图
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;

示例3,创立实例运转触发器:

-- 创建记录操作事件的表
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触发器是由数据库的INSE昂CoraT、UPDATE、DELETE操作触发,该类触发器可以在上述讲话以前或现在执行,也足以每一个受影响的行执行二回。

标准谓词:当在触发器中蕴藏多少个触发事件(INSE福特ExplorerT、UPDATE、DELETE)的构成时,为了分别指向分裂的事件开始展览不一样的拍卖,必要使用ORACLE提供的尺度谓词:

  1. INSE库罗德TING:当触发事件是INSEENVISIONT时,取值为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或AFTE奥迪Q7选项。
  3. FO福睿斯 EACH ROW子句是可选的。
  4. 未曾须求在针对3个表的视图上开创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;

 

触发器简介:

那么如何找到触发器的多少?

*  以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’来寻找你想要的视图名称。.

在二零一二及其今后版本,能够使用3个新的表值函数非常的大地简化上述查询,并得以幸免种种连接。在上边包车型大巴查询中,我们将寻找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语句 (INSE大切诺基T, UPDATE, DELETE)
    触发;

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

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

触发器是一种特有的贮存过程,它的履行不是由程序调用,也不是手动执行,而是由事件来触发。触发器是当对某多个表实行操作。例如:update、insert、delete这几个操作的时候,系统会自动调用执行该表上相应的触发器。

但是当然三个触发器是第1是3个对象,因而一定在sys.objects?

  在我们选取sys.triggers的音信此前,要求来再度一遍,全体的数据库对象都设有于sys.objects中,在SQL
Server 中的对象包含以下:聚合的CL福睿斯函数,check
约束,SQL标量函数,CLPAJERO标量函数,CL福特Explorer表值函数,SQL内联表值函数,内部表,SQL存款和储蓄进程,CL奥迪Q5存款和储蓄进度,安插指南,主键约束,老式规则,复制过滤程序,系统基础表,同义词,连串对象,服务队列,CLXC60DML
触发器,SQL表值函数,表类型,用户自定义表,唯一约束,视图和扩张存款和储蓄进程等。

  触发器是目的所以基础音讯一定保存在sys.objects。不幸运的是,有时大家须求格外的新闻,那一个新闻方可经过目录视图查询。那一个额外数据有是何等吧?

 

  修改我们选拔过的询问,来查询sys.triggers的列,此次我们晤面到额外消息。那一个额外列是来源于于sys.objects。

 SELECT coalesce(trigger_column.name,'NOT INCLUDED') AS In_Sys_Triggers,

       coalesce(object_column.name,'NOT INCLUDED') AS In_Sys_Objects

FROM

 (SELECT Thecol.name

  FROM sys.system_views AS TheView

    INNER JOIN sys.system_columns AS TheCol

      ON TheView.object_ID=TheCol.Object_ID

  WHERE  TheView.name = 'triggers') trigger_column

FULL OUTER JOIN

 (SELECT Thecol.name

  FROM sys.system_views AS TheView

    INNER JOIN sys.system_columns AS TheCol

      ON TheView.object_ID=TheCol.Object_ID

  WHERE  TheView.name = 'objects') object_column

ON trigger_column.name=object_column.name

查询结果:

In_Sys_Triggers                In_Sys_Objects

------------------------------ ----------------------

name                           name

object_id                      object_id

NOT INCLUDED                   principal_id

NOT INCLUDED                   schema_id

NOT INCLUDED                   parent_object_id

type                           type

type_desc                      type_desc

create_date                    create_date

modify_date                    modify_date

is_ms_shipped                  is_ms_shipped

NOT INCLUDED                   is_published

NOT INCLUDED                   is_schema_published

is_not_for_replication         NOT INCLUDED

is_instead_of_trigger          NOT INCLUDED

parent_id                      NOT INCLUDED

is_disabled                    NOT INCLUDED

parent_class                   NOT INCLUDED

parent_class_desc              NOT INCLUDED

 

以上这几个让大家知道在sys.triggers的额外音讯,然而因为它始终是表的子对象,所以某些不相干音讯是不会显示在这个内定的视图也许sys.triggers中的。今后即将带大家去继续找找那一个音讯。

 

触发器分类:

触发器的标题

  触发器是立见功效的,可是因为它们在SSMS对象能源管理器窗格中不是可知的,所以一般用来提示错误。触发器有时候会略带微妙的地点让其出问题,比如,当导入进程中禁用了触发器,并且由于有个别原因他们平素不重启。

下边是一个有关触发器的简易提示:

  触发器能够在视图,表大概服务器上,任何那个目标上都能够有超过贰个触发器。普通的DML触发器能被定义来推行替代一些数码修改(Insert,Update大概Delete)只怕在多少修改之后执行。每1个触发器与只与七个对象管理。DDL触发器与数据库关联大概被定义在服务器级别,这类触发器一般在Create,Alter可能Drop那类SQL语句执行后触发。

  像DML触发器一样,能够有多个DDL触发器被成立在同多个T-SQL语句上。三个DDL触发器和讲话触发它的话语在同贰个事情中运转,所以除了Alter
DATABASE之外都足以被回滚。DDL触发器运维在T-SQL语句执行实现后,也便是不能够同日而语Instead
OF触发器使用。

  三种触发器都与事件有关,在DML触发器中,包涵INSECR-VT, UPDATE,
和DELETE,可是无数风浪都足以与DDL触发器关联,稍后大家将理解。

一. DML触发器

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

在数据库中列出触发器

那么怎么获取触发器列表?下面作者在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. 语句级触发器/行级触发器

贰 、DDL(数据定义语言 Data Definition
Language)触发器:是指当服务器或数据库中发出 DDL
事件时将启用。DDL事件是指在表或索引中的 create、alter、drop 操作语句。

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

本身想知道各种表有多少个触发器,并且什么情状下接触它们。上边大家列出了颇具触发器的表以及各类事件的触发器数量。每一个表或然视图对于触发器行为都有三个INSTEAD
OF 触发器,或然是UPDATE, DELETE, 或然 INSE揽胜T

。可是1个表可以有多少个AFTE中华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)

若是超越2个触发器被触发在三个表上,它们不有限接济顺序,当然也得以运用sp_settriggerorder来决定顺序。通过采用objectpropertyex()元数据函数,要求基于事件输入参数‘ExecIsLastDeleteTrigger’,
‘ExecIsLastInsertTrigger’ 或许‘ExecIsLastUpdateTrigger’来认同何人是最后多少个推行的触发器
。为了博取第②个触发器,酌情采纳ObjectPropertyEx()
元数据函数,供给输入参数 ‘ExecIsFirstDeleteTrigger’,
‘ExecIsFirstInsertTrigger’ 或然 ‘ExecIsFirstUpdateTrigger’。

为此大家以往知晓了表有如何触发器,哪些事件触发那几个触发器。能够使用objectpropertyex()元数据函数,那一个函数重临很多见仁见智音讯,依照钦点的参数区别。通过翻看MSDN中的文书档案,查看里面包车型大巴三个文书档案是还是不是有助于元数据查询,总是值得检查的。

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

三 、登陆触发器:是指当用户登录 SQL SELacrosseVE瑞鹰实例建立会话时接触。如若身份验证失利,登录触发器不会触发。

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

让大家看一下那一个触发器,DML触发器能够在拥有别的时间发出后触发,不过足以在封锁被拍卖前还要触发INSTEAD
OF触发动作。上面大家就来看望全部的接触的到底是AFTE奔驰M级 照旧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表示;

其间 DML 触发器相比较常用,依据 DML
触发器触发的点子各异又分为以下三种情况:

触发器的多少长度?

无数数据库职员不赞成冗长触发器的概念,但她们唯恐会意识,依照定义的长度排序的触发器列表是研讨数据库的一种有用艺术。

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

 

行吗,小编也许太挑剔了,不太喜欢太长的,但是逻辑有时候会相当长。事实上,前三名以作者之见是离谱赖的,即使小编接连倾向于尽或许少地选用触发器。

 

after 触发器(之后触发):个中 after 触发器须要只有执行
insert、update、delete 某一操作之后触发器才会被触发,且不得不定义在表上。

这几个触发器访问了略微对象

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

小编们只供给检讨表达式依赖项。那个查询利用一个视图来列出“软”重视项(如触发器、视图和函数)。

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

2. BEFORE/AFTER/INSTEAD OF

instead of 触发器 (从前接触):instead of
触发器并不实施其定义的操作(insert、update、delete)而仅是实施触发器本人。能够在表或视图上定义
instead of 触发器。

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

大家能够列出触发器在代码中援引的保有指标

SELECT

  convert(char(32),name) as TheTrigger,

  convert(char(32),coalesce([referenced_server_name]+'.','')

            +coalesce([referenced_database_name]+'.','')

       +coalesce([referenced_schema_name]+'.','')+[referenced_entity_name])
     as referencedObject

FROM sys.triggers

INNER JOIN sys.SQL_Expression_dependencies

ON [referencing_id]=object_ID

WHERE name LIKE 'iduSalesOrderDetail';

--查询结果:

TheTrigger                       referencedObject

-------------------------------- --------------------------------

iduSalesOrderDetail              Sales.Customer                 

iduSalesOrderDetail              Person.Person                  

iduSalesOrderDetail              Sales.SalesOrderDetail         

iduSalesOrderDetail              Sales.SalesOrderHeader          

iduSalesOrderDetail              Production.TransactionHistory  

iduSalesOrderDetail              dbo.uspLogError                

iduSalesOrderDetail              dbo.uspPrintError

 

在SQL Server中,从概念来说惟有AFTE汉兰达/INSTEAD
OF触发器,在表上援助AFTE奥迪Q5触发器,在表/视图上帮衬INSTEAD
OF触发器,对于BEFORE触发器的急需能够尝尝通过INSEAD OF触发器来促成;

DML
触发器有七个新鲜的表:插入表(instered)和删除表(deleted),那两张表是逻辑表。那四个表是起家在数据库服务器的内部存款和储蓄器中,而且两张表的都以只读的。那两张表的组织和触发器所在的数据表的布局是平等的。当触发器完毕工作后,那两张表就会被删除。Inserted
表的数码是插入或是修改后的数码,而 deleted
表的数额是翻新前的只怕已删除的多少。

触发器里有啥样代码?

当今让我们经过检查触发器的源代码来确认这点。.

SELECT OBJECT_DEFINITION ( object_id('sales.iduSalesOrderDetail') ); 

我们在此之前的询问是毋庸置疑的,扫描源码可见全部的依赖项。大量凭借项表名对于数据库的重构等急需卓绝小心,例如,修改3个基础表的列。

据供给做什么样,您大概希望检查来自元数据视图的概念,而不是应用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

图片 1

追寻触发器的代码

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

 

7个引用正在实行这些进度。大家在sys.SQL_modules中检索了具备的定义能够找到二个特定的字符串,那种办法异常慢很暴力,然而它是立竿见影的!

在ORACLE中,在表上帮衬BEFORE/AFTEPRADO触发器,在视图上支持INSTEAD
OF触发器,比如ORACLE中不或然直接对视图做DML操作,可以经过INSTEAD
OF触发器来变样完结;

AFTE奥德赛 触发器语法:

在装有目的中摸索字符串

本身想精通除了触发器之外是还是不是还有其他对象调用这一个历程?我们有个别修改查询以搜寻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; 

询问结果如下图:

图片 3

 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行往下)

ORACLE DML Trigger

BEFORE

AFTER

INSTEAD OF

TABLE

N/A

VIEW

N/A

N/A

 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 ]

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

大家得以透过系统视图领悟它们啊?嗯,是的。以下是列出服务器触发器及其定义的话语

 SELECT name, definition

FROM sys.server_SQL_modules m

  INNER JOIN sys.server_triggers t

ON t.object_ID=m.object_ID; 

专注,只好见到有权力看的触发器

 

INSTEAD OF 触发器语法:

总结

  本文探究过触发器,并且你能搜查捕获触发器,以及地下的题材。那里并不曾指向有关触发器的询问提供多少个宏观的工具箱,因为自个儿只是使用触发器作为示范来突显在查询系统视图时可能使用的部分技术。在咱们上学了目录、列和参数之后,我们将赶回触发器,并问询了编辑访问系统视图和information
schema视图的询问的一部分普通用途。表是元数据的浩大上边的功底。它们是几体系型的靶子的父类,其余元数据如索引是表的天性。我们正在逐步地拼命去发现装有关于表的新闻。期待下期

3. 接触条件

 1 CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name   
 2 ON { table | view }   
 3 [ WITH <dml_trigger_option> [ ,...n ] ]  
 4 { FOR | AFTER | INSTEAD OF }   
 5 { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }   
 6 [ WITH APPEND ]  
 7 [ NOT FOR REPLICATION ]   
 8 AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME <method specifier [ ; ] > }  
 9 
10 <dml_trigger_option> ::=  
11     [ ENCRYPTION ]  
12     [ EXECUTE AS Clause ]  
13 
14 <method_specifier> ::=  
15     assembly_name.class_name.method_name  

(1) 不可能接触的意况

DDL 触发器语法:

对于UPDATE,DELETE操作而言,均会触发触发器;而对此INSE途胜T也许说IMPOWranglerT的景况,是能够操纵不去接触的。

1 CREATE [ OR ALTER ] TRIGGER trigger_name   
2 ON { ALL SERVER | DATABASE }   
3 [ WITH <ddl_trigger_option> [ ,...n ] ]  
4 { FOR | AFTER } { event_type | event_group } [ ,...n ]  
5 AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier >  [ ; ] }  
6 
7 <ddl_trigger_option> ::=  
8     [ ENCRYPTION ]  
9     [ EXECUTE AS Clause ]  
  • 大量导入操作,如:BULK INSE牧马人T, bcp/INSE帕杰罗T… SELECT * FROM
    OPENROWSET,都有FIRE_TRIGGERS/IGNORE_T库罗德IGGE凯雷德S选项,能够安装是还是不是接触触发器;
  • 导入导出向导/SSIS,假诺目的是表,也有FIRE_TLANDIGGEQashqaiS的装置选项;
  • 其余truncate操作也不会触发;

登陆触发器语法:

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

1 CREATE [ OR ALTER ] TRIGGER trigger_name   
2 ON ALL SERVER   
3 [ WITH <logon_trigger_option> [ ,...n ] ]  
4 { FOR| AFTER } LOGON    
5 AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier >  [ ; ] }  
6 
7 <logon_trigger_option> ::=  
8     [ ENCRYPTION ]  
9     [ EXECUTE AS Clause ]  

嵌套触发器,就是3遍操作触发了三个触发器,然后触发器里的讲话继续接触别的触发器,假诺持续回头触发了协调,那么正是递归触发器。

参数:

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

CREATE OR ALTER:

exec sp_configure 'nested triggers'

成立可能有标准的修改触发器(即要修改的触发器必须已经存在)。

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图