触发器哪天触发事件?

让大家看一下那个触发器,DML触发器能够在颇具别的时间发生后触发,可是足以在封锁被处理前并且触发INSTEAD
OF触发动作。上面我们就来探访全数的触及的到底是AFTEEnclave 照旧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使用相关子查询来询问那些事件。

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

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)还是未来(AFTEPAJERO)触发,也便是接触事件和该触发器的操作顺序。
  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和AFTECRUISER指出触发器的接触时间独家为前触发和后触发格局,前触发是在实行触发事件从前接触当前所创造的触发器,后触发是在进行触发事件随后触发当前所制造的触发器。

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

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

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

FO奥迪Q7 EACH
ROW选项表达触发器为行触发器。行触发器和讲话触发器的分别展以后:行触发器供给当一个DML语句操走影响数据库中的多行数据时,对于内部的各样数据行,只要它们符合触发约束规范,均激活贰次触发器;而讲话触发器将一切讲话操作作为触发事件,当它符合约束标准时,激活一次触发器。当省略FOR
EACH ROW 选项时,BEFORE和AFTELX570触发器为语句触发器,而INSTEAD
OF触发器则只可以为行触发器。

WHEN子句说明触发约束原则。Condition为二个逻辑表明时,当中必须包罗相关称号,而不能够包蕴查询语句,也不能够调用PL/SQL函数。WHEN子句钦赐的触发约束标准只好用在BEFORE和AFTE奥迪Q5行触发器中,不可能用在INSTEAD
OF行触发器和任何类其他触发器中。

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

ddl_event:贰个或多个DDL事件,事件间用O奥迪Q5分开。

database_event:叁个或八个数据库事件,事件间用OEvoque分开。

示范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库罗德 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;

 

修改触发器:

8.5   数据库触发器的利用比如

  例1:创建一个DML语句级触发器,当对emp表试行INSERT, UPDATE, DELETE 操作时,它自动更新dept_summary 表中的数据。由于在PL/SQL块中不能够直接调用DDL语句,所以,利用ORACLE内置包DBMS_UTILITY中的EXEC_DDL_STATEMENT进程,由它实践DDL语句创立触发器。 

CREATE TABLE dept_summary(
 Deptno NUMBER(2),
 Sal_sum NUMBER(9, 2),
 Emp_count NUMBER); 

INSERT INTO dept_summary(deptno, sal_sum, emp_count)
 SELECT deptno, SUM(sal), COUNT(*) 
FROM emp 
GROUP BY deptno;

--创建一个PL/SQL过程disp_dept_summary
--在触发器中调用该过程显示dept_summary标中的数据。
CREATE OR REPLACE PROCEDURE disp_dept_summary
IS
 Rec dept_summary%ROWTYPE;
 CURSOR c1 IS SELECT * FROM dept_summary;
BEGIN
 OPEN c1;
 FETCH c1 INTO REC;
 DBMS_OUTPUT.PUT_LINE('deptno    sal_sum    emp_count');
 DBMS_OUTPUT.PUT_LINE('-------------------------------------');
 WHILE c1%FOUND LOOP
    DBMS_OUTPUT.PUT_LINE(RPAD(rec.deptno, 6)||
      To_char(rec.sal_sum, '$999,999.99')||
      LPAD(rec.emp_count, 13));
    FETCH c1 INTO rec;
 END LOOP;
 CLOSE c1;
END;
BEGIN
 DBMS_OUTPUT.PUT_LINE('插入前');
 Disp_dept_summary();
 DBMS_UTILITY.EXEC_DDL_STATEMENT('
    CREATE OR REPLACE TRIGGER trig1
      AFTER INSERT OR DELETE OR UPDATE OF sal ON emp
    BEGIN
      DBMS_OUTPUT.PUT_LINE(''正在执行trig1 触发器…'');
      DELETE FROM dept_summary;
      INSERT INTO dept_summary(deptno, sal_sum, emp_count)
      SELECT deptno, SUM(sal), COUNT(*) 
      FROM emp GROUP BY deptno;
    END;
 ');


 INSERT INTO dept(deptno, dname, loc) 
 VALUES(90, ‘demo_dept’, ‘none_loc’);
 INSERT INTO emp(ename, deptno, empno, sal)
 VALUES(USER, 90, 9999, 3000);

 DBMS_OUTPUT.PUT_LINE('插入后');
 Disp_dept_summary();

 UPDATE emp SET sal=1000 WHERE empno=9999;
 DBMS_OUTPUT.PUT_LINE('修改后');
 Disp_dept_summary();

 DELETE FROM emp WHERE empno=9999;
 DELETE FROM dept WHERE deptno=90;

 DBMS_OUTPUT.PUT_LINE('删除后');
 Disp_dept_summary(); 
 DBMS_UTILITY.EXEC_DDL_STATEMENT(‘DROP TRIGGER trig1’);
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);

END;

  例2:创建DML语句行级触发器。当对emp表施行INSERT, UPDATE, DELETE 操作时,它自动更新dept_summary 表中的数据。由于在PL/SQL块中不能够平素调用DDL语句,所以,利用ORACLE内置包DBMS_UTILITY中的EXEC_DDL_STATEMENT进度,由它实施DDL语句成立触发器。 

BEGIN
  DBMS_OUTPUT.PUT_LINE('插入前');
  Disp_dept_summary();
  DBMS_UTILITY.EXEC_DDL_STATEMENT(
    'CREATE OR REPLACE TRIGGER trig2_update
      AFTER UPDATE OF sal ON emp
      REFERENCING OLD AS old_emp NEW AS new_emp
      FOR EACH ROW
      WHEN (old_emp.sal != new_emp.sal)
    BEGIN
      DBMS_OUTPUT.PUT_LINE(''正在执行trig2_update 触发器…'');
      DBMS_OUTPUT.PUT_LINE(''sal 旧值:''|| :old_emp.sal);
      DBMS_OUTPUT.PUT_LINE(''sal 新值:''|| :new_emp.sal);
      UPDATE dept_summary
        SET sal_sum=sal_sum + :new_emp.sal - :old_emp.sal
        WHERE deptno = :new_emp.deptno;
    END;'
  );

  DBMS_UTILITY.EXEC_DDL_STATEMENT(
    'CREATE OR REPLACE TRIGGER trig2_insert
      AFTER INSERT ON emp
      REFERENCING NEW AS new_emp
      FOR EACH ROW
    DECLARE
      I NUMBER;
    BEGIN
      DBMS_OUTPUT.PUT_LINE(''正在执行trig2_insert 触发器…'');
      SELECT COUNT(*) INTO I 
      FROM dept_summary WHERE deptno = :new_emp.deptno;
      IF I > 0 THEN
        UPDATE dept_summary 
        SET sal_sum=sal_sum+:new_emp.sal,
        Emp_count=emp_count+1
        WHERE deptno = :new_emp.deptno;
      ELSE
        INSERT INTO dept_summary
        VALUES (:new_emp.deptno, :new_emp.sal, 1);
      END IF;
    END;'
  );

  DBMS_UTILITY.EXEC_DDL_STATEMENT(
    'CREATE OR REPLACE TRIGGER trig2_delete
      AFTER DELETE ON emp
      REFERENCING OLD AS old_emp
      FOR EACH ROW
    DECLARE
      I NUMBER;
    BEGIN
      DBMS_OUTPUT.PUT_LINE(''正在执行trig2_delete 触发器…'');
      SELECT emp_count INTO I 
      FROM dept_summary WHERE deptno = :old_emp.deptno;
      IF I >1 THEN
        UPDATE dept_summary 
        SET sal_sum=sal_sum - :old_emp.sal,
        Emp_count=emp_count - 1
        WHERE deptno = :old_emp.deptno;
      ELSE
        DELETE FROM dept_summary WHERE deptno = :old_emp.deptno;
      END IF;
    END;'
  );

  INSERT INTO dept(deptno, dname, loc) 
    VALUES(90, 'demo_dept', 'none_loc');
  INSERT INTO emp(ename, deptno, empno, sal)
    VALUES(USER, 90, 9999, 3000);
  INSERT INTO emp(ename, deptno, empno, sal)
    VALUES(USER, 90, 9998, 2000);
  DBMS_OUTPUT.PUT_LINE('插入后');
  Disp_dept_summary();

  UPDATE emp SET sal = sal*1.1 WHERE deptno=90;
  DBMS_OUTPUT.PUT_LINE('修改后');
  Disp_dept_summary();

  DELETE FROM emp WHERE deptno=90;
  DELETE FROM dept WHERE deptno=90;
  DBMS_OUTPUT.PUT_LINE('删除后');
  Disp_dept_summary();

  DBMS_UTILITY.EXEC_DDL_STATEMENT('DROP TRIGGER trig2_update');
  DBMS_UTILITY.EXEC_DDL_STATEMENT('DROP TRIGGER trig2_insert');
  DBMS_UTILITY.EXEC_DDL_STATEMENT('DROP TRIGGER trig2_delete');
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END;

  例3:运用ORACLE提供的口径谓词INSERTING、UPDATING和DELETING创制与例2具备同等效果的触发器。 

BEGIN
    DBMS_OUTPUT.PUT_LINE('插入前');
    Disp_dept_summary();
    DBMS_UTILITY.EXEC_DDL_STATEMENT(
        'CREATE OR REPLACE TRIGGER trig2
            AFTER INSERT OR DELETE OR UPDATE OF sal
ON emp
            REFERENCING OLD AS old_emp NEW AS new_emp
            FOR EACH ROW
        DECLARE
            I NUMBER;
        BEGIN
            IF UPDATING AND :old_emp.sal != :new_emp.sal THEN
            DBMS_OUTPUT.PUT_LINE(''正在执行trig2 触发器…'');
                DBMS_OUTPUT.PUT_LINE(''sal 旧值:''|| :old_emp.sal);
                DBMS_OUTPUT.PUT_LINE(''sal 新值:''|| :new_emp.sal);
                UPDATE dept_summary
                    SET sal_sum=sal_sum + :new_emp.sal - :old_emp.sal
                WHERE deptno = :new_emp.deptno;
            ELSIF INSERTING THEN
                DBMS_OUTPUT.PUT_LINE(''正在执行trig2触发器…'');
                SELECT COUNT(*) INTO I 
        FROM dept_summary 
        WHERE deptno = :new_emp.deptno;
                IF I > 0 THEN
                    UPDATE dept_summary 
          SET sal_sum=sal_sum+:new_emp.sal,
              Emp_count=emp_count+1
          WHERE deptno = :new_emp.deptno;
            ELSE
          INSERT INTO dept_summary
            VALUES (:new_emp.deptno, :new_emp.sal, 1);
        END IF;
      ELSE
        DBMS_OUTPUT.PUT_LINE(''正在执行trig2触发器…'');
        SELECT emp_count INTO I 
        FROM dept_summary WHERE deptno = :old_emp.deptno;
      IF I > 1 THEN
        UPDATE dept_summary 
        SET sal_sum=sal_sum - :old_emp.sal,
        Emp_count=emp_count - 1
        WHERE deptno = :old_emp.deptno;
      ELSE
          DELETE FROM dept_summary 
          WHERE deptno = :old_emp.deptno;
      END IF;
    END IF;
    END;'
  );

  INSERT INTO dept(deptno, dname, loc) 
    VALUES(90, 'demo_dept', 'none_loc');
  INSERT INTO emp(ename, deptno, empno, sal)
    VALUES(USER, 90, 9999, 3000);
  INSERT INTO emp(ename, deptno, empno, sal)
    VALUES(USER, 90, 9998, 2000);
  DBMS_OUTPUT.PUT_LINE('插入后');
  Disp_dept_summary();

  UPDATE emp SET sal = sal*1.1 WHERE deptno=90;
  DBMS_OUTPUT.PUT_LINE('修改后');
  Disp_dept_summary();

  DELETE FROM emp WHERE deptno=90;
  DELETE FROM dept WHERE deptno=90;
  DBMS_OUTPUT.PUT_LINE('删除后');
  Disp_dept_summary();

  DBMS_UTILITY.EXEC_DDL_STATEMENT('DROP TRIGGER trig2');
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END;

  例4:创设INSTEAD OF 触发器。首先创立一个视图myview, 由于该视图是复合查询所发出的视图,所以不能施行DML语句。依据用户对视图所插入的数额判别须求将数据插入到哪些视图基表中,然后对该基表执行插入操作。 

DECLARE
    No NUMBER;
    Name VARCHAR2(20);
BEGIN
    DBMS_UTILITY.EXEC_DDL_STATEMENT('
        CREATE OR REPLACE VIEW myview AS
            SELECT empno, ename, ''E'' type FROM emp
            UNION
            SELECT dept.deptno, dname, ''D'' FROM dept
    ');
    -- 创建INSTEAD OF 触发器trigger3;
    DBMS_UTILITY.EXEC_DDL_STATEMENT('
        CREATE OR REPLACE TRIGGER trig3
            INSTEAD OF INSERT ON myview
            REFERENCING NEW n
            FOR EACH ROW
        DECLARE
            Rows INTEGER;
        BEGIN
            DBMS_OUTPUT.PUT_LINE(''正在执行trig3触发器…'');
            IF :n.type = ''D'' THEN
                SELECT COUNT(*) INTO rows
                    FROM dept WHERE deptno = :n.empno;
                IF rows = 0 THEN
                    DBMS_OUTPUT.PUT_LINE(''向dept表中插入数据…'');
                    INSERT INTO dept(deptno, dname, loc)
                        VALUES (:n.empno, :n.ename, ''none’’);
                ELSE
                    DBMS_OUTPUT.PUT_LINE(''编号为''|| :n.empno||
                     ''的部门已存在,插入操作失败!'');
                 END IF;
            ELSE
                SELECT COUNT(*) INTO rows
                    FROM emp WHERE empno = :n.empno;
                IF rows = 0 THEN
                    DBMS_OUTPUT.PUT_LINE('’向emp表中插入数据…’’);
                    INSERT INTO emp(empno, ename)
                        VALUES(:n.empno, :n.ename);
                ELSE
                    DBMS_OUTPUT.PUT_LINE(''编号为''|| :n.empno||
                      ''的人员已存在,插入操作失败!'');
                END IF;
            END IF;
        END;
    ');

    INSERT INTO myview VALUES (70, 'demo', 'D');
    INSERT INTO myview VALUES (9999, USER, 'E');
    SELECT deptno, dname INTO no, name FROM dept WHERE deptno=70;
    DBMS_OUTPUT.PUT_LINE('员工编号:'||TO_CHAR(no)||'姓名:'||name);
    SELECT empno, ename INTO no, name FROM emp WHERE empno=9999;
    DBMS_OUTPUT.PUT_LINE('部门编号:'||TO_CHAR(no)||'姓名:'||name);
  DELETE FROM emp WHERE empno=9999;
  DELETE FROM dept WHERE deptno=70;
    DBMS_UTILITY.EXEC_DDL_STATEMENT('DROP TRIGGER trig3');
END;

  例5:采纳ORACLE事件性质函数,创设多少个类别事件触发器。首先创立三个事件日志表eventlog,由它存款和储蓄用户在时下数据库中所创制的数据库对象,以及用户的登录和裁撤、数据库的运转和停业等事件,之后创建trig4_ddl、trig4_before和trig4_after触发器,它们调用事件性质函数将逐个事件记录到eventlog数据表中。 

BEGIN
    -- 创建用于记录事件日志的数据表
    DBMS_UTILITY.EXEC_DDL_STATEMENT('
        CREATE TABLE eventlog(
            Eventname VARCHAR2(20) NOT NULL,
            Eventdate date default sysdate,
            Inst_num NUMBER NULL,
            Db_name VARCHAR2(50) NULL,
            Srv_error NUMBER NULL,
            Username VARCHAR2(30) NULL,
            Obj_type VARCHAR2(20) NULL,
            Obj_name VARCHAR2(30) NULL,
            Obj_owner VARCHAR2(30) NULL
        )
    ');

    -- 创建DDL触发器trig4_ddl
    DBMS_UTILITY.EXEC_DDL_STATEMENT('
        CREATE OR REPLACE TRIGGER trig4_ddl
            AFTER CREATE OR ALTER OR DROP 
ON DATABASE
        DECLARE
            Event VARCHAR2(20);
            Typ VARCHAR2(20);
            Name VARCHAR2(30);
            Owner VARCHAR2(30);
        BEGIN
            -- 读取DDL事件属性
            Event := SYSEVENT;
            Typ := DICTIONARY_OBJ_TYPE;
            Name := DICTIONARY_OBJ_NAME;
            Owner := DICTIONARY_OBJ_OWNER;
            --将事件属性插入到事件日志表中
            INSERT INTO scott.eventlog(eventname, obj_type, obj_name, obj_owner)
                VALUES(event, typ, name, owner);
        END;
    ');

    -- 创建LOGON、STARTUP和SERVERERROR 事件触发器
    DBMS_UTILITY.EXEC_DDL_STATEMENT('
        CREATE OR REPLACE TRIGGER trig4_after
            AFTER LOGON OR STARTUP OR SERVERERROR 
      ON DATABASE
        DECLARE
            Event VARCHAR2(20);
            Instance NUMBER;
            Err_num NUMBER;
            Dbname VARCHAR2(50);
            User VARCHAR2(30);
        BEGIN
            Event := SYSEVENT;
            IF event = ''LOGON'' THEN
                User := LOGIN_USER;
                INSERT INTO eventlog(eventname, username)
                    VALUES(event, user);
            ELSIF event = ''SERVERERROR'' THEN
                Err_num := SERVER_ERROR(1);
                INSERT INTO eventlog(eventname, srv_error)
                    VALUES(event, err_num);
            ELSE
                Instance := INSTANCE_NUM;
                Dbname := DATABASE_NAME;
                INSERT INTO eventlog(eventname, inst_num, db_name)
                    VALUES(event, instance, dbname);
      END IF;
    END;
  ');

  -- 创建LOGOFF和SHUTDOWN 事件触发器
  DBMS_UTILITY.EXEC_DDL_STATEMENT('
    CREATE OR REPLACE TRIGGER trig4_before
      BEFORE LOGOFF OR SHUTDOWN 
      ON DATABASE
    DECLARE
      Event VARCHAR2(20);
      Instance NUMBER;
      Dbname VARCHAR2(50);
      User VARCHAR2(30);
    BEGIN
      Event := SYSEVENT;
      IF event = ''LOGOFF'' THEN
        User := LOGIN_USER;
        INSERT INTO eventlog(eventname, username)
          VALUES(event, user);
      ELSE
        Instance := INSTANCE_NUM;
        Dbname := DATABASE_NAME;
        INSERT INTO eventlog(eventname, inst_num, db_name)
          VALUES(event, instance, dbname);
      END IF;
    END;
  ');
END;

CREATE TABLE mydata(mydate NUMBER);
CONNECT SCOTT/TIGER

COL eventname FORMAT A10
COL eventdate FORMAT A12
COL username FORMAT A10
COL obj_type FORMAT A15
COL obj_name FORMAT A15
COL obj_owner FORMAT A10
SELECT eventname, eventdate, obj_type, obj_name, obj_owner, username, Srv_error
  FROM eventlog;

DROP TRIGGER trig4_ddl;
DROP TRIGGER trig4_before;
DROP TRIGGER trig4_after;
DROP TABLE eventlog;
DROP TABLE mydata;

背景

  上一篇中,小编介绍了SQL Server
允许访谈数据库的元数据,为啥有元数据,怎样利用元数据。这一篇中作者会介绍怎么着越发找到种种有价值的音讯。以触发器为例,因为它们往往一齐相当多主题素材。

 

在ORACLE中,实例级触发器可支撑更加多事件 (SEGL450VERE帕杰罗RO奇骏, LOGON, LOGOFF,
STARTUP, or SHUTDOWN)。

加密 CREATE T途胜IGGE奇骏 语句的文书。使用 WITH ENC奥迪Q5YPTION 可以卫戍触发器作为
SQL Server 复制的一片段进行揭露。不能为 CLCR-V 触发器钦赐 WITH ENC陆风X8YPTION。

8.2.4 系统触发器事件性质

这一个触发器访谈了多少对象

在代码中,各类触发器要拜会多少对象(例如表和函数)?

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

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来其实看一下,有怎么珍视视。

  • 大批量导入操作,如:BULK INSERT, bcp/INSERT… SELECT * FROM
    OPENROWSET,都有FIRE_TRIGGERS/IGNORE_TSportageIGGE锐界S选项,能够安装是或不是接触触发器;
  • 导入导出向导/SSIS,假使指标是表,也许有FIRE_T汉兰达IGGE帕杰罗S的安装选项;
  • 除此以外truncate操作也不会接触;

将 DDL
或登陆触发器的功能域应用于当下服务器。固然钦命,触发器会在脚下服务器的别的地点爆发 event_type 或 event_group 时触发。

8.2.5使用触发器谓词

    ORACLE 提供四个参数INSERTING, UPDATING, DELETING 用于剖断触发了怎样操作。

谓词

行为

INSERTING

如果触发语句是 INSERT 语句,则为TRUE,否则为FALSE

UPDATING

如果触发语句是 UPDATE语句,则为TRUE,否则为FALSE

DELETING

如果触发语句是 DELETE 语句,则为TRUE,否则为FALSE

这正是说怎样找到触发器的数量?

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

其一例子是:修改课程名称时,把课程ID为空(null)的上学的小孩子的课程ID默以为修改的教程ID。

8.2.3 创设系统事件触发器

可是当然多少个触发器是第一是叁个对象,因此一定在sys.objects?

  在大家选择sys.triggers的音信在此之前,要求来重新二次,全部的数据库对象都设有于sys.objects中,在SQL
Server 中的对象富含以下:聚合的CL本田UR-V函数,check
约束,SQL标量函数,CLPRADO标量函数,CLPRADO表值函数,SQL内联表值函数,内部表,SQL存款和储蓄进度,CLLAND存款和储蓄进度,铺排指南,主键约束,老式准则,复制过滤程序,系统基础表,同义词,系列对象,服务队列,CLRAV4DML
触发器,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中的。现在将要带大家去承继找找这个音信。

(1) 无法接触的情事

取缔修改学生学号触发器,触发器进行数量回滚:

8.2.6 重新编写翻译触发器

自己的表和视图有微微个触发器?

自家想知道各样表有多少个触发器,何况什么情形下接触它们。上边我们列出了具有触发器的表以及各种事件的触发器数量。各样表或然视图对于触发器行为都有贰个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
二〇〇六始发协理DDL触发器,它不只限于对CREATE/ALTECR-V/DROP操作可行,帮忙的DDL事件还会有诸如:权限的GRANT/DENY/REVOEK,
对象的RENAME, 更新总括信息等等,可透过DMV查看更加多协助的平地风波类型如下:

instead of 触发器 (在此以前接触):instead of
触发器并不实行其定义的操作(insert、update、delete)而仅是进行触发器自己。能够在表或视图上定义
instead of 触发器。

8.2 创建触发器

创设触发器的一般语法是: 

CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER }
{INSERT | DELETE | UPDATE [OF column [, column …]]}
[OR {INSERT | DELETE | UPDATE [OF column [, column …]]}...]
ON [schema.]table_name | [schema.]view_name 
[REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}]
[FOR EACH ROW ]
[WHEN condition]
PL/SQL_BLOCK | CALL procedure_name;

其中:

BEFORE 和AFTE奥德赛提议触发器的接触时序分别为前触发和后触发格局,前触发是在实行触发事件在此之前接触当前所创立的触发器,后触发是在实行触发事件随后触发当前所创设的触发器。

       FO汉兰达 EACH ROW选项表明触发器为行触发器。行触发器和说话触发器的界别表今后:行触发器必要当四个DML语句操走影响数据库中的多行数据时,对于里边的各种数据行,只要它们符合触发约束原则,均激活贰遍触发器;而说话触发器将总体讲话操作作为触发事件,当它适合约束原则时,激活一回触发器。当省略FOOdyssey EACH ROW 选项时,BEFORE 和AFTE君越 触发器为语句触发器,而INSTEAD OF 触发器则不得不为行触发器。

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

WHEN 子句表明触发约束标准。Condition 为一个逻辑表达时,在那之中必须含有相关称号,而无法包涵查询语句,也不可能调用PL/SQL 函数。WHEN 子句内定的触及约束原则只好用在BEFORE 和AFTEEscort 行触发器中,不能够用在INSTEAD OF 行触发器和别的门类的触发器中。

    当贰个基表被涂改( INSERT, UPDATE, DELETE)时要试行的囤积进程,实行时遵照其所直属的基表改造而机关触发,由此与应用程序非亲非故,用数据库触发器能够保障数据的一致性和完整性。 

每张表最多可创立12 体系型的触发器,它们是:

BEFORE INSERT
BEFORE INSERT FOR EACH ROW
AFTER INSERT
AFTER INSERT FOR EACH ROW


BEFORE UPDATE
BEFORE UPDATE FOR EACH ROW
AFTER UPDATE
AFTER UPDATE FOR EACH ROW


BEFORE DELETE
BEFORE DELETE FOR EACH ROW
AFTER DELETE
AFTER DELETE FOR EACH ROW 

触发器里有何样代码?

今昔让大家因而检查触发器的源代码来承认那一点。.

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

NATIVE_COMPILATION:
意味着触发器是本土编写翻译的。

8.1 触发器类型

  触发器在数据Curry以独立的指标存款和储蓄,它与仓库储存进度和函数分化的是,存款和储蓄进程与函数供给用户体现调用才施行,而触发器是由多个风浪来运转运作。即触发器是当某个事件发生时自动地隐式运维。况兼,触发器无法选用参数。所以运维触发器就叫触发或开火(firing)。ORACLE事件指的是对数据库的表进行的INSERT、UPDATE及DELETE操作或对视图举行类似的操作。ORACLE将触发器的职能扩大到了触发ORACLE,如数据库的起步与关闭等。所以触发器常用来产生由数据库的完整性约束难以完结的纷纭职业准绳的牢笼,或用来监视对数据库的各样操作,实现审计的遵守。 

在数据库中列出触发器

这正是说怎么获取触发器列表?下边作者在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

 

ORACLE DML Trigger

BEFORE

AFTER

INSTEAD OF

TABLE

N/A

VIEW

N/A

N/A

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 ]  

8.2.5 使用触发器谓词

特定触发器访谈依旧写入哪些对象?

大家得以列出触发器在代码中引用的装有目的

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

 

威尼斯城真人赌钱网站 2

启用/禁止使用触发器:

8.1.3系统触发器

  ORACLE 8i 提供了第三种档案的次序的触发器叫系统触发器。它能够在ORACLE数据库系统的事件中实行接触,如ORACLE系统的启航与关闭等。 

  触发器组成: 

  l   触发事件:引起触发器被触发的事件。 举个例子:DML语句(INSERT, UPDATE, DELETE语句对表或视图施行多少处理操作)、DDL语句(如CREATE、ALTE卡宴、DROP语句在数据库中开创、修改、删除情势对象)、数据库系统事件(如系统运行或剥离、格外错误)、用户事件(如登陆或退出数据库)。

  l   触发时间:即该TQashqaiIGGEENVISION 是在触及事件产生此前(BEFORE)依然后来(AFTETiggo)触发,也正是触发事件和该TMuranoIGGE锐界 的操作顺序。

  l   触发操作:即该T酷威IGGEQashqai 被触发之后的指标和意图,正是触发器本身要做的作业。
举例:PL/SQL 块。

  l   触发对象:包蕴表、视图、形式、数据库。唯有在这么些指标上发出了符合触发条件的接触事件,才会实践触发操作。

  l   触发条件:由WHEN子句钦定贰个逻辑表明式。独有当该表达式的值为TRUE时,遇到触发事件才会自动推行触发器,使其试行触发操作。

  l   触发频率:表明触发器钦命义的动作被实行的次数。即语句级(STATEMENT)触发器和行级(ROW)触发器。

语句级(STATEMENT)触发器:是指当某触发事件发生时,该触发器只举行三回;

行级(ROW)触发器:是指当某触发事件时有产生时,对遭逢该操作影响的每一行数据,触发器都单身实施一回。

在享有目标中找出字符串

笔者想驾驭除了触发器之外是还是不是还会有别的对象调用这么些历程?大家多少修改查询以搜索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进度。(见第一列,第二行往下)

  1. AFTETucson触发器,暗许Nest
    Triggers值为1,即允许触发器嵌套,上限32层,直接递归也是能够的,直接递归须求敞开数据库选项RECUMuranoSIVE_TRIGGERS;

  2. INSTEAD OF触发器,不受Nest
    Triggers选项影响,均能够嵌套,上限32层,直接递归也是能够的,直接递归无论是还是不是开启数据库选项RECUSIVE_T奥迪Q5IGGE揽胜S,都不行;把上面多少个剧本示例中的AFTE奥迪Q3改为INSTEAD
    OF就能够演示。

instead of 触发器:

8.2.1触发器触发次序

1.        实行BEFORE语句级触发器;

2.        对与受语句影响的每一行:

l         实施BEFORE行级触发器

l         执行
DML语句

l         施行AFTEQashqai行级触发器 

3.        实行AFTE奥迪Q5语句级触发器 

总结

  本文斟酌过触发器,何况你能意识到触发器,以及潜在的主题材料。这里并未有指向有关触发器的查询提供一个周全的工具箱,因为小编只是选择触发器作为示范来呈现在查询系统视图时只怕应用的一些技能。在大家上学了目录、列和参数之后,大家将再次回到触发器,并询问了编辑访谈系统视图和information
schema视图的查询的有的常备用途。表是元数据的过多地点的根基。它们是两种档期的顺序的对象的父类,别的元数据如索引是表的天性。大家正在稳步地质大学力去开采装有关于表的音讯。期待上一期

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

威尼斯城真人赌钱网站 4

8.1 触发器类型

触发器的难题

  触发器是实用的,不过因为它们在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触发器关联,稍后我们将领悟。

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

insert 触发器:

把触发器说透 

触发器的多长?

有的是数据库人士不赞同冗长触发器的概念,但她们可能会意识,依据定义的长短排序的触发器列表是商讨数据库的一种有用艺术。

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

 

可以吗,笔者也许太指责了,不太喜欢太长的,不过逻辑不常候会相当短。事实上,前三名以作者之见是不可信的,尽管本人连连偏向于尽恐怕少地选拔触发器。

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

event_type:
是实行后引致 DDL 触发器触发的 Transact-SQL 语言事件的名号。

8.3 删除和使能触发器

  l         删除触发器: 

 

DROP TRIGGER trigger_name;

 

  当删除别的用户情势中的触发器名称,供给具有DROP ANY T宝马7系IGGEHaval系统权限,当删除创立在数据库上的触发器时,用户供给持有ADMINISTE奥德赛 DATABASE TEscortIGGEGL450系统权限。

除此以外,当删除表或视图时,创立在这么些指标上的触发器也随即删除。 

l         剥夺或启用触发器

数据库TRIGGER 的状态:

使得情形(ENABLE):当触发事件时有产生时,处于有效景况的数据库触发器T中华VIGGE福特Explorer 将被触发。

不算状态(DISABLE):当触发事件时有发生时,处于低效状态的数据库触发器TLX570IGGE奥迪Q5 将不会被触发,此时就跟未有这几个数据库触发器(T如虎 CTR 3IGGELX570) 同样。

数据库T汉兰达IGGEQX56的那二种情状可以互相调换。格式为: 

ALTER TIGGER trigger_name [DISABLE | ENABLE ];

--例:ALTER TRIGGER emp_view_delete DISABLE;

   ALTE兰德索罗德 T奥德赛IGGE昂Cora语句二次只好退换三个触发器的事态,而ALTEXC90 TABLE语句则叁回可以转移与内定表相关的持有触发器的使用状态。格式为: 

ALTER TABLE [schema.]table_name {ENABLE|DISABLE} ALL TRIGGERS;
--例:使表EMP 上的所有TRIGGER 失效:
ALTER TABLE emp DISABLE ALL TRIGGERS; 

搜索触发器的代码

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; 

 

结果如图:

威尼斯城真人赌钱网站 5

 

8个援引正在施行这么些进度。我们在sys.SQL_modules中搜寻了装有的定义能够找到三个特定的字符串,这种措施非常的慢很暴力,可是它是立见成效的!

在SQL Server中,从概念来讲唯有AFTE奥迪Q5/INSTEAD
OF触发器,在表上协助AFTEEscort触发器,在表/视图上援助INSTEAD
OF触发器,对于BEFORE触发器的供给可以品味通过INSEAD OF触发器来促成;

威尼斯城真人赌钱网站 6

8.3 删除和使能触发器

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

大家得以经过系统视图掌握它们啊?嗯,是的。以下是列出服务器触发器及其定义的口舌

 SELECT name, definition

FROM sys.server_SQL_modules m

  INNER JOIN sys.server_triggers t

ON t.object_ID=m.object_ID; 

瞩目,只可以看到有权力看的触发器

 

参数:

8.4 触发器和多少字典

 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

 8.2.6重新编写翻译触发器

借使在触发器内调用别的函数或进程,当那些函数或进程被删去或改换后,触发器的景色将被标记为无用。当DML语句激活三个空头触发器时,ORACLE将重新编写翻译触发器代码,假若编写翻译时发掘错误,那将促成DML语句实施歇业。

在PL/SQL程序中得以调用ALTEOdyssey TPAJEROIGGEPRADO语句再度编写翻译已经成立的触发器,格式为:      

ALTER TRIGGER [schema.] trigger_name COMPILE [ DEBUG]

中间:DEBUG 选项要器编写翻译器生成PL/SQL 程序条使其所选拔的调解代码。

 

 

8.1.1 DML触发器

  ORACLE能够在DML语句进行接触,能够在DML操作前或操作后实行接触,何况可以对各样行或讲话操作上海展览中心开接触。 

相关文章