开创触发器,Oracle数据库之PL

背景

  上一篇中,作者介绍了SQL Server
允许访谈数据库的元数据,为何有元数据,怎么着行使元数据。这一篇中作者会介绍怎样越发找到各类有价值的音信。以触发器为例,因为它们往往一齐非常多难题。

 

触发器能够知道为由特定事件触发的积累进度,
和存款和储蓄进度、函数同样,触发器也支撑CL奥迪Q3,方今SQL
Server共扶助以下二种触发器:

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奥迪Q7)触发,相当于接触事件和该触发器的操作顺序。
  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

开创触发器,Oracle数据库之PL。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奥迪Q3建议触发器的接触时间独家为前触发和后触发格局,前触发是在实施触发事件在此以前接触当前所创制的触发器,后触发是在试行触发事件未来触发当前所开创的触发器。

REFERENCING子句表明有关称号,在行触发器的PL/SQL块和WHEN子句中能够利用有关称号参照当前的新、旧列值,默许的有关称号为OLD和NEW。触发器的PL/SQL块中央银行使相关称号时,必须在它们从前加冒号(:),但在WHEN子句中则不可能加冒号。

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

OLD只在UPDATE、DELETE的DML触发器内可用,它富含了修改发生前被影响行的值。

FO奥迪Q5 EACH
ROW选项表明触发器为行触发器。行触发器和讲话触发器的差别表现在:行触发器供给当二个DML语句操走影响数据库中的多行数据时,对于内部的每一种数据行,只要它们符合触发约束标准,均激活三遍触发器;而讲话触发器将整个讲话操作作为触发事件,当它符合约束原则时,激活三遍触发器。当省略FOR
EACH ROW 选项时,BEFORE和AFTEEvoque触发器为语句触发器,而INSTEAD
OF触发器则不得不为行触发器。

WHEN子句表明触发约束原则。Condition为三个逻辑表明时,在这之中必须包括相关称号,而不可能包括查询语句,也无法调用PL/SQL函数。WHEN子句钦命的触发约束规范只好用在BEFORE和AFTEENCORE行触发器中,无法用在INSTEAD
OF行触发器和其他类型的触发器中。

INSTEAD
OF选项(创造取代触发器)使ORACLE激活触发器,而不实行触发事件。只好对视图和指标视图创设INSTEAD
OF触发器,而不能够对表、方式和数据库建构INSTEAD OF触发器。

ddl_event:一个或多少个DDL事件,事件间用O大切诺基分开。

database_event:一个或多个数据库事件,事件间用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触发器是由数据库的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)而成,由此不用全体的视图都以可更新的,但能够遵照所需的不二等秘书诀实施更新。

开创触发器,Oracle数据库之PL。创建INSTEAD OF触发器供给当心以下几点:

  1. 唯其如此被创立在视图上,而且该视图未有一些名WITH CHECK OPTION选项。
  2. 无法钦命BEFORE或AFTE索罗德选项。
  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;

 

触发器简要介绍:

那么怎么样找到触发器的多寡?

*  以sys.system_views*is表开端。让我们询问出数据库中使用触发器的音讯。能够告知您眼下SQL
Server版本中有何样触发器。

SELECT schema_name(schema_ID)+'.'+ name

  FROM sys.system_views WHERE name LIKE '%trigger%'

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

sys.dm_exec_trigger_stats              

sys.server_trigger_events              

sys.server_triggers                    

sys.trigger_event_types                

sys.trigger_events                     

sys.triggers                           



(6 row(s) affected)

  当中sys.triggers看起来音讯非常多,它又满含哪些列?上边那个查询很轻易查到:

 SELECT Thecol.name+ ' '+ Type_name(TheCol.system_type_id)

  + CASE WHEN TheCol.is_nullable=1 THEN ' NULL' ELSE ' NOT NULL' END as Column_Information

FROM sys.system_views AS TheView

  INNER JOIN sys.system_columns AS TheCol

    ON TheView.object_ID=TheCol.Object_ID

  WHERE  TheView.name = 'triggers'

  ORDER BY column_ID;

结果如下:

 Column_Information

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

name nvarchar NOT NULL

object_id int NOT NULL

parent_class tinyint NOT NULL

parent_class_desc nvarchar NULL

parent_id int NOT NULL

type char NOT NULL

type_desc nvarchar NULL

create_date datetime NOT NULL

modify_date datetime NOT NULL

is_ms_shipped bit NOT NULL

is_disabled bit NOT NULL

is_not_for_replication bit NOT NULL

is_instead_of_trigger bit NOT NULL

 

据此大家多这一个新闻有了更加好的精通,有了二个索引的目录。那么些概念有一点点令人头晕,可是另一方面,它也是一定轻巧的。大家可以意识到元数据,再找个查询中,必要做的便是更改这几个单词‘triggers’来搜寻你想要的视图名称。.

在二零一二会同今后版本,能够使用叁个新的表值函数十分大地简化上述查询,并能够制止各类连接。在上面的询问中,大家将追寻sys.triggers
视图

中的列。能够采用同样的询问通过改变字符串中的对象名称来收获其他视图的概念。

 SELECT name+ ' '+ system_type_name

  + CASE WHEN is_nullable=1 THEN ' NULL' ELSE ' NOT NULL' END as Column_Information

FROM sys.dm_exec_describe_first_result_set

  ( N'SELECT * FROM sys.triggers;', NULL, 0) AS f

  ORDER BY column_ordinal;

询问结果如下:

 Column_Information

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

name nvarchar(128) NOT NULL

object_id int NOT NULL

parent_class tinyint NOT NULL

parent_class_desc nvarchar(60) NULL

parent_id int NOT NULL

type char(2) NOT NULL

type_desc nvarchar(60) NULL

create_date datetime NOT NULL

modify_date datetime NOT NULL

is_ms_shipped bit NOT NULL

is_disabled bit NOT NULL

is_not_for_replication bit NOT NULL

is_instead_of_trigger bit NOT NULL

 

sys.dm_exec_describe_first_result_set函数的最大优势在于你能来看别的结果的列,不止是表和视图、存款和储蓄进度或然贬值函数。

为了摸清任何列的新闻,你能够行使稍微修改的本子,只必要改变代码中的字符串’sys.triggers’就可以,如下:

 Declare @TheParamater nvarchar(255)

Select @TheParamater = 'sys.triggers'

Select @TheParamater = 'SELECT * FROM ' + @TheParamater

SELECT

  name+ ' '+ system_type_name

  + CASE WHEN is_nullable=1 THEN ' NULL' ELSE ' NOT NULL' END as Column_Information

FROM sys.dm_exec_describe_first_result_set

  ( @TheParamater, NULL, 0) AS f

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

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

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

触发器是一种特有的储存进度,它的试行不是由程序调用,亦不是手动推行,而是由事件来触发。触发器是当对某一个表张开操作。举例:update、insert、delete那几个操作的时候,系统会活动调用试行该表上相应的触发器。

然则当然一个触发器是第一是八个对象,因而一定在sys.objects?

  在大家利用sys.triggers的新闻在此以前,须求来重新三次,全部的数据库对象都存在于sys.objects中,在SQL
Server 中的对象包罗以下:聚合的CL福特Explorer函数,check
约束,SQL标量函数,CL宝马7系标量函数,CL陆风X8表值函数,SQL内联表值函数,内部表,SQL存款和储蓄进度,CL福特Explorer存款和储蓄进度,布置指南,主键约束,老式准绳,复制过滤程序,系统基础表,同义词,系列对象,服务队列,CLENCOREDML
触发器,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对象能源管理器窗格中不是可知的,所以一般用来提示错误。触发器一时候会稍稍微妙的地方让其出难点,譬如,当导入进程中禁止使用了触发器,並且由于有个别原因他们一贯不重启。

上边是三个有关触发器的粗略提示:

  触发器能够在视图,表或然服务器上,任何这个指标上都足以有超过1个触发器。普通的DML触发器能被定义来试行代替一些数目修改(Insert,Update大概Delete)大概在数据修改之后奉行。每二个触发器与只与四个对象管理。DDL触发器与数据库关联或许被定义在服务器品级,那类触发器一般在Create,Alter也许Drop这类SQL语句实践后触发。

  像DML触发器同样,能够有多少个DDL触发器被创设在同三个T-SQL语句上。多个DDL触发器和说话触发它的口舌在同一个事务中运作,所以除了Alter
DATABASE之外都得以被回滚。DDL触发器运维在T-SQL语句实践达成后,也正是不能够作为Instead
OF触发器使用。

  二种触发器都与事件有关,在DML触发器中,满含INSERT, UPDATE,
和DELETE,可是无数事件都得以与DDL触发器关联,稍后我们将领悟。

一. DML触发器

1、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. 语句级触发器/行级触发器

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

本人的表和视图有多少个触发器?

本身想掌握各样表有多少个触发器,並且什么状态下接触它们。下边大家列出了装有触发器的表以及各类事件的触发器数量。每一种表也许视图对于触发器行为皆有二个INSTEAD
OF 触发器,或然是UPDATE, DELETE, 或者 INSERT

。然而二个表可以有七个AFTERAV4触发器行为。那个将呈今后上面包车型地铁询问中(排除视图):

SELECT

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

    +object_name(parent_ID),'Database ('+db_name()+')')) AS 'Table', triggers,[KD1] [AC2] 

convert(SMALLINT,objectpropertyex(parent_ID, N'TABLEDeleteTriggerCount')) AS 'Delete',

convert(SMALLINT,objectpropertyex(parent_ID, N'TABLEInsertTriggerCount')) AS 'Insert',

convert(SMALLINT,objectpropertyex(parent_ID, N'TABLEUpdateTriggerCount')) AS 'Update'

FROM (SELECT count(*) AS triggers, parent_ID FROM sys.triggers

      WHERE objectpropertyex(parent_ID, N'IsTable') =1

         GROUP BY parent_ID

          )TablesOnly;

--查询结果如下:

Table                            triggers    Delete Insert Update

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

Purchasing.Vendor                1           0      0      0

Production.WorkOrder             2           0      1      1

Purchasing.PurchaseOrderDetail   2           0      1      1

Purchasing.PurchaseOrderHeader   1           0      0      1

Sales.SalesOrderDetail           1           1      1      1

HumanResources.Employee          1           0      0      0

Sales.SalesOrderHeader           1           0      0      1

Person.Person                    1           0      1      1



(8 row(s) affected)

比如超过二个触发器被触发在贰个表上,它们不有限帮忙顺序,当然也得以动用sp_settriggerorder来决定顺序。通过使用objectpropertyex()元数据函数,须要基于事件输入参数‘ExecIsLastDeleteTrigger’,
‘ExecIsLastInsertTrigger’ 或者‘ExecIsLastUpdateTrigger’来承认何人是最后二个施行的触发器
。为了拿走第多少个触发器,酌情采取ObjectPropertyEx()
元数据函数,必要输入参数 ‘ExecIsFirstDeleteTrigger’,
‘ExecIsFirstInsertTrigger’ 只怕 ‘ExecIsFirstUpdateTrigger’。

就此大家明日知晓了表有何样触发器,哪些事件触发那个触发器。能够使用objectpropertyex()元数据函数,这些函数重回相当多见仁见智音讯,依据钦命的参数差异。通过翻看MSDN中的文书档案,查看里面包车型地铁一个文书档案是不是有利于元数据查询,总是值得检查的。

在SQL
Server中,从概念来讲独有语句级触发器,但万一有行级的逻辑要拍卖,有七个仅在触发器内有效的表
(inserted, deleted),
贮存着受影响的行,能够从这五个表里收取特定的行并自行定义脚本管理;

3、登录触发器:是指当用户登陆 SQL SECRUISERVE奥德赛实例建构会话时接触。假诺身份验证失败,登陆触发器不会接触。

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

让大家看一下那么些触发器,DML触发器能够在富有其余时间产生后触发,可是足以在封锁被管理前并且触发INSTEAD
OF触发动作。上面大家就来拜会全体的接触的究竟是AFTEQX56 照旧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

 

居然有四个触发器有7个依附!让大家就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瑞虎触发器,在表/视图上协理INSTEAD
OF触发器,对于BEFORE触发器的须要可以品味通过INSEAD OF触发器来促成;

DML
触发器有几个特殊的表:插入表(instered)和删除表(deleted),这两张表是逻辑表。那七个表是确立在数据库服务器的内存中,何况两张表的都以只读的。这两张表的构造和触发器所在的数据表的布局是平等的。当触发器完毕职业后,这两张表就能够被剔除。Inserted
表的数量是插入或是修改后的数目,而 deleted
表的数目是翻新前的也许已删除的数码。

触发器里有啥代码?

于今让我们因而检查触发器的源代码来认同那或多或少。.

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

咱俩前边的询问是没有错的,扫描源码可见全体的依附项。大批量依附项表名对于数据库的重构等急需相当小心,比方,修改二个基础表的列。

据须求做什么,您大概希望检查来自元数据视图的概念,并不是使用OBJECT_DEFINITION函数。

 SELECT definition

FROM sys.SQL_modules m

  INNER JOIN sys.triggers t

    ON t.object_ID=m.object_ID

WHERE t.object_ID=object_id('sales.iduSalesOrderDetail');

SQL Server DML Trigger

BEFORE

AFTER

INSTEAD OF

TABLE

N/A

VIEW

N/A

N/A

计算机数据库 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

 

8个援用正在执行这一个进程。大家在sys.SQL_modules中探求了有着的定义能够找到二个一定的字符串,这种方法相当慢很暴力,但是它是有效的!

在ORACLE中,在表上帮助BEFORE/AFTE汉兰达触发器,在视图上援救INSTEAD
OF触发器,譬如ORACLE中不也许间接对视图做DML操作,能够通过INSTEAD
OF触发器来变样完毕;

AFTEEscort 触发器语法:

在全数指标中查找字符串

自己想领悟除了触发器之外是不是还应该有其余对象调用这些历程?大家有一点点修改查询以搜索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进程。(见第一列,第二行往下)

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操作来讲,均会接触触发器;而对此INSERT或然说IMPORT的意况,是足以调整不去接触的。

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 INSERT, bcp/INSERT… SELECT * FROM
    OPENROWSET,都有FIRE_TRIGGERS/IGNORE_T奇骏IGGE大切诺基S选项,能够设置是否接触触发器;
  • 导入导出向导/SSIS,假设指标是表,也许有FIRE_TCR-VIGGEPAJEROS的安装选项;
  • 除此以外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 ]  

嵌套触发器,正是一次操作触发了二个触发器,然后触发器里的讲话继续接触其余触发器,即使持续回头触发了和睦,那么便是递归触发器。

参数:

对于AFTEKuga触发器有个八个按钮分别调节嵌套触发和递归触发:

CREATE OR ALTER:

exec sp_configure 'nested triggers'

创建只怕有原则的修改触发器(即要修改的触发器必须已经存在)。

以此参数暗中同意值为1,
约等于说允许AFTE本田CR-V触发器嵌套,最多嵌套32层,设为0就是分歧意AFTE昂Cora触发器嵌套,如下:

schema_name:
DML触发器所属的形式的称号(即全数者,举例:dbo)。

exec sp_configure 'nested triggers',0
RECONFIGURE

trigger_name:
是触发器的称谓。

但以此参数有七个别的:

table | view:

  • INSTEAD OF触发器,能够嵌套,不受这些参数开关与否影响;
  • AFTE奥迪Q5触发器,尽管张开该选用,也不会友善嵌套自身(即递归),除非张开了RECU讴歌ZDXSIVE_T纳瓦拉IGGE冠道S选项,也正是循环/递归触发器;

    –create table, sql server 2016 & higher
    drop table if exists A
    GO
    create table A(id int)
    GO

    –create DML trigger
    drop trigger if exists tri_01
    GO
    create TRIGGER tri_01
    ON A
    AFTER INSERT, UPDATE, DELETE
    as
    begin

    if @@NESTLEVEL = 32
    begin
        return
    end 
    insert A values(0)
    

    end
    GO

    –check nested triggers server option
    exec sp_configure ‘nested triggers’
    –name minimum maximum config_value run_value
    –nested triggers 0 1 1 1

    –test with RECURSIVE_TRIGGERS off
    ALTER DATABASE dba set RECURSIVE_TRIGGERS off
    select is_recursive_triggers_on, from sys.databases
    GO
    insert A values(1)
    select
    from A
    –id
    –1
    –0

    –test with RECURSIVE_TRIGGERS on
    ALTER DATABASE dba set RECURSIVE_TRIGGERS on
    select is_recursive_triggers_on, * from sys.databases
    GO

    truncate table A
    insert A values(1)
    select * from A –32 rows

    –若无加@@NESTLEVEL剖断并退出,会产出32层限制的报错,并且表里不会插入任何数据
    /*
    Msg 217, Level 16, State 1, Procedure tri_01, Line 10
    Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

    select from A –0 rows/

    –删表会级联删除触发器,就如索引
    drop table A

是实行 DML
触发器的表或视图,不时称为触发器表或触发器视图。钦赐表格或视图的一丝一毫限定名称是可选的。视图只可以由
INSTEAD OF 触发器引用。

 

DATABASE:
将 DDL
触发器的范围使用于当下数据库。假使钦定,触发器会在脚下数据库中发生 event_type 或 event_group 时触发。

循环/递归触发器的前提便是嵌套触发器,只有同意嵌套了技艺够递归(递归也正是嵌套并触及自个儿),递归有间接和直接三种景况:

ALL SERVER:

  • 直接递归:正是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

  • 能够看来数据库选项RECU奇骏SIVE_T凯雷德IGGE奥迪Q5S,仅对一贯递归有效,对直接递归无效;能够通过Nest
    Triggers的开关来支配是或不是同意嵌套,进而调整是还是不是允许直接递归;

  • 计算机数据库,无论间接递归,依然直接递归,递归次数都有二十六次嵌套的上限;

将 DDL
或登入触发器的成效域应用于当下服务器。借使钦点,触发器会在此时此刻服务器的其他省方时有发生 event_type 或 event_group 时触发。

小结下来:

WITH ENCRYPTION:

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

  2. INSTEAD OF触发器,不受Nest
    Triggers选项影响,均可以嵌套,上限32层,直接递归也是足以的,直接递归无论是不是展开数据库选项RECUSIVE_T中华VIGGERubiconS,都船到江心补漏迟;把地点四个本子示例中的AFTEMurano改为INSTEAD
    OF就能够演示。

加密 CREATE T景逸SUVIGGE智跑 语句的公文。使用 WITH ENCQX56YPTION 可防止止触发器作为
SQL Server 复制的一有些开始展览发布。不可能为 CLLAND 触发器钦定 WITH ENC奥迪Q7YPTION。

 

EXECUTE AS:
钦点实行触发器的平安上下文。以便能够调节 SQL Server
实例用于注明触发器援用的别样数据库对象的权限的用户帐户。

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

NATIVE_COMPILATION:
意味着触发器是地面编写翻译的。

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

SCHEMABINDING:
钦定触发器引用的表不能够被删去或改变。

在SQL
Server和Oracle中都以那般,触发器作为一切业务的一有个别存在,可是并不调整总体事情的交给/回滚,为保险数据一致性,事务逻辑由触发器外层的语句来决定。

FOR | AFTER:
AFTEXC60 钦命仅在触发 SQL 语句中钦定的全体操作成功实行时触发 DML
触发器。全体援引级联操作和自律检查在此触发器触发在此以前也不可能不成功。当 FOLAND是点名的天下无双主要字时,AFTE路虎极光 是暗中认可值。视图不大概定义AFTE兰德Highlander触发器。

 

INSTEAD OF:
钦定实行 DML 触发器并非触发 SQL 语句,因而覆盖触发语句的操作。无法为
DDL 或登陆触发器钦定 INSTEAD OF。

二. DDL触发器

对于 INSTEAD OF 触发器,在全数内定级联合浮动作 ON DELETE
的引用关系的表上不一致意利用 DELETE 选项。类似地,在享有钦命级联动作 ON UPDATE
的引用关系的表上,不容许 UPDATE 选项。

SQL Server
二零零五开端帮忙DDL触发器,它不只限于对CREATE/ALTECRUISER/DROP操作可行,帮忙的DDL事件还或然有诸如:权限的GRANT/DENY/REVOEK,
对象的RENAME, 更新总计音讯等等,可通过DMV查看愈来愈多支持的平地风波类型如下:

{[DELETE] [,] [INSERT] [,] [UPDATE]} :
钦点在针对此表或视图举行尝试时激活 DML
触发器的多少修改语句。必须至少内定二个抉择。在触发器定义中允许以其余顺序对这几个接纳举办自由组合。

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

event_type:
是施行后引致 DDL 触发器触发的 Transact-SQL 语言事件的称谓。

注意:

event_group:
是 Transact-SQL 语言事件的预订义分组的名号。属于其余 Transact-SQL
语言事件实施后的 DDL 触发器触发 event_group。

  1. TRUNCATE不在DDL触发器的风云类型中,SQL Server中将Truncate
    归为DML操作语句,纵然它也并不触发DML触发器,似乎展开按钮的大量导入操作
    (Bulk Import Operations) 同样;

sql_statement:
是触发条件和动作。触发条件内定附加条件,以分明尝试的 DML,DDL
或登入事件是还是不是形成奉行触发器操作。

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

<method_specifier>:

 

对此 CLPRADO触发器,内定要与触发器绑定的次序集的艺术。该格局不得不援引任何参数并回到
void。class_name 必须是行得通的 SQL Server
标记符,况兼必须作为具备程序集可知性的次序聚焦的类存在。

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

 

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

以下是DML触发器的行使,先看看示例数据:

 

计算机数据库 4

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

 

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

insert 触发器:

 

 1 if(OBJECT_ID('trigger_Stu_Insert') is not null)        -- 判断名为 trigger_Stu_Insert 的触发器是否存在
 2 drop trigger trigger_Stu_Insert        -- 删除触发器
 3 go
 4 create trigger trigger_Stu_Insert
 5 on Student        -- 指定创建触发器的表
 6 for insert        -- insert 触发器,也可以写为 after insert
 7 as
 8 
 9 declare @C_Id    int
10 declare @S_Id    int
11 
12 select @C_Id=C_Id from Course where C_Name='SQL'    -- 获取课程为 SQL 的ID
13 select @S_Id=S_Id from inserted        --插入一条学生的数据,那么这条数据就存在 inserted 这个表中
14 
15 select @C_Id
16 select @S_Id
17 
18 select * from inserted
19 
20 update Student set C_S_Id=@C_Id where S_Id=@S_Id
21 go
22 
23 insert into Student(S_StuNo,S_Name,S_Sex,S_Height,S_BirthDate)
24 values('016','大熊','男','210','2017-01-01')
25 
26 select * from Student
27 select * from Course

三. LOGON 触发器

计算机数据库 5

SQL Server
二〇〇六在SP第22中学悄悄引进了LOGON触发器,作为三个实例级的对象,它的种类视图,定义语句和DDL/DML触发器都以分别的。

计算机数据库 6

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

本条例子是:当 Student 表新扩张一条数据时,修改那条数据的科目ID。

在SQL Server中,从名称想到所包涵的意义,LOGON触发器,只帮忙LOGON事件;

delete 触发器:

在ORACLE中,实例级触发器可支撑愈多事件 (SE奥迪Q5VERE奥迪PB18ROOdyssey, LOGON, LOGOFF,
STARTUP, or SHUTDOWN)。

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

 

计算机数据库 7

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

计算机数据库 8

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

以这件事例是:删除钦命课程ID的学习者时,并剔除内定课程ID的学科。

 

update 触发器:

代码示例2: 界定特定用户在特按期间范围登陆、限制连接数

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

计算机数据库 9

 

计算机数据库 10

只顾:纵然LOGON触发器把全部人都锁在外场了如何做?

本条例子是:修改课程名称时,把课程ID为空(null)的学生的学科ID默感到修改的科目ID。

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

取缔修改学生学号触发器,触发器举办数据回滚:

计算机数据库 11

 1 if(OBJECT_ID('trigger_Stu_Update') is not null)        -- 判断名为 trigger_Stu_Update 的触发器是否存在
 2 drop trigger trigger_Stu_Update        -- 删除触发器
 3 go
 4 create trigger trigger_Stu_Update
 5 on Student        -- 指定创建触发器的表
 6 for update        -- update 触发器,也可以写为 after update
 7 as
 8 begin try
 9     if(UPDATE(S_StuNo))        -- 列级触发器:判断是否更新了学生学号(学号不允许更改)
10     begin
11         raiserror(66666,16,1)
12     end
13 end try
14 begin catch
15     select * from deleted        -- 修改前的数据就存在 deleted 这个表中
16     select * from inserted        -- 修改后的数据就存在 inserted 这个表中
17     rollback tran;
18 end catch
19 go
20 
21 update  Student set S_StuNo='006' where S_Id='20'
22 
23 select * from Student

那会儿,只好通过DAC登陆SQL
Server去禁止使用LOGON触发器/修改逻辑以允许登入,DAC登陆格局有长途和本地两种,远程登入需求经过sp_configure
开启remote admin connections
,若无优先开启,这就不得不采取本地登陆格局:

计算机数据库 12

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

after 触发器能够钦命多个操作都能够触发该触发器。只要求在 for/after
前面增加逗号和触发器的项目,比方:

计算机数据库 13

1 for update,insert,delete 
2 
3 after update,insert,delete 

 

instead of 触发器:

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

本条触发器就有意思了,上边先看看数据。

计算机数据库 14

计算机数据库 15

--禁用/启用LOGON触发器
DISABLE TRIGGER limit_user_connections ON ALL SERVER
ENABLE TRIGGER limit_user_connections ON ALL SERVER
 1 if(OBJECT_ID('trigger_Stu_InsteadOf') is not null)        -- 判断名为 trigger_Stu_InsteadOf 的触发器是否存在
 2 drop trigger trigger_Stu_InsteadOf        -- 删除触发器
 3 go
 4 create trigger trigger_Stu_InsteadOf
 5 on Student        -- 指定创建触发器的表
 6 instead of update,insert,delete         -- instead of 触发器
 7 as
 8     select * from deleted        -- 修改前的数据就存在 deleted 这个表中
 9     select * from inserted        -- 修改后的数据就存在 inserted 这个表中
10 go
11 
12 update Student set S_StuNo='006' where S_Id='20'
13 
14 insert into Student([S_StuNo],[S_Name],[S_Sex],[S_Height],[S_BirthDate])
15 values('017','清红','女','180','2017-01-01')
16 
17 delete from Student where C_S_Id='5'
18 
19 select * from Student

 

计算机数据库 16

参考:

实践上面包车型客车语句之后,咦,数据怎么一点转移都并没有?看看上边的介绍。instead
of 触发器是事先接触。

CREATE TRIGGER (Transact-SQL)

instead of
触发器并不推行其定义的操作(insert、update、delete)而仅是施行触发器自己,并且会覆盖触发语句的操作,即
after 触发器 T-SQL 语句的操作,很明朗我们地点定义的表 Student 的 after
触发器也从未效果了,未来领会了那句话了啊。

修改触发器:

Create Nested Triggers

 1 alter trigger trigger_Stu_InsteadOf        -- 修改触发器
 2 on Student        -- 指定创建触发器的表
 3 instead of update,insert,delete         -- instead of 触发器
 4 as
 5     declare @Count1 int
 6     declare @Count2 int
 7 
 8     select @Count1=COUNT(1) from deleted        
 9     select @Count2=COUNT(1) from inserted        
10 
11     if(@Count1>0 and @Count2>0)
12     begin
13         select 'update操作'
14     end
15     else if(@Count1>0)
16     begin
17         select 'delete操作'
18     end
19     else if(@Count2>0)
20     begin
21         select 'insert操作'
22     end
23 go
24 
25 update Student set S_StuNo='006' where S_Id='20'
26 
27 insert into Student([S_StuNo],[S_Name],[S_Sex],[S_Height],[S_BirthDate])
28 values('017','清红','女','180','2017-01-01')
29 
30 delete from Student where C_S_Id='5'
31 
32 select * from Student

计算机数据库 17

Transact-SQL statements

启用/禁止使用触发器:

1 --禁用触发器
2 disable trigger trigger_Stu_InsteadOf on Student;    -- trigger_Stu_InsteadOf 触发器名称
3 --启用触发器
4 enable trigger trigger_Stu_InsteadOf on Student;    -- trigger_Stu_InsteadOf 触发器名称

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

询问已存在的触发器:

1 -- 查询已存在的触发器
2 select * from sys.triggers;
3 select * from sys.objects where type = 'TR';
4 select * from sysobjects where xtype='TR'

 1 -- sys.trigger_events 触发器事件对象视图
 2 select * from sys.trigger_events 
 3 
 4 -- 查看触发器触发事件对象    
 5 select a.type_desc,b.* from sys.trigger_events a 
 6 inner join sys.triggers b on a.object_id = b.object_id
 7 where b.name = 'trigger_Stu_Insert';
 8 
 9 -- 查询创建触发器的 T-SQL 文本
10 exec sp_helptext 'trigger_Stu_Insert'

Database PL/SQL Language Reference, Using Triggers

 

参考:

Post Author: admin

发表评论

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