Oracle開發基礎-觸發器

Oracle 開發基礎 - 觸發器

---參考：《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       USERNAME OWNER    OBJNAME  OBJTYPE    TIME
-------------------- ---------- ---------- -------------------- ---------- ---------
CREATE       SCOTT SCOTT    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;

歡迎關注我的微信公眾號"IT小Chen"，共同學習，共同成長！！！    

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29785807/viewspace-2697535/，如需轉載，請註明出處，否則將追究法律責任。