ORACLE触发器

触发器

 

q      触发器是当特定事件出现时自动执行的存储过程

q      特定事件可以是执行更新的DML语句和DDL语句

q      触发器不能被显式调用

q      触发器的功能：

q      自动生成数据

q      自定义复杂的安全权限

q      提供审计和日志记录

q      启用复杂的业务逻辑

 

创建触发器的语法：

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;

 

触发器由三部分组成：

q      触发器语句（事件）

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

q      触发器限制

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

q      触发器操作（主体）

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

 

创建触发器 CREATE OR REPLACE TRIGGER first_emp_trg AFTER INSERT ON emp FOR EACH ROW BEGIN   IF (:NEW.sal < 10000) THEN     DBMS_OUTPUT.PUT_LINE('less 10000');   ELSE     DBMS_OUTPUT.PUT_LINE('ge 10000');   END IF; END;

SQL> insert into emp(empno,ename,sal) values(8102,'HuangPei',10000);   ge 10000   1 row inserted   SQL> insert into emp(empno,ename,sal) values(8103,'HuangPei',100);   less 10000   1 row inserted

 

查询触发器 SQL> select trigger_name,trigger_type,triggering_event,status from user_triggers;   TRIGGER_NAME   TRIGGER_TYPE      TRIGGERING_EVENT   STATUS                                                              -------------  ---------------- ----------------- -------- FIRST_EMP_TRG  AFTER EACH ROW    INSERT              ENABLED                                                                SQL> select OBJECT_NAME,OBJECT_TYPE,STATUS from user_objects where object_type='TRIGGER';   OBJECT_NAME           OBJECT_TYPE         STATUS                                                       ------------------- -----------------  ------- FIRST_EMP_TRG         TRIGGER              VALID                                                   SQL> select * from user_source where type='TRIGGER';   NAME             TYPE        LINE TEXT -------------- --------- --------------------------------------- FIRST_EMP_TRG  TRIGGER     1 TRIGGER first_emp_trg FIRST_EMP_TRG  TRIGGER     2 AFTER INSERT FIRST_EMP_TRG  TRIGGER     3 ON emp FIRST_EMP_TRG  TRIGGER     4 FOR EACH ROW FIRST_EMP_TRG  TRIGGER     5 BEGIN FIRST_EMP_TRG  TRIGGER     6   IF (:NEW.sal < 10000) THEN FIRST_EMP_TRG  TRIGGER     7     DBMS_OUTPUT.PUT_LINE('less 10000'); FIRST_EMP_TRG  TRIGGER     8   ELSE FIRST_EMP_TRG  TRIGGER     9     DBMS_OUTPUT.PUT_LINE('ge 10000'); FIRST_EMP_TRG  TRIGGER     10   END IF; FIRST_EMP_TRG  TRIGGER     11 END;   11 rows selected

 

CREATE OR REPLACE TRIGGER second_emp_trg after insert or delete or update ON emp FOR EACH ROW BEGIN   IF inserting THEN     DBMS_OUTPUT.PUT_LINE('insert');   END IF;   IF deleting THEN     DBMS_OUTPUT.PUT_LINE('delete');   END IF;   IF updating THEN     DBMS_OUTPUT.PUT_LINE('update');   END IF; END;   SQL> insert into emp(empno,ename) values(8105,'HP');   insert   1 row inserted   SQL> delete from emp where empno>=8101;   delete delete delete delete delete   5 rows deleted   SQL> update emp set sal=10000;   update update update update update update update update update update update update update update update   15 rows updated

 

CREATE OR REPLACE TRIGGER second_emp_trg after insert or delete or update ON emp --FOR EACH ROW BEGIN   IF inserting THEN     DBMS_OUTPUT.PUT_LINE('insert');   END IF;   IF deleting THEN     DBMS_OUTPUT.PUT_LINE('delete');   END IF;   IF updating THEN     DBMS_OUTPUT.PUT_LINE('update');   END IF; END;   SQL> insert into emp(empno,ename) values(8105,'HP');   insert   1 row inserted   SQL> delete from emp where empno>=8105;   delete   1 row deleted   SQL> update emp set sal=20000;   update   15 rows updated

 

CREATE OR REPLACE TRIGGER third_emp_trg instead of insert ON emp BEGIN   null; END;   SQL> ed SQL> /   CREATE OR REPLACE TRIGGER third_emp_trg instead of insert ON emp BEGIN   null; END;   ORA-25002: 无法在表中创建 INSTEAD OF 触发器   CREATE OR REPLACE TRIGGER third_emp_trg instead of insert ON emp_view BEGIN   null; END;   SQL> ed SQL> /   Trigger created

 

CREATE OR REPLACE TRIGGER first_emp_trg after update ON emp BEGIN   update emp set sal=10000; END;   SQL> update emp set sal=5000;   update emp set sal=5000 ORA-00036: 超过递归 SQL (50) 级的最大值 ORA-00036: 超过递归 SQL (50) 级的最大值 ORA-00036: 超过递归 SQL (50) 级的最大值 ORA-06512: 在"SCOTT.FIRST_EMP_TRG", line 2 ORA-04088: 触发器 'SCOTT.FIRST_EMP_TRG' 执行过程中出错 ORA-06512: 在"SCOTT.FIRST_EMP_TRG", line 2 ORA-04088: 触发器 'SCOTT.FIRST_EMP_TRG' 执行过程中出错 ORA-06512: 在"SCOTT.FIRST_EMP_TRG", line 2 ORA-04088: 触发器 'SCOTT.FIRST_EMP_TRG' 执行过程中出错 ORA-06512: 在"SCOTT.FIRST_EMP_TRG", line 2 ORA-04088: 触发器 'SCOTT.FIRST_EMP_TRG' 执行过程中出错 ORA-06512: 在"SCOTT.FIRST_EMP_TRG", line 2 ORA-04088: 触发器 'SCOTT.FIRST_EMP_TRG' 执行过程中出错 ORA-06512: 在"SCOTT.FIRST_EMP_TRG", line 2 ORA-04088: 触发器 'SCOTT.FIRST_EMP_TRG' 执行过程中出错 ORA-06512: 在"SCOTT.FIRST_EMP_TRG", line 2 ORA-04088: 触发器 'SCOTT.FIRST_EMP_TRG' 执行过程中出错 ORA-06512: 在"SCOTT.FIRST_EMP_TRG", line 2 ORA-04088: 触发器 'SCOTT.FIRST_EMP_TRG' 执行过程中出错 ORA-06512: 在"SCOTT.FIRST_EMP_TRG", line 2 ORA-04088: 触发器 'SCOTT.FIRST_EMP_TRG' 执行过程中出错 ORA-06512: 在"SCOTT.F

CREATE OR REPLACE TRIGGER first_emp_trg after update ON emp FOR EACH ROW BEGIN   update emp set sal=10000; END;   SQL> update emp set sal=5000;   update emp set sal=5000   ORA-04091: 表 SCOTT.EMP 发生了变化，触发器/函数不能读 ORA-06512: 在"SCOTT.FIRST_EMP_TRG", line 2 ORA-04088: 触发器 'SCOTT.FIRST_EMP_TRG' 执行过程中出错