Oracle 笔记（九）、触发器

小咪咪 2022-05-19 13:52 226阅读 0赞

[一、行级触发器][Link 1]  
[二、语句级触发器][Link 2]  
[三、替换触发器][Link 3]  
[四、模式级触发器][Link 4]  
[五、数据库级触发器][Link 5]  
  
  
  
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;

转载：[http://www.blogjava.net/kiant/articles/236787.html][http_www.blogjava.net_kiant_articles_236787.html]

[Link 1]: http://www.blogjava.net/kiant/articles/236787.html#1
[Link 2]: http://www.blogjava.net/kiant/articles/236787.html#2
[Link 3]: http://www.blogjava.net/kiant/articles/236787.html#3
[Link 4]: http://www.blogjava.net/kiant/articles/236787.html#4
[Link 5]: http://www.blogjava.net/kiant/articles/236787.html#5
[http_www.blogjava.net_kiant_articles_236787.html]: http://www.blogjava.net/kiant/articles/236787.html