Oracle学习笔记十三 触发器

简介

触发器是当特定事件出现时自动执行的存储过程，特定事件可以是执行更新的DML语句和DDL语句，触发器不能被显式调用。

 

触发器的功能：

1.自动生成数据

2.自定义复杂的安全权限

3.提供审计和日志记录

4.启用复杂的业务逻辑

 

创建触发器的语法

CREATE [OR REPLACE] TRIGGER trigger_name
AFTER | BEFORE | INSTEAD OF
[INSERT] [[OR] UPDATE [OF column_list]] 
[[OR] DELETE]
ON table_or_view_name
[REFERENCING {OLD [AS] old / NEW [AS] new}]
[FOR EACH ROW]
[WHEN ( condition ) ]
pl/sql_block;

:new --为一个引用最新的列值;
:old --为一个引用以前的列值; 这两个变量只有在使用了关键字 "FOR EACH ROW"时才存在.

且update语句两个都有,而insert只有:new ,delect 只有:old;

 

触发器由三部分组成：

触发器语句（事件）

　　定义激活触发器的 DML 事件和 DDL 事件

触发器限制

　　执行触发器的条件，该条件必须为真才能激活触发器

触发器操作（主体）

　　包含一些 SQL 语句和代码，它们在发出了触发器语句且触发限制的值为真时运行

 

触发器语句

CREATE OR REPLACE TRIGGER trig_sal
    AFTER UPDATE OF empsal ON salary_records  --在更新 emp_sal 列之后激活触发器
    …

触发器限制

        …
    FOR EACH ROW
    WHEN (NEW.empsal>OLD.empsal)    --只有在WHEN子句中的条件得到满足时，才激活trig_sal 触发器
    DECLARE
    Sal_diff NUMBER;
    …    

 

触发器操作

        …
    BEGIN
      sal_diff:=:NEW.empsal-:OLD.empsal;    --如果WHEN子句中的条件得到满足，将执行BEGIN 块中的代码
      DBMS_OUTPUT.PUT_LINE(‘工资差额：’sal_diff);
    END;

 

 

BEFORE 触发器的工作原理

 

 

 

 创建触发器

CREATE OR REPLACE TRIGGER aiu_itemfile
AFTER INSERT
ON itemfile
FOR EACH ROW
BEGIN
  IF (:NEW.qty_hand = 0) THEN
    DBMS_OUTPUT.PUT_LINE('警告：已插入记录，但数量为零');
  ELSE
    DBMS_OUTPUT.PUT_LINE(‘已插入记录');
  END IF;
END;

查看表的触发器

select * from all_triggers where table_name =upper('tbname')   

 

CREATE OR REPLACE TRIGGER TR_SEC_EMP
BEFOR INSERT  OR UPDATE OR DELETE ON EMP2
BEGIN 
 IF TO_CHAR(SYSDATE,'DY', 'nls_date_language=AMERICAN') IN ('SAT', 'SUN') THEN 
RAISE_APPLICATION_ERROR(-20002,’禁止修改数据！’);
END IF;
END;

 

工资一般来说都是往上调整，写个触发器禁止降低工资？

create or replace trigger guo_trigger----创建触发器 
before update on emp2 ----指明触发器时机 
for each row ----行触发器标识 
when (new.sal<old.sal ) ----触发条件 
begin 
raise_application_error(-20500,'不能给员工减少工资'); 
end; 

 

 触发器类型

触发器的类型有：

 

 

DDL 触发器

　　在模式中执行 DDL 语句时执行

数据库级触发器

　　在发生打开、关闭、登录和退出数据库等系统事件时执行

DML 触发器

　　在对表或视图执行DML语句时执行

语句级触发器

　　无论受影响的行数是多少，都只执行一次

行级触发器

 　　对DML语句修改的每个行执行一次

INSTEAD OF 触发器

　　用于用户不能直接使用 DML 语句修改的视图

 

综述：

触发器组成: 

l         触发事件：引起触发器被触发的事件。 例如：DML语句(INSERT, UPDATE, DELETE语句对表或视图执行数据处理操作)、DDL语句（如CREATE、ALTER、DROP语句在数据库中创建、修改、删除模式对象）、数据库系统事件（如系统启动或退出、异常错误）、用户事件（如登录或退出数据库）。

l         触发时间：即该TRIGGER 是在触发事件发生之前（BEFORE）还是之后(AFTER)触发，也就是触发事件和该TRIGGER 的操作顺序。

l         触发操作：即该TRIGGER 被触发之后的目的和意图，正是触发器本身要做的事情。 例如：PL/SQL 块。

l         触发对象：包括表、视图、模式、数据库。只有在这些对象上发生了符合触发条件的触发事件，才会执行触发操作。

l         触发条件：由WHEN子句指定一个逻辑表达式。只有当该表达式的值为TRUE时，遇到触发事件才会自动执行触发器，使其执行触发操作。

l         触发频率：说明触发器内定义的动作被执行的次数。即语句级(STATEMENT)触发器和行级(ROW)触发器。

语句级(STATEMENT)触发器：是指当某触发事件发生时，该触发器只执行一次；行级(ROW)触发器：是指当某触发事件发生时，对受到该操作影响的每一行数据，触发器都单独执行一次。

触发器不接受参数。

l         一个表上最多可有12个触发器，但同一时间、同一事件、同一类型的触发器只能有一个。并各触发器之间不能有矛盾。

l         在一个表上的触发器越多，对在该表上的DML操作的性能影响就越大。

l        触发器最大为32KB。若确实需要，可以先建立过程，然后在触发器中用CALL语句进行调用。

l         在触发器的执行部分只能用DML语句（SELECT、INSERT、UPDATE、DELETE），不能使用DDL语句（CREATE、ALTER、DROP）。

l         触发器中不能包含事务控制语句(COMMIT，ROLLBACK，SAVEPOINT)。因为触发器是触发语句的一部分，触发语句被提交、回退时，触发器也被提交、回退了。

l         在触发器主体中调用的任何过程、函数，都不能使用事务控制语句。

l         在触发器主体中不能申明任何Long和blob变量。新值new和旧值old也不能向表中的任何long和blob列。

l         不同类型的触发器(如DML触发器、INSTEAD OF触发器、系统触发器)的语法格式和作用有较大区别。

 

行级触发器

CREATE TABLE TEST_TRG
          (ID NUMBER, NAME VARCHAR2(20));
CREATE SEQUENCE SEQ_TEST;

CREATE OR REPLACE TRIGGER BI_TEST_TRG
　　BEFORE INSERT OR UPDATE OF ID
　　ON TEST_TRG
　　FOR EACH ROW
BEGIN
--函数UPDATING, DELETING判断触发器是由哪个操作触发的 
     IF INSERTING THEN SELECT SEQ_TEST.NEXTVAL INTO :NEW.ID FROM DUAL;
     ELSE
       RAISE_APPLICATION_ERROR(-20020, '不允许更新ID值！');
     END IF;
END;

 

修改数据前备份员工工资

CREATE OR REPLACE TIGGER TR_SAL_BACK AFFTER UPDATE OF SAL ON EMP2 FOR EACH ROW 
DECLARE 
　　V_TEM INT ;
BEGIN 
　　SELECT COUNT(*) INTO V_TEMP FROM NEW_BACK WHERE ENAME =:OLD.ENAME;
　　IF V_TEMP =0 THEN 
　　　　INSERT INTO NEW_BACK VALUES (:OLD.ENAME,:OLD.SAL, :NEW.SAL,SYSDATE);
　　ELSE 
　　　　UPDATE NEW_BACK VALUES  SET OLDSAL=:OLDSAL, NEWSAL=:NEW.SAL,TIME=SYSDATE WHERE NAME=:OLD.ENAME;
　　END IF; 
END;

 

建立一个触发器, 当职工表 emp 表被删除一条记录时，把被删除记录写到职工表删除日志表中去。

CREATE TABLE emp_his AS SELECT * FROM EMP WHERE 1=2; 

CREATE OR REPLACE TRIGGER tr_del_emp BEFORE 
DELETE --指定触发时机为删除操作前触发
   ON scott.emp 
   FOR EACH ROW   --说明创建的是行级触发器 
BEGIN
   --将修改前数据插入到日志记录表 del_emp ,以供监督使用。
   INSERT INTO emp_his(deptno , empno, ename , job ,mgr , sal , comm , hiredate )
       VALUES( :old.deptno, :old.empno, :old.ename , :old.job,:old.mgr, :old.sal, :old.comm, :old.hiredate );
END;

DELETE emp WHERE empno=7788;
DROP TABLE emp_his;
DROP TRIGGER del_emp;

语句级触发器

CREATE OR REPLACE TRIGGER trgdemo
   AFTER INSERT OR UPDATE OR DELETE
   ON order_master
BEGIN
   IF UPDATING THEN
     DBMS_OUTPUT.PUT_LINE('已更新 ORDER_MASTER 中的数据');
   ELSIF DELETING THEN
     DBMS_OUTPUT.PUT_LINE('已删除 ORDER_MASTER 中的数据');
   ELSIF INSERTING THEN
     DBMS_OUTPUT.PUT_LINE('已在 ORDER_MASTER 中插入数据');
   END IF;
END;

 

行级与语句区别

行触发器和语句触发器的区别表现在：行触发器要求当一个DML语句操走影响数据库中的多行数据时，对于其中的每个数据行，只要它们符合触发约束条件，均激活一次触发器；而语句触发器将整个语句操作作为触发事件，当它符合约束条件时，激活一次触发器。当省略FOR EACH ROW 选项时，BEFORE 和AFTER 触发器为语句触发器，而INSTEAD OF 触发器则只能为行触发器。

INSTEAD OF 触发器

CREATE OR REPLACE TRIGGER upd_ord_view
　　INSTEAD OF UPDATE ON ord_view
　　FOR EACH ROW
BEGIN
　　UPDATE order_master
　　SET vencode=:NEW.vencode 
　　　　WHERE orderno = :NEW.orderno;
　　DBMS_OUTPUT.PUT_LINE('已激活触发器');
END;

 

 模式触发器

CREATE TABLE dropped_obj 
(
　　obj_name VARCHAR2(30),
　　obj_type VARCHAR2(20),
　　drop_date DATE
);


CREATE OR REPLACE TRIGGER log_drop_obj
　　AFTER DROP ON SCHEMA
BEGIN
　　INSERT INTO dropped_obj
　　VALUES( ORA_DICT_OBJ_NAME, ORA_DICT_OBJ_TYPE, SYSDATE);
END;

 

 启用、禁用和删除触发器

启用和禁用触发器

ALTER TRIGGER aiu_itemfile DISABLE;

ALTER TRIGGER aiu_itemfile ENABLE;

 

删除触发器

DROP TRIGGER aiu_itemfile;

 查看有关触发器的信息

USER_TRIGGERS 数据字典视图包含有关触发器的信息

SELECT TRIGGER_NAME FROM USER_TRIGGERS
WHERE TABLE_NAME='EMP';



SELECT TRIGGER_TYPE, TRIGGERING_EVENT, WHEN_CLAUSE
FROM USER_TRIGGERS
WHERE TRIGGER_NAME = 'BIU_EMP_DEPTNO';