你的位置:首页 > 数据库

[数据库]Oracle学习笔记十三 触发器

简介

触发器是当特定事件出现时自动执行的存储过程,特定事件可以是执行更新的DML语句和DDL语句,触发器不能被显式调用。
 
触发器的功能:
1.自动生成数据
2.自定义复杂的安全权限
3.提供审计和日志记录
4.启用复杂的业务逻辑
 

创建触发器的语法

CREATE [OR REPLACE] TRIGGER trigger_nameAFTER | 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 --为一个引用以前的列值;这两个变量只有在使用了关键字"FOREACH 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_itemfileAFTER INSERTON itemfileFOR EACH ROWBEGIN 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_EMPBEFOR INSERT OR UPDATE OR DELETE ON EMP2BEGIN 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 ROWBEGIN--函数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_masterBEGIN  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 ROWBEGIN  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 SCHEMABEGIN  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_TRIGGERSWHERE TABLE_NAME='EMP';SELECT TRIGGER_TYPE, TRIGGERING_EVENT, WHEN_CLAUSEFROM USER_TRIGGERSWHERE TRIGGER_NAME = 'BIU_EMP_DEPTNO';