Oracle 笔记（九）、触发器

1、触发器一旦创建就会立刻生效，有时可能需要临时禁用触发器，最常见的原因就是涉及数据加载。
ALTER TRIGGER trigger_name [ENABLE | DISABLE];

2、Oracle 触发器里两个重要的内存逻辑表 :old 和 :new。:old 表保存的是在DML语句之前的数据，:new 表保存的是在DML语句创建的数据。
old new
INSERT - √
DELETE √ -
UPDATE √ √

3、在触发器语句中不能显式的提交/回滚事务。




一、行级触发器
行级触发器对 DML 语句影响的每个行执行一次。主要应用保持数据完整性。

例：两表级联更新，修改部门表编号的同时也修改员工表的部门编号：
CREATE OR REPLACE TRIGGER update_dept
/* 行级触发器，在更新部门表操作后触发 */
AFTER UPDATE ON deptment
FOR EACH ROW
BEGIN
/* new、old 表的有效利用，把旧表的id列值 更新为 新表的id列值 */
UPDATE emp SET id=:new.id WHERE id=:old.id;
END;

UPDATE deptment SET id='yy' WHERE id='01';

SELECT * FROM deptment;
SELECT * FROM emp;


插入时利用触发器+序列实现整型字段的自增：
CREATE OR REPLACE TRIGGER set_no
BEFORE INSERT ON auto
FOR EACH ROW
DECLARE
sn number(5);
BEGIN
/* 触发器预处理序列的值 */
SELECT myseq.nextval INTO sn FROM dual;
:NEW.a := sn;
END;

INSERT INTO auto VALUES(21,'dtt');
SELECT * FROM auto;
/* 插入时表a列由触发器产生的值替代用户的输入 */

二、语句级触发器
只与语句有关，与行无关，不涉及数据完整性问题。

例如：利用触发器记录，记录某表中用户的操作（日志处理）。
CREATE OR REPLACE TRIGGER dm1_aa
AFTER INSERT OR DELETE OR UPDATE ON aa
BEGIN
IF INSERTING THEN
INSERT INTO mylog VALUES(user,sysdate,'I');
ELSEIF DELETING THEN
INSERT INTO mylog VALUES(user,sysdate,'D');
ELSE
INSERT INTO mylog VALUES(user,sysdate,'U');
END IF;
END;

ps.能否记录多个表？"after insert or delete or update on t1,t2" 出错。



三、替换触发器
解决Oracle视图中多表更新的限制，只能在视图中使用，属于行级触发器。

例如：在视图中插入新的部门同时插入其所属的新员工：
CREATE OF REPLACE TRIGGER tr_v_e_d
/* 在视图上创建替换触发器 */
INSTEAD OF INSERT ON v_emp_dept
FOR EACH ROW
BEGIN
/* 替换触发器先插入部门表的信息，然后再插入其所属员工表的信息 */
INSERT INTO deptment VALUES(:new.id, :new.name);
INSERT INTO emp(eid, ename, sex, id) VALUES(:new.eid, :new.ename, :new.sex, :new.d);
END;

INSERT INTO v_emp_dept VALUES('456', 'test', 'f', '33', 'hg');
SELECT * FROM v_emp_dept;

四、模式级触发器
可以在模式对象的操作上建立的触发器，如 CREATE、ALTER、DROP、GRANT、REVOKE 和 TRUNCATE 等DDL语句。
[BEFORE | AFTER] trigger_event ON [schema.]SCHEMA

例如：对用户所删除的所有对象进行日志记录
CREATE OR REPLACE TRIGGER log_drop_obj
AFTER DROP ON SCHEMA
BEGIN
/* 记录操作类型、操作对象、操作时间 */
INSERT INTO dropped_obj VALUES(ORA_DICT_OBJ_NAME, ORA_DICT_TYPE, SYSDATE);
END;

CREATE TABLE for_drop(x char);
DROP TABLE for_drop;

SELECT * FROM dropped_obj;

五、数据库级触发器
可以创建在数据库事件上的触发器，包括启动、关闭、服务器错误、登录和注销等。这些事件都是实例范围内的，不与特定的表或视图关联。

CREATE OR REPLACE TRIGGER system_startup
AFTER STARTUP ON DATABASE
BEGIN
...
END;