必发365Oracle数据库之PL

背景

  上一篇中,作者介绍了SQL Server
允许访谈数据库的元数据,为啥有元数据,怎样运用元数据。这一篇中笔者会介绍如何进一步找到各样有价值的消息。以触发器为例,因为它们往往一齐相当多标题。

 

2. BEFORE/AFTER/INSTEAD OF

Oracle数据库之PL/SQL触发器

1. 介绍

触发器(trigger)是数据库提要求技师和数量深入分析员来保障数据完整性的一种情势,它是与表事件相关的不一致平时的积累进度,它的进行不是由程序调用,也不是手工业运维,而是由事件来触发,举个例子当对二个表实行操作(insert,delete,update)时就能激活它试行。触发器日常用来压实数据的完整性约束和业务准则等。

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

DML触发器

从名称想到所包含的意义,DML触发器是由DML语句触发的。比方数据库的INSERT、UPDATE、DELETE操作都能够触发该类型的触发器。它们得以在这一个讲话此前或未来触发,或然在行级上接触(正是说对于每一个受影响的行都触发贰回)。

替代它触发器

庖代触发器只好动用在视图上,与DML差别的是,DML触发器是运维在DML之外的,而顶替触发器是代表激发它的DML语句运营。代替触发器是行触发器。

系统触发器

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

触发器作用壮大,轻巧可相信地达成无数犬牙交错的作用,可是我们也理应慎用。为啥又要慎用呢?触发器本人未有过错,但尽管大家滥用,会产生数据库及应用程序的保证困难。在数据库操作中,大家得以经过关系、触发器、存款和储蓄进程、应用程序等来贯彻数量操作,相同的时间约束、缺省值也是保障数据完整性的要紧保险。假设大家对触发器过分的依赖性,势必影响数据库的构造,同临时间增添了保卫安全的复杂程度。

2. 触发器组成

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

  1. 接触事件:引起触发器被触发的平地风波。
  2. 接触时间:触发器是在触发事件时有产生以前(BEFORE)照旧今后(AFTE奇骏)触发,也正是触发事件和该触发器的操作顺序。
  3. 接触操作:触发器被触发之后的目标和企图,是触发器本人要做的事情。
  4. 接触对象:包括表、视图、形式、数据库。唯有在这个目的上发出了适合触发条件的触发事件,才会进行触发操作。
  5. 接触条件:由WHEN子句钦赐一个逻辑表达式。独有当该表明式的值为TRUE时,遭受触发事件才会自动实行触发器,使其实行触发操作。
  6. 触发频率:说明触发器钦赐义的动作被试行的功效。即语句级(STATEMENT)触发器和行级(ROW)触发器: 
    语句级(STATEMENT)触发器:是指当某触发事件产生时,该触发器只举行二次; 
    行级(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和AFTE卡宴提出触发器的触发时间分别为前触发和后触发情势,前触发是在实行触发事件在此之前接触当前所创立的触发器,后触发是在实施触发事件现在触发当前所创设的触发器。

REFERENCING子句表达有关称号,在行触发器的PL/SQL块和WHEN子句中得以接纳有关称号参照当前的新、旧列值,暗中认可的有关称号为OLD和NEW。触发器的PL/SQL块中央银行使相关称号时,必须在它们此前加冒号(:),但在WHEN子句中则无法加冒号。

NEW只在UPDATE、INSERT的DML触发器内可用,它富含了修改发生后被潜移默化行的值。

OLD只在UPDATE、DELETE的DML触发器内可用,它含有了修改发生前被耳熟能详行的值。

FOEscort EACH
ROW选项表达触发器为行触发器。行触发器和说话触发器的界别表今后:行触发器须要当一个DML语句操走影响数据库中的多行数据时,对于里边的每一种数据行,只要它们符合触发约束规范,均激活一遍触发器;而说话触发器将全部讲话操作作为触发事件,当它符合约束标准时,激活三次触发器。当省略FOR
EACH ROW 选项时,BEFORE和AFTESportage触发器为语句触发器,而INSTEAD
OF触发器则只好为行触发器。

WHEN子句表明触发约束原则。Condition为一个逻辑表达时,其中必须满含相关称号,而不能够包含查询语句,也不可能调用PL/SQL函数。WHEN子句钦命的触发约束规范只好用在BEFORE和AFTE奥德赛行触发器中,不能够用在INSTEAD
OF行触发器和任何项目标触发器中。

INSTEAD
OF选项(成立取代触发器)使ORACLE激活触发器,而不进行触发事件。只可以对视图和目的视图创立INSTEAD
OF触发器,而无法对表、方式和数据库创立INSTEAD OF触发器。

ddl_event:二个或几个DDL事件,事件间用O帕杰罗分开。

database_event:四个或四个数据库事件,事件间用OOdyssey分开。

身体力行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触发器是由数据库的INSERT、UPDATE、DELETE操作触发,该类触发器能够在上述讲话此前或今后施行,也足以每种受影响的行施行三回。

规范谓词:当在触发器中蕴藏多少个触发事件(INSERT、UPDATE、DELETE)的组成时,为了分别指向不一致的风浪进展分歧的拍卖,须求运用ORACLE提供的条件谓词:

  1. INSERTING:当触发事件是INSERT时,取值为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或AFTEHighlander选项。
  3. FO凯雷德 EACH ROW子句是可选的。
  4. 并不须要在针对一个表的视图上成立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;

 

触发器的难点

  触发器是立见功效的,可是因为它们在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触发器关联,稍后大家将掌握。

在SQL Server中,从概念来讲唯有AFTELacrosse/INSTEAD
OF触发器,在表上协理AFTE奥迪Q5触发器,在表/视图上扶助INSTEAD
OF触发器,对于BEFORE触发器的须要能够尝试通过INSEAD OF触发器来兑现;

触发器的多少长度?

多相当多据库职员不赞同冗长触发器的概念,但她们唯恐会发觉,根据定义的长度排序的触发器列表是商量数据库的一种有用艺术。

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

 

行吗,笔者大概太训斥了,不太喜欢太长的,不过逻辑有的时候候会十分短。事实上,前三名在小编眼里是不可靠的,固然自身接连侧向于尽大概少地接纳触发器。

 

触发器哪天触发事件?

让大家看一下那个触发器,DML触发器能够在享有别的时间发出后触发,然则能够在约束被拍卖前还要触发INSTEAD
OF触发动作。上边大家就来拜谒全数的接触的毕竟是AFTE大切诺基 依然INSTEAD OF
触发器,有事什么日子接触了触发器。

/* 列出触发器,无论它们是否启用,以及触发器事件。*/

SELECT

  convert(CHAR(25),name) AS triggerName,

  convert(CHAR(32),coalesce(object_schema_name(parent_ID)+'.'

    +object_name(parent_ID),'Database ('+db_name()+')')) AS TheParent,

       is_disabled,

       CASE WHEN is_instead_of_trigger=1 THEN 'INSTEAD OF ' ELSE 'AFTER ' END

       +Stuff (--get a list of events for each trigger

        (SELECT ', '+type_desc FROM sys.trigger_events te

           WHERE te.object_ID=sys.triggers.object_ID

         FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,2,'') AS events

 FROM sys.triggers;

结果如下:

triggerName               TheParent                        is_disabled events

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

ddlDatabaseTriggerLog     Database (AdventureWorks2012)    1           AFTER CREATE_TABLE, ALTER_TABLE, DROP_TABLE, CREATE_VIEW, ALTER_VIEW, DROP_VIEW, CREATE_INDEX, ALTER_INDEX, DROP_INDEX, CREATE_XML_INDEX, ALTER_FULLTEXT_INDEX, CREATE_FULLTEXT_INDEX, DROP_FULLTEXT_INDEX, CREATE_SPATIAL_INDEX, CREATE_STATISTICS, UPDATE_STAT

t_AB                      dbo.AB                           0           INSTEAD OF INSERT

dEmployee                 HumanResources.Employee          0           INSTEAD OF DELETE

iuPerson                  Person.Person                    0           AFTER INSERT, UPDATE

iPurchaseOrderDetail      Purchasing.PurchaseOrderDetail   0           AFTER INSERT

uPurchaseOrderDetail      Purchasing.PurchaseOrderDetail   0           AFTER UPDATE

uPurchaseOrderHeader      Purchasing.PurchaseOrderHeader   0           AFTER UPDATE

iduSalesOrderDetail       Sales.SalesOrderDetail           0           AFTER INSERT, UPDATE, DELETE

uSalesOrderHeader         Sales.SalesOrderHeader           0           AFTER UPDATE

dVendor                   Purchasing.Vendor                0           INSTEAD OF DELETE

iWorkOrder                Production.WorkOrder             0           AFTER INSERT

uWorkOrder                Production.WorkOrder             0           AFTER UPDATE

 

As you will notice, we used a FOR XML PATH(‘’)
trick
here to make a list of the events for each trigger to make it easier to
read. These events were pulled from the sys.trigger_events view using
a correlated subquery.

只顾到我们使用了FOR XML
PATH(‘’)来列出事件的每贰个触发器,更便于读取领悟。sys.trigger_events行使相关子查询来询问这个事件。

那正是说怎么着找到触发器的数额?

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

在SQL
Server中,从概念来讲独有语句级触发器,但借使有行级的逻辑要拍卖,有多个仅在触发器内立竿见影的表
(inserted, deleted),
寄放着受影响的行,能够从那多个表里收取特定的行并自行定义脚本管理;

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

自己想领会种种表有多少个触发器,並且什么情形下接触它们。上边我们列出了独具触发器的表以及各种事件的触发器数量。每种表可能视图对于触发器行为都有多个INSTEAD
OF 触发器,恐怕是UPDATE, DELETE, 恐怕 INSERT

。然而一个表能够有八个AFTESportage触发器行为。那一个将显得在上面包车型大巴查询中(排除视图):

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中的文档,查看里面包车型客车一个文书档案是还是不是有利于元数据查询,总是值得检查的。

代码示例2: 限定特定用户在特定期间限定登入、限制连接数

搜求触发器的代码

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; 

 

结果如图:

必发365 1

 

8个援引正在进行这些进程。大家在sys.SQL_modules中查找了富有的定义能够找到三个特定的字符串,这种格局极慢很暴力,不过它是平价的!

 

总结

  本文研究过触发器,並且你能意识到触发器,以及地下的难点。这里并从未指向有关触发器的询问提供二个完美的工具箱,因为自己只是利用触发器作为示范来呈今后询问系统视图时或然使用的一对本领。在大家学习了目录、列和参数之后,大家将回到触发器,并打听了编写制定访谈系统视图和information
schema视图的询问的有个别家常用途。表是元数据的比非常多下边包车型地铁根底。它们是二种档案的次序的靶子的父类,其余元数据如索引是表的属性。大家正在日渐地努力去发现装有有关表的新闻。期待上一期

ORACLE DML Trigger

BEFORE

AFTER

INSTEAD OF

TABLE

N/A

VIEW

N/A

N/A

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

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

 SELECT name, definition

FROM sys.server_SQL_modules m

  INNER JOIN sys.server_triggers t

ON t.object_ID=m.object_ID; 

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

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

触发器里有何样代码?

前日让大家透过检查触发器的源代码来认同那或多或少。.

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和Oracle中都是如此,触发器作为任何事情的一局地存在,但是并不调节总体育赛工作的交由/回滚,为保险数据一致性,事务逻辑由触发器外层的语句来决定。

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

  在大家利用sys.triggers的新闻以前,须要来重新三次,全体的数据库对象都设有于sys.objects中,在SQL
Server 中的对象包括以下:聚合的CLLX570函数,check
约束,SQL标量函数,CLLAND标量函数,CLTucson表值函数,SQL内联表值函数,内部表,SQL存款和储蓄进度,CL福睿斯存款和储蓄进度,布署指南,主键约束,老式准则,复制过滤程序,系统基础表,同义词,种类对象,服务队列,CLPAJERODML
触发器,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中的。今后就要带我们去承继找找那一个音信。

 

在有着指标中找找字符串

我想知道除了触发器之外是不是还会有别的对象调用那个进度?大家多少修改查询以搜寻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; 

查询结果如下图:

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

服务器本地,在SSMS中经过DAC登入

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

咱俩能够列出触发器在代码中引用的具备指标

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

 

Database PL/SQL Language Reference, Using Triggers

在数据库中列出触发器

那么怎么获取触发器列表?上边笔者在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

 

在意:若是LOGON触发器把全数人都锁在外部了如何做?

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

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

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

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来实在看一下,有何重视。

那时候,只可以通过DAC登陆SQL
Server去禁止使用LOGON触发器/修改逻辑以允许登入,DAC登入情势有长途和本土三种,远程登陆供给经过sp_configure
开启remote admin connections
,如果没有事先开启,那就只可以选用本地登陆情势:

触发器能够驾驭为由特定事件触发的蕴藏进程,
和积攒进程、函数同样,触发器也支撑CL传祺,最近SQL
Server共援助以下两种触发器:

 

1. 语句级触发器/行级触发器

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

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

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

  • 一大波导入操作,如:BULK INSERT, bcp/INSERT… SELECT * FROM
    OPENROWSET,都有FIRE_TRIGGERS/IGNORE_TLacrosseIGGE昂科拉S选项,能够安装是或不是接触触发器;
  • 导入导出向导/SSIS,假诺指标是表,也可能有FIRE_T奥德赛IGGEENVISIONS的设置选项;
  • 别的truncate操作也不会触发;

循环/递归触发器的前提正是嵌套触发器,独有同意嵌套了才得以递归(递归也正是嵌套并触及本身),递归有直接和间接三种意况:

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

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

Transact-SQL statements

Create Nested Triggers

必发365 3

代码示例1: 笔录全部login登入历史 (其实也能够经过改变login
auditing选项,来记录成功和破产的登入在errorlog里)

4.
触发器中不能够commit/rollback事务

一. DML触发器

 

  1. DML触发器, 表/视图级有效,可由DML语句 (INSERT, UPDATE, DELETE)
    触发;

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

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

注意:

  • INSTEAD OF触发器,能够嵌套,不受这几个参数开关与否影响;
  • AFTEPRADO触发器,就算展开该选择,也不会和谐嵌套自个儿(即递归),除非张开了RECUQashqaiSIVE_T奔驰G级IGGEMuranoS选项,也正是循环/递归触发器;

    –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

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

 

 

 

3. 接触条件

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

对此UPDATE,DELETE操作来讲,均会接触触发器;而对此INSERT或许说IMPORT的情形,是足以调整不去接触的。

必发365 4

三. LOGON 触发器

  1. TRUNCATE不在DDL触发器的事件类型中,SQL Server少将Truncate
    归为DML操作语句,即便它也并不触发DML触发器,就如展开开关的大批量导入操作
    (Bulk Import Operations) 同样;

嵌套触发器,就是二回操作触发了贰个触发器,然后触发器里的语句继续接触其余触发器,假如继续回头触发了团结,那么就是递归触发器。

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

参考:

(1) 无法接触的意况

其一参数私下认可值为1,
也正是说允许AFTE大切诺基触发器嵌套,最多嵌套32层,设为0正是不容许AFTEKuga触发器嵌套,如下:

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

但以此参数有七个别的:

exec sp_configure 'nested triggers',0
RECONFIGURE
--限制下班时间不能登录
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

2.
DDL触发器中捕获的新闻都由EVENTDATA()函数重回,再次来到类型为XML格式,须求用XQuery来读取;

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

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

  1. AFTELX570触发器,暗中同意Nest
    Triggers值为1,即允许触发器嵌套,上限32层,直接递归也是可以的,直接递归供给开启数据库选项RECULX570SIVE_TRIGGERS;

  2. INSTEAD OF触发器,不受Nest
    Triggers选项影响,均能够嵌套,上限32层,直接递归也是足以的,直接递归无论是或不是张开数据库选项RECUSIVE_TEnclaveIGGE中华VS,都行不通;把地点七个剧本示例中的AFTELX570改为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

  • 可以看到数据库选项RECUOdysseySIVE_TENVISIONIGGE奥迪Q7S,仅对一贯递归有效,对直接递归无效;可以透过Nest
    Triggers的开关来支配是不是允许嵌套,进而调整是否同意直接递归;

  • 随意直接递归,照旧直接递归,递归次数都有贰十五回嵌套的上限;

必发365 5

在ORACLE中,
对表做二回DML操作爆发一遍接触,叫语句级触发器,别的还足以经过点名[FOR
EACH
ROW]子句,对于表中受影响的每行数据均触发,叫行级触发器,原有行用:OLD表示,新行用:NEW代表;

 

 

对此AFTETiguan触发器有个五个按键分别调节嵌套触发和递归触发:

 

CREATE TRIGGER (Transact-SQL)

SQL Server DML Trigger

BEFORE

AFTER

INSTEAD OF

TABLE

N/A

VIEW

N/A

N/A

总计下来:

在ORACLE中,在表上帮衬BEFORE/AFTEPRADO触发器,在视图上支持INSTEAD
OF触发器,比方ORACLE中不能够直接对视图做DML操作,能够经过INSTEAD
OF触发器来变样完毕;

SQL Server
2005始发帮忙DDL触发器,它不只限于对CREATE/ALTE奥德赛/DROP操作可行,帮忙的DDL事件还会有诸如:权限的GRANT/DENY/REVOEK,
对象的RENAME, 更新总计新闻等等,可因此DMV查看越多协助的平地风波类型如下:

 

 

 

二. DDL触发器

select * from sys.server_triggers where name = 'login_history_trigger'
select * from sys.server_trigger_events
select OBJECT_ID('login_history_trigger') --无法获取
exec sp_configure 'nested 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)
    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.
*/

在SQL Server中,顾名思义,LOGON触发器,只扶助LOGON事件;

在ORACLE中,实例级触发器可帮助越多事件 (SE大切诺基VERE揽胜极光RORubicon, LOGON, LOGOFF,
STARTUP, or SHUTDOWN)。

相关文章

发表评论

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

*
*
Website