它是与表事件相关的特殊的存储过程,新葡京32450网址是指触发器在数据库中发生 DML

发布时间:2019-08-12  栏目:数据  评论:0 Comments

背景

  上一篇中,笔者介绍了SQL Server
允许访问数据库的元数据,为何有元数据,怎样行使元数据。这一篇中笔者会介绍如何越发找到各个有价值的音讯。以触发器为例,因为它们往往一同相当多标题。

 

触发器能够知道为由特定事件触发的储存进度,
和存款和储蓄进度、函数同样,触发器也支撑CLLAND,如今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传祺)触发,也便是触发事件和该触发器的操作顺序。
  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和AFTEEvoque建议触发器的触发时间分别为前触发和后触发情势,前触发是在实行触发事件从前接触当前所成立的触发器,后触发是在推行触发事件现在触发当前所创立的触发器。

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

NEW只在UPDATE、INSERT的DML触发器内可用,它富含了修改爆发后被影响行的值。

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

FO福特Explorer EACH
ROW选项表明触发器为行触发器。行触发器和语句触发器的区分表未来:行触发器必要当叁个DML语句操走影响数据库中的多行数据时,对于里边的各类数据行,只要它们符合触发约束原则,均激活一遍触发器;而讲话触发器将全体讲话操作作为触发事件,当它适合约束原则时,激活贰回触发器。当省略FOR
EACH ROW 选项时,BEFORE和AFTERAV4触发器为语句触发器,而INSTEAD
OF触发器则不得不为行触发器。

WHEN子句表达触发约束原则。Condition为三个逻辑表明时,当中必须含有相关称号,而无法包括查询语句,也无法调用PL/SQL函数。WHEN子句钦点的触及约束标准只可以用在BEFORE和AFTELacrosse行触发器中,无法用在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)而成,由此不用全部的视图都以可更新的,但能够遵照所需的主意实施更新。

开创INSTEAD OF触发器须要留心以下几点:

  1. 唯其如此被创建在视图上,并且该视图没有一点名WITH CHECK OPTION选项。
  2. 不能够内定BEFORE或AFTE索罗德选项。
  3. FO奔驰G级 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’来搜索你想要的视图名称。.

在2011及其未来版本,能够使用三个新的表值函数非常大地简化上述查询,并得防止止种种连接。在下边的查询中,大家将搜索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这几个操作的时候,系统会活动调用施行该表上相应的触发器。

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

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

  在我们应用sys.triggers的音信在此之前,须求来再一次一次,全数的数据库对象都留存于sys.objects中,在SQL
Server 中的对象包蕴以下:聚合的CL凯雷德函数,check
约束,SQL标量函数,CLLX570标量函数,CLCR-V表值函数,SQL内联表值函数,内部表,SQL存款和储蓄进程,CLTucson存款和储蓄进度,安排指南,主键约束,老式法规,复制过滤程序,系统基础表,同义词,连串对象,服务队列,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中的。未来将在带大家去承继找找那么些音信。

 

触发器分类:

触发器分类:

触发器的主题材料

  触发器是平价的,不过因为它们在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( 数据垄断(monopoly)语言 Data Manipulation
Language)触发器:是指触发器在数据库中生出 DML
事件时将启用。DML事件是指在表或视图中对数码进行的 insert、update、delete
操作的说话。

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 操作语句。

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_settriggerorder来支配顺序。通过动用objectpropertyex()元数据函数,需求基于事件输入参数‘ExecIsLastDeleteTrigger’,
‘ExecIsLastInsertTrigger’ 大概‘ExecIsLastUpdateTrigger’来承认什么人是最终二个实行的触发器
。为了赢得第一个触发器,酌情选取ObjectPropertyEx()
元数据函数,须求输入参数 ‘ExecIsFirstDeleteTrigger’,
‘ExecIsFirstInsertTrigger’ 可能 ‘ExecIsFirstUpdateTrigger’。

进而我们明天清楚了表有怎样触发器,哪些事件触发这几个触发器。可以动用objectpropertyex()元数据函数,那么些函数重返比相当多不及音讯,根据钦赐的参数差异。通过查看MSDN中的文书档案,查看里面包车型地铁三个文书档案是还是不是有利于元数据查询,总是值得检查的。

在SQL
Server中,从概念来讲独有语句级触发器,但纵然有行级的逻辑要拍卖,有多个仅在触发器内有效的表
(inserted, deleted),
贮存着受影响的行,能够从那八个表里抽出特定的行并自行定义脚本管理;

3、登入触发器:是指当用户登陆 SQL SEOdysseyVEHighlander实例创立会话时接触。假诺身份验证失败,登陆触发器不会接触。

3、登入触发器:是指当用户登录 SQL SE智跑VEMurano实例创立会话时接触。如若身份验证失利,登陆触发器不会触发。

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

让我们看一下那么些触发器,DML触发器能够在有着别的时间发出后触发,但是足以在约束被拍卖前还要触发INSTEAD
OF触发动作。上边大家就来看看全部的接触的究竟是AFTESportage 照旧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
触发器触发的主意各异又分为以下三种意况:

其间 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 某一操作之后触发器才会被触发,且不得不定义在表上。

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 触发器。

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福特Explorer/INSTEAD
OF触发器,在表上帮忙AFTE君越触发器,在表/视图上支撑INSTEAD
OF触发器,对于BEFORE触发器的需要能够尝试通过INSEAD OF触发器来兑现;

DML
触发器有七个匠心独运的表:插入表(instered)和删除表(deleted),这两张表是逻辑表。这四个表是白手起家在数据库服务器的内部存款和储蓄器中,况且两张表的都以只读的。这两张表的构造和触发器所在的数据表的构造是同一的。当触发器达成职业后,这两张表就能够被删去。Inserted
表的数码是插入或是修改后的数额,而 deleted
表的数额是翻新前的或是已去除的多少。

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

新葡京32450网址 1

新葡京32450网址 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; 

 

结果如图:

新葡京32450网址 3

 

8个援用正在实行那几个进度。我们在sys.SQL_modules中寻找了全体的定义能够找到贰个特定的字符串,这种艺术一点也不快很暴力,不过它是平价的!

在ORACLE中,在表上扶助BEFORE/AFTE奥迪Q5触发器,在视图上支撑INSTEAD
OF触发器,比如ORACLE中不能直接对视图做DML操作,能够由此INSTEAD
OF触发器来变样达成;

AFTECR-V 触发器语法:

AFTEKoleos 触发器语法:

在有着目标中追寻字符串

自家想清楚除了触发器之外是还是不是还会有别的对象调用那个历程?大家略微修改查询以搜索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; 

询问结果如下图:

新葡京32450网址 4

 From this output we can see that, other than the procedure itself where
it is defined, and the triggers, only dbo.uspLogError is executing the
uspPrintError procedure. (see the first column, second line down)

从这几个输出中大家能够见到,除了在概念它的历程自己之外,还会有触发器,独有dbo.uspLogError正在实行uspPrintError进程。(见第一列,第二行往下)

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 ]
 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 触发器语法:

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 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 触发器语法:

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 ]  
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 ]  

留下评论

网站地图xml地图