Oracle中的触发器trigger
触发器是指被隐含执行的存储过程
一、创建DML触发器(before/after)
1、行触发器:
当一个DML操作影响DB中的多行时,对于其中复合触发条件的每行均触发一次(for each row)
例1: 建立一个触发器, 当职工表 emp 表被删除一条记录时,把被删除记录写到职工表删除日志表中去。
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;
例2:级联更新:利用行触发器实现级联更新。在修改了主表regions中的region_id之后(AFTER),级联的、自动的更新子表countries表中原来在该地区的国家的region_id
CREATE OR REPLACE TRIGGER tr_reg_cou AFTER update OF region_id ON regions FOR EACH ROW BEGIN DBMS_OUTPUT.PUT_LINE('旧的region_id值是'||:old.region_id ||'、新的region_id值是'||:new.region_id); UPDATE countries SET region_id = :new.region_id WHERE region_id = :old.region_id; END;
例3:限定只对部门号为80的记录进行行触发器操作。
CREATE OR REPLACE TRIGGER tr_emp_sal_comm BEFORE UPDATE OF salary, commission_pct OR DELETE ON HR.employees FOR EACH ROW WHEN (old.department_id = 80) BEGIN CASE WHEN UPDATING ('salary') THEN IF :NEW.salary < :old.salary THEN RAISE_APPLICATION_ERROR(-20001, '部门80的人员的工资不能降'); END IF; WHEN UPDATING ('commission_pct') THEN IF :NEW.commission_pct < :old.commission_pct THEN RAISE_APPLICATION_ERROR(-20002, '部门80的人员的奖金不能降'); END IF; WHEN DELETING THEN RAISE_APPLICATION_ERROR(-20003, '不能删除部门80的人员记录'); END CASE; END; /* 实例: UPDATE employees SET salary = 8000 WHERE employee_id = 177; DELETE FROM employees WHERE employee_id in (177,170); */
2、语句触发器:
将整个DML语句作为触发条件,当它符合约束条件时,激活一次触发器。
限制对Departments表修改(包括INSERT,DELETE,UPDATE)的时间范围,即不允许在非工作时间修改departments表。
CREATE OR REPLACE TRIGGER tr_dept_time BEFORE INSERT OR DELETE OR UPDATE ON departments BEGIN IF (TO_CHAR(sysdate,'DAY') IN ('星期六', '星期日')) OR (TO_CHAR(sysdate, 'HH24:MI') NOT BETWEEN '08:30' AND '18:00') THEN RAISE_APPLICATION_ERROR(-20001, '不是上班时间,不能修改departments表'); END IF; END;
二、创建替代(instead of )触发器
用于对视图(没有指定WITH CHECK OPTION选项)的DML触发。
- 只能被创建在视图上。
- 不能指定BEFORE 或 AFTER选项。
- FOR EACH ROW子可是可选的,即INSTEAD OF触发器只能在行级上触发、或只能是行级触发器,没有必要指定。
创建INSTEAD_OF触发器来为 DELETE 操作执行所需的处理,即删除EMP表中所有基准行:
CREATE OR REPLACE TRIGGER emp_view_delete INSTEAD OF DELETE ON emp_view FOR EACH ROW BEGIN DELETE FROM emp WHERE deptno= :old.deptno; END emp_view_delete;
三、创建系统事件触发器(on schema/on database)
1、当建立在模式(SCHEMA)之上时,只有模式所指定用户的DDL操作和它们所导致的错误才激活触发器, 默认时为当前用户模式。
例1:创建触发器,存放有关事件信息。
--创建触犯发器 CREATE OR REPLACE TRIGGER tr_ddl AFTER DDL ON SCHEMA BEGIN INSERT INTO ddl_event VALUES (systimestamp,ora_sysevent, ora_login_user, ora_dict_obj_type, ora_dict_obj_name); END tr_ddl;
2、当建立在数据库(DATABASE)之上时,该数据库所有用户的DDL操作和他们所导致的错误,以及数据库的启动和关闭均可激活触发器。
要在数据库之上建立触发器时,要求用户具有ADMINISTER DATABASE TRIGGER权限。
例1:创建登录触发器。
CREATE OR REPLACE TRIGGER tr_logon AFTER LOGON ON DATABASE BEGIN INSERT INTO log_event (user_name, address, logon_date) VALUES (ora_login_user, ora_client_ip_address, systimestamp); END tr_logon;
四、重新编译触发器
ALTER TRIGGER trigger COMPILE
删除触发器:当删除表或视图时,建立在这些对象上的触发器也随之删除。
DROP TRIGGER trigger_name;
禁用或启用触发器
ALTER TRIGGER emp_view_delete DISABLE| ENABLE; --使表EMP 上的所有TRIGGER 失效: ALTER TABLE emp DISABLE ALL TRIGGERS;
触发器和数据字典
相关数据字典:USER_TRIGGERS、ALL_TRIGGERS、DBA_TRIGGERS
Oracle 字符集
到此这篇关于Oracle触发器trigger的文章就介绍到这了。希望对大家的学习有所帮助,也希望大家多多支持我们。