Oracle开发基础-触发器

IT小Chen 2021-04-13

510

---参考：《Oracle 11g SQL和 PLSQL从入门到精通》

一：DML触发器

1.1语句级别触发器

1.2 行级触发器

1.3 组合触发器

1.4 DML触发器开发示例

二：INSTEND OF触发器

三：事件触发器

四：维护触发器

一 DML触发器

1.1 语句级触发器

语句级触发器是指当执行DML语句时被隐含执行的触发器。

如果执行了相关的DML语句，那么会执行该触发器的相应代码。

语法如下：

CREATE [OR REPLACE] TRIGGER trigger_name
  {BEFORE | AFTER} event1 [OR event2 OR event3]
ON table_name
PL/SQL block;

1.1.1建立BEFORE语句触发器

BEFORE语句触发器是指在执行DML语句之前被触发的触发器。

下面禁止在周六、周日改变EMP表数据为例。

select to_char(sysdate,'DY') FROM dual;

CREATE OR REPLACE TRIGGER tr_sec_emp
  BEFORE INSERT OR UPDATE OR DELETE ON emp
BEGIN
  IF to_char(sysdate, 'DY', 'nls_date_language=AMERICAN') IN ('SAT', 'SUN') THEN
    raise_application_error(-20001,
                            'Employee information cannot be changed on a rest day!');
  END IF;
END;
/

SQL> UPDATE emp SET sal=sal*1.1 WHERE deptno=10;
UPDATE emp SET sal=sal*1.1 WHERE deptno=10
       *
ERROR at line 1:
ORA-20001: Employee information cannot be changed on a rest day!
ORA-06512: at "SCOTT.TR_SEC_EMP", line 3
ORA-04088: error during execution of trigger 'SCOTT.TR_SEC_EMP'
SQL> rollback;

1.1.2建立AFTER语句触发器

AFTER语句触发器是指在执行DML语句之后被触发的触发器。

下面以审计在EMP表上执行UPADTE语句的客户主机名、语句以及执行时间为例。

CREATE TABLE aud_upd_table(host VARCHAR2(30),statement VARCHAR2(100),exectime DATE);

CREATE OR REPLACE TRIGGER tr_upd_emp
  AFTER UPDATE ON emp
DECLARE
  sql_txt ora_name_list_t;
  v_stmt  VARCHAR2(100);
  n       BINARY_INTEGER;
BEGIN
  n := ora_sql_txt(sql_txt);
  FOR i IN 1 .. n LOOP
    v_stmt := v_stmt || sql_txt(i);
  END LOOP;
  INSERT INTO aud_upd_table
  VALUES
    (sys_context('userenv', 'host'), v_stmt, SYSDATE);
END;
/

SQL> UPDATE emp SET sal=2000.00 WHERE empno=7369;
UPDATE emp SET sal=2000.00 WHERE empno=7369
       *
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SCOTT.TR_UPD_EMP", line 7
ORA-04088: error during execution of trigger 'SCOTT.TR_UPD_EMP'

创建错误，提示FOR i IN 1..N LOOP错误，提示n不是数值。

1.1.3 使用条件谓词

当DML触发器包含多个触发事件(INSERT、UPDATE、DELETE)时，为了区分具体的触发事件，可以使用以下条件谓词：

INSERTING:当触发事件是INSERT语句时，返回TRUE,否则返回FALSE。

UPDATING:当触发事件是UPDATE语句时，返回TRUE,否则返回FALSE。

DELETING:当触发事件是DELETE语句时，返回TRUE,否则返回FALSE。

下面以防止周六、周日在EMP表上执行DML，根据不同谓词显示不同错误信息，并建立触发器tr_sec_emp为例，说明使用条件谓词的方法。

CREATE OR REPLACE TRIGGER tr_sec_emp
  BEFORE INSERT OR UPDATE OR DELETE ON emp
BEGIN
  IF to_char(sysdate, 'DY', 'nls_date_language=AMERICAN') in ('SAT', 'SUN') THEN
    CASE
      WHEN INSERTING THEN
        raise_application_error(-20001, '');
      WHEN UPDATING THEN
        raise_application_error(-20002, '');
      WHEN DELETING THEN
        raise_application_error(-20003, '');
    END CASE;
  END IF;
END;
/

SQL> DELETE FROM emp WHERE empno=7788;
DELETE FROM emp WHERE empno=7788
            *
ERROR at line 1:
ORA-20003: Do not delete data on a rest day!
ORA-06512: at "SCOTT.TR_SEC_EMP", line 9
ORA-04088: error during execution of trigger 'SCOTT.TR_SEC_EMP'
SQL> update emp set sal=sal*1.1 where empno=7788;
update emp set sal=sal*1.1 where empno=7788
       *
ERROR at line 1:
ORA-20002: Do not update data on a rest day!
ORA-06512: at "SCOTT.TR_SEC_EMP", line 7
ORA-04088: error during execution of trigger 'SCOTT.TR_SEC_EMP'

1.2 行触发器

行触发器是指当执行DML语句时，每作用一行被触发一次的触发器。

为了审计表数据的变化，可以使用行触发器。

语法如下：

CREATE [OR REPLACE] TRIGGER trigger_name
  {BEFORE | AFTER} event1 [OR event2 OR event3]
ON table_name [REFERENCING OLD AS old | NEW AS new]
FOR EACH ROW [WHEN condition]
PL/SQL block;

1.2.1 建立BEFORE行触发器

在处理行之前被触发的触发器

下面以确保雇员工资不能低于其原有工资。

CREATE OR REPLACE TRIGGER tr_emp_sal
BEFORE UPDATE OF sal ON emp FOR EACH ROW
BEGIN
  IF :new.sal<:old.sal THEN
    raise_application_error(-20010,'Wages can only go up, not down!');
  END IF;
END;
/

SQL> UPDATE emp SET sal=500 WHERE empno=7369;
UPDATE emp SET sal=500 WHERE empno=7369
       *
ERROR at line 1:
ORA-20010: Wages can only go up, not down!
ORA-06512: at "SCOTT.TR_EMP_SAL", line 3
ORA-04088: error during execution of trigger 'SCOTT.TR_EMP_SAL'

1.2.2建立AFTER行触发器

AFTER行触发器是指在处理行之后被触发的触发器。

下面以审计雇员工资变化，并建立行触发器tr_sal_change为例。

CREATE TABLE audit_emp_change(name varchar2(10),oldsal NUMBER(6,2),newsal NUMBER(6,2),time DATE);

CREATE OR REPLACE TRIGGER tr_sal_change
  AFTER UPDATE OF sal ON emp
  FOR EACH ROW
DECLARE
  v_temp INT;
BEGIN
  SELECT count(*)
    INTO v_temp
    FROM audit_emp_change
   WHERE name = :old.ename;
  IF v_temp = 0 THEN
    INSERT INTO audit_emp_change
    VALUES
      (:old.ename, :old.sal, :new.sal, SYSDATE);
  ELSE
    UPDATE audit_emp_change
       SET oldsal = :old.sal, newsal = :new.sal, time = SYSDATE
     WHERE name = :old.ename;
  END IF;
END;
/

SQL> UPDATE emp SET sal=sal*1.1 WHERE deptno=10;
3 rows updated.
SQL> select * from audit_emp_change;
NAME       OLDSAL  NEWSAL TIME
---------- ---------- ---------- ---------
CLARK       2964.5 3260.95 09-JUN-20
KING 6050    6655 09-JUN-20
MILLER 1573  1730.3 09-JUN-20
SQL> rollback;
Rollback complete.
SQL> select * from audit_emp_change;
no rows selected

1.2.3限制行触发器

需要使用WHEN子句对触发条件加以限制。以SALESMAN的雇员工资变化为例。

CREATE OR REPLACE TRIGGER tr_sal_change
  AFTER UPDATE OF sal ON emp
  FOR EACH ROW
    WHEN (old.job='MANAGER')
DECLARE
  v_temp INT;
BEGIN
  SELECT count(*)
    INTO v_temp
    FROM audit_emp_change
   WHERE name = :old.ename;
  IF v_temp = 0 THEN
    INSERT INTO audit_emp_change
    VALUES
      (:old.ename, :old.sal, :new.sal, SYSDATE);
  ELSE
    UPDATE audit_emp_change
       SET oldsal = :old.sal, newsal = :new.sal, time = SYSDATE
     WHERE name = :old.ename;
  END IF;
END;
/

SQL> select job,deptno from emp;
JOB      DEPTNO
--------- ----------
CLERK  20
MANAGER   20
MANAGER   10
PRESIDENT  10
CLERK  20
ANALYST   20
CLERK  10
7 rows selected.
SQL> UPDATE emp SET sal=sal*1.1 WHERE deptno=10;
3 rows updated.
SQL> select * from audit_emp_change;
NAME       OLDSAL  NEWSAL TIME
---------- ---------- ---------- ---------
CLARK       2964.5 3260.95 09-JUN-20

1.3 组合触发器

组合触发器(Compound Trigger)是Oracle Database 11g的新特性。

无论编写语句触发器还是行级触发器，它们只具有一种触发时机，而组合触发器往往具有多种触发时机。

示例：

CREATE OR REPLACE TRIGGER tr_update_sal
  FOR UPDATE OF sal ON emp COMPOUND TRIGGER
    msg1 VARCHAR2(50) := 'Salary range must be 1000 to 5000!';
    msg2 VARCHAR2(50) := 'Cannot be updated on a rest day!';
  BEFORE STATEMENT IS
BEGIN
  IF to_char(sysdate, 'DY', 'nls_date_language=AMERICAN')
    IN ('SAT', 'SUN') THEN
    raise_application_error(-20000, msg2);
  END IF;
END BEFORE STATEMENT;
  AFTER EACH ROW IS
  BEGIN
    IF :new.sal NOT BETWEEN 1000 AND 5000 THEN
      raise_application_error(-20001, msg1);
    END IF;
END AFTER EACH ROW;
END;
/

SQL> UPDATE emp SET sal=900 WHERE empno=7369;    
UPDATE emp SET sal=900 WHERE empno=7369
       *
ERROR at line 1:
ORA-20001: Salary range must be 1000 to 5000!
ORA-06512: at "SCOTT.TR_UPDATE_SAL", line 14
ORA-04088: error during execution of trigger 'SCOTT.TR_UPDATE_SAL'

1.4 DML触发器开发示例

DML触发器可用于实现数据安全保护、数据审计、数据完整性、参照完整性、数据复制等功能。

1.4.1控制数据安全

下面限制用户在正常工作时间(9:00-17:00)改变EMP表数据为例:

CREATE OR REPLACE TRIGGER tr_emp_time
BEFORE INSERT OR UPDATE OR DELETE ON emp
BEGIN
  IF to_char(SYSDATE,'HH24') NOT BETWEEN '11' AND '17' THEN
    raise_application_error(-20101,'Non working time!');
  END IF;
END;
/

SQL> UPDATE emp SET sal=3200 WHERE empno=7369;
UPDATE emp SET sal=3200 WHERE empno=7369
       *
ERROR at line 1:
ORA-20101: Non working time!
ORA-06512: at "SCOTT.TR_EMP_TIME", line 3
ORA-04088: error during execution of trigger 'SCOTT.TR_EMP_TIME'

1.4.2 实现数据审计

审计用于监视非法和可疑的数据库活动，但数据库审计只能用于监视用户操作，而不能记录数据变化。

为了审计表数据的变化，可以使用DML行触发器。

下面以审计删除时间，以及被删除的雇员名为例。

SQL>
CREATE TABLE audit_delete_emp(name VARCHAR2(10),time DATE);
CREATE OR REPLACE TRIGGER tr_delete_emp
AFTER DELETE ON emp FOR EACH ROW
BEGIN
  INSERT INTO audit_delete_emp VALUES(:old.ename,SYSDATE);
END;
/

SQL> DELETE FROM emp WHERE empno=7369;
1 row deleted.
SQL> SELECT * FROM audit_delete_emp;
NAME   TIME
---------- ---------
SMITH   10-JUN-20

1.4.3实现数据完整性

要求雇员新工资不能低于原工资，并且不能超过原工资20%。

SQL>
CREATE OR REPLACE TRIGGER tr_check_sal
BEFORE UPDATE OF sal ON emp FOR EACH ROW
WHEN (new.sal<old.sal OR new.sal>1.2*old.sal)
BEGIN
  raise_application_error(-20931,'Wages can only be increased but not decreased, and the increase cannot exceed 20%');
END;
/

SQL> UPDATE emp SET sal=sal*1.25 WHERE empno=7369;
UPDATE emp SET sal=sal*1.25 WHERE empno=7369
       *
ERROR at line 1:
ORA-20931: Wages can only be increased but not decreased, and the increase cannot exceed 20%
ORA-06512: at "SCOTT.TR_CHECK_SAL", line 2
ORA-04088: error during execution of trigger 'SCOTT.TR_CHECK_SAL'

1.4.4实现参照完整性

参照完整性是指在两张表之间具有主从关系(也即主外键关系)。当删除主表数据时，需要首先删除从表的相关数据；

当更新主表主键列时，需要首先更新从表相关数据。

为了实现级联删除，可以在定义外部键约束时指定ON DELETE CASCADE关键字。

但使用约束却不能实现级联更新。

为了实现级联更新，需要使用触发器。

示例：

CREATE OR REPLACE TRIGGER tr_update_cascade
AFTER UPDATE OF deptno ON dept FOR EACH ROW
BEGIN
  UPDATE emp SET deptno=:new.deptno WHERE deptno=:old.deptno;
END;
/

UPDATE dept SET deptno=50 where deptno=10;
SELECT ename FROM emp WHERE deptno=50;
---ENAME
CLARK
KING
MILLER

二 INSTEAD OF触发器

在简单视图上往往可以执行INSERT、UPDATE和DELETE操作，但在复杂视图上执行DML操作时有限制的。

例如视图子查询包含有集合操作符、分组函数、DISTINCT关键字或者连接查询，那么将禁止在该视图上执行DML操作。

为了在这些复杂视图上执行DML操作，需要建立INSTEAD-OF触发器。

INSTEAD-OF触发器只适用于视图。

SQL> CREATE OR REPLACE VIEW dept_emp AS SELECT a.deptno,a.dname,b.empno,b.ename FROM dept a,emp b WHERE a.deptno=b.deptno;
SQL> select * from dept_emp;
SQL> INSERT INTO dept_emp VALUES(50,'ADMIN',1223,'MARY');
INSERT INTO dept_emp VALUES(50,'ADMIN',1223,'MARY')
*
ERROR at line 1:
ORA-01779: cannot modify a column which maps to a non key-preserved table

http://www.itpub.net/thread-225291-1-1.html

1)view同一时刻只能对一个表进行操作。

2)“cannot modify a column which maps to a non key-preserved table”意思就是:

当对一个view进行delete、insert、update时，被(涉及到的)view列所映射table列(或列的组合)必须是有主健约束的，目的是要保证在同一时刻只能针对一条记录操作。

3)如果要实现“用一条SQL语句,想通过视图同时往三个表中插入数据”，可以在触发器里代码实现。

CREATE OR REPLACE TRIGGER tr_instead_of_dept_emp
INSTEAD OF INSERT ON dept_emp FOR EACH ROW
DECLARE
v_temp INT;
BEGIN
  SELECT count(*) INTO v_temp FROM dept WHERE deptno=:new.deptno;
  IF v_temp=0 THEN
    INSERT INTO dept(deptno,dname) VALUES(:new.deptno,:new.dname);
  END IF;
  SELECT count(*) INTO v_temp FROM emp WHERE empno=:new.empno;
  IF v_temp=0 THEN
    INSERT INTO emp(empno,ename,deptno) VALUES(:new.empno,:new.ename,:new.deptno);
  END IF;
END;
/

SQL> INSERT INTO dept_emp VALUES(50,'ADMIN',1223,'MARY');
SQL> select dname from dept where deptno=50;---ADMIN
SQL> SELECT ename FROM emp WHERE empno=1223;---MARY

三事件触发器

指基于Oracle系统事件或者客户事件所建立的触发器，通过使用事件触发器，提供了跟踪系统或数据库变化的机制。

当建立事件触发器时，需要使用各种事件属性函数。

3.1事件属性函数

1.ora_client_ip_address:该属性用于返回客户端IP地址。
2.ora_database_name:该属性用于返回数据名。
3.ora_des_encrypted_password:该属性用于返回当建立或修改用户时DES加密后的用户口令。
4.ora_dict_obj_name:该属性用于返回DDL操作所对应对象的名称。
5.ora_dict_obj_name_list:该属性用于返回特定事件所修改对象的对象名个数。
6.ora_dict_obj_owner_list:该属性用于返回DDL操作所对应对象的所有者列表个数。
7.ora_dict_obj_type:该属性用于返回特定事件所修改对象的所有者列表个数。
8.ora_grantee:该属性用于返回被授权者个数。
9.ora_instance_num:该属性用于返回实例编号。
10.ora_is_alter_column:该属性用于检测特定列是否被修改。
11.ora_is_creating_nested_table:该属性用于检测是否正在建立嵌套表。
12.ora_is_drop_column:该属性用于检测特定列是否被删除。
13.ora_is_servererror:该属性用于检测是否返回特定Oracle错误。
14.ora_login_user:该属性用于返回登录用户名。
15.ora_dict_obj_owner:该属性用于返回DDL操作所对应对象的所有者名称。
16.ora_partition_pos:该属性用于确定SQL语句文本中插入PARTITION子句的位置。
17.ora_privilege_list:该属性用于返回被授予或者被收回权限的个数。
18.ora_revokee:该属性用于返回被收回权限的用户个数。
19.ora_server_error:该属性用于返回在错误堆栈中特定错误位置所对应的错误号。
20.ora_server_error_depth:该属性用于返回在错误堆栈中错误消息的总数。
21.ora_server_error_msg:该属性用于返回在错误堆栈中特定错误位置的错误消息。
22.ora_server_error_num_params:该属性返回在错误堆栈中特定错误位置特定参数号所对应的字符串替代值。
23.ora_server_error_param:该属性用于返回在错误堆栈中特定错误位置特定参数号所对应的字符串替代值。
24.ora_sql_txt:该属性用于返回触发器语句的SQL文本元素个数。
25.ora_sysevent:用于返回触发触发器的系统事件名。
26.ora_with_grant_option:该属性用于确定授权是否带有WITH GRANT OPTOIN选项。
27.space_error_info:该属性用于确定错误是否与out-of-space相关。

3.2系统事件触发器

是由特定系统事件所触发的触发器。

包括STARTUP、SHUTDOWN、DB_ROLE_CHANGE、SERVERERROR四个事件。

注意：系统事件触发器只能由SYS用户建立，并且SHUTDOWN ABORT命令不会触发SHUTDOWN事件。

示例：

SQL> conn  as sysdba
SQL> create table event_table(event varchar2(30),time date);

SQL>
CREATE OR REPLACE TRIGGER tr_startup
AFTER STARTUP ON DATEBASE
BEGIN
  INSERT INTO event_table VALUES(ora_sysevent,SYSDATE);
END;
/
ORA-30506: system triggers cannot be based on tables or views
创建触发器报错ORA-30506
SQL> SHUDOWN
SQL> STARTUP
SQL> SELECT * FROM event_table;

3.3 客户事件触发器

指基于客户事件所建立的触发器，客户事件是指与用户登录、用户注销、DDL以及DCL相关的事件。

客户事件触发器只能由SYS用户建立。

3.3.1 建立登录触发器

conn / as sysdba
CREATE TABLE aud_logon_tab(username varchar2(20),time DATE,addr VARCHAR2(20));

CREATE OR REPLACE TRIGGER tr_logon
AFTER LOGON ON DATABASE
BEGIN
  INSERT INTO aud_logon_tab VALUES(ora_login_user,SYSDATE,ora_client_ip_address);
END;
/

conn cjc/cjc
conn scott/tiger
SELECT * FROM sys.aud_logon_tab;
USERNAME     TIME      ADDR
-------------------- --------- --------------------
CJC     10-JUN-20
SCOTT     10-JUN-20
SYS     10-JUN-20

3.3.2 建立DDL触发器

为了记录系统所发生的DDL事件，可以建立DDL触发器。

SQL> conn / as sysdba
SQL> CREATE TABLE aud_ddl_tab(event VARCHAR2(20),username VARCHAR2(10),owner VARCHAR2(10),objname VARCHAR2(20),objtype VARCHAR2(10),time DATE);
SQL> grant select on aud_ddl_tab to scott;

SQL>
CREATE OR REPLACE TRIGGER tr_ddl
AFTER DDL ON scott.schema
BEGIN
  INSERT INTO
aud_ddl_tab VALUES(ora_sysevent,ora_login_user,ora_dict_obj_owner,ora_dict_obj_name,ora_dict_obj_type,SYSDATE);
END;
/

SQL> conn scott/tiger
SQL> CREATE TABLE temp(cola INT);
SQL> SELECT * FROM sys.aud_ddl_tab;
EVENT     USERNAMEOWNER   OBJNAMEOBJTYPE    TIME
-------------------- ---------- ---------- -------------------- ---------- ---------
CREATE     SCOTTSCOTT   TEMP TABLE   11-JUN-20

四维护触发器

4.1 显示触发器信息

通过user_triggers查看当前用户触发器信息。

SQL> set long 800
SQL> set pagesize 400
SQL> select trigger_body FROM user_triggers WHERE trigger_name='TR_CHECK_SAL';
TRIGGER_BODY
--------------------------------------------------------------------------------
BEGIN
  raise_application_error(-20931,'Wages can only be increased but not decreased,
 and the increase cannot exceed 20%');
END;

SQL> select dbms_metadata.get_ddl('TRIGGER','TR_CHECK_SAL','SCOTT') from dual;
DBMS_METADATA.GET_DDL('TRIGGER','TR_CHECK_SAL','SCOTT')
--------------------------------------------------------------------------------
  CREATE OR REPLACE TRIGGER "SCOTT"."TR_CHECK_SAL"
BEFORE UPDATE OF sal ON emp FOR EACH ROW
 WHEN (new.sal<old.sal OR new.sal>1.2*old.sal) BEGIN
  raise_application_error(-20931,'Wages can only be increased but not decreased,
 and the increase cannot exceed 20%');
END;
ALTER TRIGGER "SCOTT"."TR_CHECK_SAL" ENABLE

4.2禁止触发器

ALTER TRIGGER "SCOTT"."TR_CHECK_SAL" DISABLE;
select STATUS FROM user_triggers WHERE trigger_name='TR_CHECK_SAL';

如果禁用表emp的所有触发器，可以使用：

ALTER TABLE SCOTT.EMP DISABLE ALL TRIGGERS;

4.3激活触发器

ALTER TRIGGER "SCOTT"."TR_CHECK_SAL" ENABLE;

如果激活表emp的所有触发器，可以使用：

ALTER TABLE SCOTT.EMP ENABLE ALL TRIGGERS;

4.4重新编译触发器

当使用ALTER TABLE命令修改表结构时，会使相关触发器转变为INVALID状态，为了使用这些触发器，需要重新编译。

ALTER TRIGGER tr_upd_emp COMPILE;

4.5删除触发器

DROP TRIGGER tr_check_sal;

更多数据库相关学习资料，可以查看我的ITPUB博客，网名chenoracle：

http://blog.itpub.net/29785807/

oracle

文章转载自IT小Chen，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

Oracle开发基础-触发器

评论