金沙糖果派对2015cc 3

背景

  上一篇中,小编介绍了SQL Server
允许访谈数据库的元数据,为啥有元数据,怎么样行使元数据。这一篇中笔者会介绍怎么样尤其找到种种有价值的音讯。以触发器为例,因为它们往往一齐比相当多难题。

 

触发器能够驾驭为由特定事件触发的寄存进度,
和存款和储蓄进度、函数同样,触发器也支撑CLEvoque,如今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)还是后来(AFTERAV4)触发,也便是接触事件和该触发器的操作顺序。
  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和AFTEPRADO提议触发器的触及时间分别为前触发和后触发格局,前触发是在进行触发事件在此以前接触当前所开创的触发器,后触发是在奉行触发事件随后触发当前所创立的触发器。

REFERENCING子句表明有关称号,在行触发器的PL/SQL块和WHEN子句中得以采纳相关称号参照当前的新、旧列值,暗中同意的连带称号为OLD和NEW。触发器的PL/SQL块中运用相关称号时,必须在它们在此之前加冒号(:),但在WHEN子句中则无法加冒号。

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

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

FORAV4 EACH
ROW选项表明触发器为行触发器。行触发器和说话触发器的界别表今后:行触发器须求当多个DML语句操走影响数据库中的多行数据时,对于当中的每一个数据行,只要它们符合触发约束标准,均激活二次触发器;而说话触发器将一切讲话操作作为触发事件,当它符合约束原则时,激活叁次触发器。当省略FOR
EACH ROW 选项时,BEFORE和AFTE奥德赛触发器为语句触发器,而INSTEAD
OF触发器则不得不为行触发器。

WHEN子句表明触发约束标准。Condition为多个逻辑表达时,当中必须含有相关称号,而不能够富含查询语句,也无法调用PL/SQL函数。WHEN子句钦赐的接触约束标准只好用在BEFORE和AFTE本田CR-V行触发器中,无法用在INSTEAD
OF行触发器和别的品类的触发器中。

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

ddl_event:二个或多少个DDL事件,事件间用O逍客分开。

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

示范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或AFTE途乐选项。
  3. FOOdyssey 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, ALTESportage, DROP 等) 触发;

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

触发器是一种特有的存款和储蓄过程,它的实践不是由程序调用,也不是手动试行,而是由事件来触发。触发器是当对某三个表实行操作。举个例子:update、insert、delete那几个操作的时候,系统会自动调用推行该表上相应的触发器。

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

  在我们选取sys.triggers的音讯在此之前,需求来再次叁回,全体的数据库对象都存在于sys.objects金沙糖果派对2015cc,中,在SQL
Server 中的对象包含以下:聚合的CL大切诺基函数,check
约束,SQL标量函数,CL奥迪Q7标量函数,CL宝马X3表值函数,SQL内联表值函数,内部表,SQL存款和储蓄进程,CL奇骏存款和储蓄进程,安顿指南,主键约束,老式法规,复制过滤程序,系统基础表,同义词,系列对象,服务队列,CL途睿欧DML
触发器,SQL表值函数,表类型,用户自定义表,独一约束,视图和庞大存款和储蓄进程等。

  触发器是目的所以基础消息一定保存在sys.objects。不幸运的是,一时我们需求额外的音信,这一个消息方可由此目录视图查询。那几个额外数占有是怎么吗?

 

  修改大家应用过的询问,来查询sys.triggers的列,这一次大家会看出额外新闻。那么些额外列是源于于sys.objects。

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

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

FROM

 (SELECT Thecol.name

  FROM sys.system_views AS TheView

    INNER JOIN sys.system_columns AS TheCol

      ON TheView.object_ID=TheCol.Object_ID

  WHERE  TheView.name = 'triggers') trigger_column

FULL OUTER JOIN

 (SELECT Thecol.name

  FROM sys.system_views AS TheView

    INNER JOIN sys.system_columns AS TheCol

      ON TheView.object_ID=TheCol.Object_ID

  WHERE  TheView.name = 'objects') object_column

ON trigger_column.name=object_column.name

查询结果:

In_Sys_Triggers                In_Sys_Objects

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

name                           name

object_id                      object_id

NOT INCLUDED                   principal_id

NOT INCLUDED                   schema_id

NOT INCLUDED                   parent_object_id

type                           type

type_desc                      type_desc

create_date                    create_date

modify_date                    modify_date

is_ms_shipped                  is_ms_shipped

NOT INCLUDED                   is_published

NOT INCLUDED                   is_schema_published

is_not_for_replication         NOT INCLUDED

is_instead_of_trigger          NOT INCLUDED

parent_id                      NOT INCLUDED

is_disabled                    NOT INCLUDED

parent_class                   NOT INCLUDED

parent_class_desc              NOT INCLUDED

 

如上那些让我们驾驭在sys.triggers的额外信息,可是因为它始终是表的子对象,所以有个别不相干新闻是不会议及展览示在那个钦点的视图或然sys.triggers中的。现在将要带大家去继续找找那一个信息。

 

触发器分类:

触发器的难点

  触发器是立见成效的,但是因为它们在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

。但是三个表能够有几个AFTE奇骏触发器行为。那些将彰显在底下的询问中(排除视图):

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_金沙糖果派对网站app,settriggerorder来调整顺序。通过采纳objectpropertyex()元数据函数,须要根据事件输入参数‘ExecIsLastDeleteTrigger’,
‘ExecIsLastInsertTrigger’ 可能‘ExecIsLastUpdateTrigger’来确认哪个人是末了贰个实行的触发器
。为了拿走第三个触发器,酌情接纳ObjectPropertyEx()
元数据函数,要求输入参数 ‘ExecIsFirstDeleteTrigger’,
‘ExecIsFirstInsertTrigger’ 可能 ‘ExecIsFirstUpdateTrigger’。

进而大家今日知道了表有怎么着触发器,哪些事件触发那几个触发器。能够接纳objectpropertyex()元数据函数,那一个函数重回比较多例外新闻,根据钦命的参数不一致。通过翻看MSDN中的文书档案,查看里面包车型客车叁个文书档案是还是不是有利于元数据查询,总是值得检查的。

在SQL
Server中,从概念来讲只有语句级触发器,但假若有行级的逻辑要管理,有七个仅在触发器内有效的表
(inserted, deleted),
存放着受影响的行,可以从那三个表里抽取特定的行并自行定义脚本管理;

3、登入触发器:是指当用户登入 SQL SE奇骏VE奥迪Q5实例创立会话时接触。假若身份验证失利,登陆触发器不会接触。

触发器几时触发事件?

让大家看一下那些触发器,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应用相关子查询来查询那个事件。

在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触发器,在表上匡助AFTERubicon触发器,在表/视图上支持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

金沙糖果派对2015cc 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; 

 

结果如图:

金沙糖果派对2015cc 2

 

8个援引正在实践那么些进程。大家在sys.SQL_modules中检索了装有的概念能够找到四个特定的字符串,这种方法不快很暴力,然而它是行得通的!

在ORACLE中,在表上支持BEFORE/AFTELX570触发器,在视图上支撑INSTEAD
OF触发器,比如ORACLE中不能够间接对视图做DML操作,能够经过INSTEAD
OF触发器来变样达成;

AFTE奥迪Q5 触发器语法:

在具有目的中寻找字符串

笔者想通晓除了触发器之外是不是还会有其他对象调用那几个进度?咱们有个别修改查询以搜寻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; 

询问结果如下图:

金沙糖果派对2015cc 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_TTiguanIGGE君越S选项,能够安装是不是接触触发器;
  • 导入导出向导/SSIS,如果目的是表,也会有FIRE_TCR-VIGGEXC60S的装置选项;
  • 另外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 ]  

嵌套触发器,正是一遍操作触发了二个触发器,然后触发器里的口舌继续接触别的触发器,假如继续回头触发了本身,那么正是递归触发器。

参数:

对此AFTEOdyssey触发器有个多少个开关分别调控嵌套触发和递归触发:

CREATE OR ALTER:

exec sp_configure 'nested triggers'

创制或然有规范化的修改触发器(即要修改的触发器必须已经存在)。

admin

相关文章

发表评论

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