第 13 章 開發觸發器

觸發器是指存放在資料庫中，並被隱含執行的儲存過程。在ORACLE8i之前，只允許基於表或檢視的DML操作(INSERT,UPDATE和DELETE)建立觸發器；而從ORACLE8i開始，不僅支援DML觸發器，也允許基於系統事件(啟動資料庫、關閉資料庫、登入)和DDL操作建立觸發器。

13.1 觸發器簡介

觸發器由 觸發事件、 觸發條件和 觸發操作三部分組成。

1 觸發事件

觸發事件是指引起觸發器被觸發的SQL語句、資料庫事件或使用者事件。在Oracle8i之前，觸發事件只能是DML操作；在8i開始，不僅支援原有的DML事件，而且還增加了其他觸發器事件。具體觸發事件如下：

(1) 啟動和關閉例程；

(2)Oracle 錯誤資訊；

(3) 使用者登入和斷開會話；

(4) 特定表或檢視的DML 操作；

(5) 在任何方案上的DDL 語句。

2 觸發條件( 可選)

觸發條件是指使用WHEN子句指定一個BOOLEAN表示式，當布林表示式返回值為TRUE時，會自動執行觸發器相應程式碼；當布林表示式返回值為FALSE或UNKNOWN時，不會執行觸發器。

3 觸發操作

觸發操作是指包含SQL語句和其他執行程式碼的PL/SQL塊，不僅可以使用PL/SQL進行開發，也可以使用JAVA語言和C語言進行開發。當觸發條件為TRUE時，會自動執行觸發器操作的相應程式碼。但編寫觸發器執行程式碼時，需要注意一下限制：

(1) 觸發器程式碼大小不能超過32K 。如果確實需要使用大量程式碼建立觸發器，應該首先建立儲存過程，然後在觸發器中使用CALL 語句呼叫儲存過程。

(2) 觸發器只能包含SELECT,INSERT,UPDATE 和DELETE 語句，而不能包含DDL 語句(CREATE,ALTER,DROP) 和事務控制語句(COMMIT,ROLLBACK 和SAVEPOINT) 。

13.2 建立DML 觸發器

在Oracle8i之前，只能基於DML事件建立觸發器。在建立了DML觸發器後，如果發生了相應的DML操作，就會自動執行觸發器的程式碼。當建立DML觸發器時，需要指定觸發時機(BEFORE或AFTER)、觸發事件(INSERT,UPDATE,DELETE)、表名、觸發型別、觸發條件以及觸發操作。

1 觸發時機

觸發時機用於指定觸發器的觸發事件。當指定BEFORE關鍵字時，表示在執行DML操作之前觸發觸發器；當指定AFTER關鍵字，表示在執行DML操作之後觸發觸發器。

2 觸發事件

觸發事件用於指定導致觸發器執行的DML操作，也即INSERT,UPDATE和DELETE操作。既可以使用單個觸發事件，也可以組合多個觸發事件。

3 表名

因為DML觸發器是針對特定表進行的，所以必須指定DML操作所對應的表。

4 觸發型別

觸發型別用於指定當觸發事件發生後，需要執行幾次觸發操作。如果指定語句觸發型別(預設)，那麼只會執行一次觸發器程式碼；如果指定行觸發型別，則會在每個被作用行上執行一次觸發器程式碼。

5 觸發條件

觸發條件用於指定執行觸發器程式碼的條件，只有條件為TRUE時才會執行觸發器程式碼。注意，當編寫DML觸發器時，只允許在行觸發器上指定觸發條件。

6 觸發操作

觸發操作作用於指定觸發器執行程式碼。如果使用PL/SQL儲存過程、JAVA儲存過程或外部儲存過程來實現觸發器程式碼，那麼在觸發操作部分可直接使用CALL語句呼叫相應過程。如果使用PL/SQL匿名塊編寫觸發器操作，則應該按照以下格式進行編寫：

[DECLARE]

________定義變數、常量等

BEGIN

________編寫SQL語句和PL/SQL語句

EXCEPTION

________編寫例外處理語句

END;

7 DML 觸發器觸發順序

(1) DML觸發器在單行資料上的觸發順序

當針對某一表的相同DML操作而建立了多個DML觸發器(BEFORE/AFTER語句觸發器、BEFORE/AFTER行觸發器)時，如果在單行資料上執行了該種DML操作，則觸發器會按照以下順序執行：

對應單行資料而言，無論是語句觸發器，還是行觸發器，觸發器程式碼實際只執行一次，並且執行順序為BEFORE語句觸發器、BEFORE行觸發器、DML操作、AFTER行觸發器、AFTER語句觸發器。

(2) DML觸發器在多行資料上的觸發順序

在多行資料而言，語句觸發器只能執行一次，而行觸發器在每個作用行上都執行一次。

13.2.1 語句觸發器

語句觸發器是指當執行DML語句時被隱含執行的觸發器。如果在表上針對某種DML操作建立了語句觸發器，那麼當執行DML操作時會自動執行觸發器的相應程式碼。當審計DML操作，或者確保DML操作安全執行時，可以使用語句觸發器。注意，使用語句觸發器，不能記錄列資料的變化。建立語句觸發器的語法如下：

CREATE [OR REPLACE] TRIGGER trigger_name

timing event1 [OR event2 OR event3]

ON table_name

PL/SQL block;

如上所示，trigger_name用於指定觸發器名；timint用於指定觸發器時機(BEFORE或AFTER)；event用於指定觸發器事件(INSERT、UPDATE和DELETE)；table_name用於指定DML操作所對應的表名。

1 建立BEFORE 語句觸發器

為了確保DML操作在正常情況下執行，可以基於DML操作建立BEFORE語句觸發器。例如，為了禁止工作人員在休息日改變僱員資訊，開發人員可以建立BEFORE語句觸發器，以實現資料的安全保護。示例如下：

SQL> create or replace trigger tr_sec_emp

2 before insert or update or delete on emp

3 begin

4 if to_char(sysdate,'DY','nls_date_language=AMERICAN')

5 in('SAT','SUN') THEN

6 raise_application_error(-20001,'bu neng zai xiu xi ri xiu gai gu yuan xin xi');

7 end if;

8 end;

9 /

Trigger created.

在建立了觸發器tr_sec_emp之後，如果星期六、星期日在EMP表上執行DML操作，則會顯示錯誤資訊。示例如下：

[root@ogg1 ~]# date -s "20150531 14:05:30"

Sun May 31 14:05:30 CST 2015

[root@ogg1 ~]# date

Sun May 31 14:05:31 CST 2015

SQL> delete emp where deptno=10;

delete emp where deptno=10

*

ERROR at line 1:

ORA-20001: bu neng zai xiu xi ri xiu gai gu yuan xin xi

ORA-06512: ?"CHEN.TR_SEC_EMP", line 4

ORA-04088: 靠?'CHEN.TR_SEC_EMP' 靠靠靠?

2 使用條件謂語

當觸發器中同時包含多個觸發事件(INSERT,UPDATE,DELETE)時，為了在觸發器程式碼中區分具體的觸發器事件，可以使用以下三個條件謂語：

(1) INSERTINT：當觸發器事件是INSERT時，該條件謂語返回值為TRUE，否則為FALSE。

(2) UPDATING：當觸發器事件是UPDATE時，該條件謂語返回值為TRUE，否則為FALSE。

(3) DELETING：當觸發器事件是DELETE時，該條件謂語返回值為TRUE，否則為FALSE。

下面舉例說明在觸發器中使用這三個條件謂語的方法，示例如下：

SQL> 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,'not add people in rest day');

WHEN UPDATING THEN

raise_application_error(-20002,'not modify people in rest day');

WHEN DELETING THEN

raise_application_error(-20003,'not delete people in rest day');

end case;

end if;

end;

/

Trigger created.

SQL> insert into emp(empno,ename) values(123,'abc');

insert into emp(empno,ename) values(123,'abc')

*

ERROR at line 1:

ORA-20001: not add people in rest day

ORA-06512: ?"CHEN.TR_SEC_EMP", line 6

ORA-04088: 靠?'CHEN.TR_SEC_EMP' 靠靠靠?

SQL> update emp set sal=5000 where deptno=10;

update emp set sal=5000 where deptno=10

*

ERROR at line 1:

ORA-20002: not modify people in rest day

ORA-06512: ?"CHEN.TR_SEC_EMP", line 8

ORA-04088: 靠?'CHEN.TR_SEC_EMP' 靠靠靠?

SQL> delete emp where deptno=20;

delete emp where deptno=20

*

ERROR at line 1:

ORA-20003: not delete people in rest day

ORA-06512: ?"CHEN.TR_SEC_EMP", line 10

ORA-04088: 靠?'CHEN.TR_SEC_EMP' 靠靠靠?

3 建立AFTER 語句觸發器

為了審計DML操作，或者在DML操作之後執行彙總運算，可以使用AFTER語句觸發器。例如，為了審計在EMP表上INSERT,UPDATE和DELETE的操作次數，可以建立AFTER觸發器。在建立AFTER觸發器之前，首先建立審計表audit_table。示例如下：

SQL> create table audit_table(

2 name varchar2(20),ins int,upd int,del int,starttime date,endtime date);

Table created.

為了審計EMP表上DML操作執行的次數、最早執行時間和最近執行時間，需要建立AFTER語句觸發器。示例如下：

SQL> create or replace trigger tr_audit_emp

2 after insert or update or delete on emp

3 declare

4 v_temp int;

5 begin

6 select count(*) into v_temp from audit_table where name='EMP';

7 if v_temp=0 then

8 insert into audit_table values('EMP',0,0,0,SYSDATE,NULL);

9 end if;

10 case

11 when inserting then

12 update audit_table set ins=ins+1,endtime=SYSDATE where name='EMP';

13 when updating then

14 update audit_table set upd=upd+1,endtime=sysdate where name='EMP';

15 when deleting then

16 update audit_table set del=del+1,endtime=sysdate where name='EMP';

17 end case;

18 end;

19 /

Trigger created.

SQL> update emp set sal=5000 where empno=7788;

1 row updated.

SQL> update emp set sal=8000 where empno=7369;

1 row updated.

SQL> delete emp where empno=7369;

1 row deleted.

SQL> insert into emp(empno,ename) values(1234,'aaa');

1 row created.

SQL> select * from audit_table;

NAME INS UPD DEL STARTTIME ENDTIME

-------------------- ---------- ---------- ---------- ---------- ----------

EMP 1 2 1 02-6? -15 02-6? -15

13.2.2 行觸發器

行觸發器是指執行DML操作時，每作用一行就觸發一次的觸發器。審計資料變化時，可以使用行觸發器。建立行觸發器的語法如下：

CREATE [OR REPLACE] TRIGGER trigger_name

Timing event1 [OR event12OR event3]

ON table_name

[REFERENCING OLD AS old | NEW AS NEW]

FOR EACH ROW

[WHEN condition]

PL/SQL block;

其中：REFERENCING子句用於指定引用新、舊資料的方式，預設情況使用OLD修飾符引用舊資料，使用NEW修飾符引用新資料；FOR EACH ROW表示建立行觸發器；

1 建立BEFORE 行觸發器

在開發資料庫應用時，為了確保資料符合商業邏輯或企業規則，應該使用約束對輸入資料加以限制，但某些情況下使用約束可能無法實現複雜的商業邏輯或企業規則，此時可以考慮使用BEFORE行觸發器。下面以確保僱員工資不能低於其原有工資為例，說明建立BEFORE行觸發器。示例如下：

SQL> 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,'Bu neng jiang sal!');

end if;

end;

/

Trigger created.

在建立觸發器tr_emp_sal之後，如果僱員新工資低於其原工資，則會提示錯誤資訊。示例如下：

SQL> update emp set sal=1000 where empno=7788;

update emp set sal=1000 where empno=7788

*

ERROR at line 1:

ORA-20010: Bu neng jiang sal!

ORA-06512: ?"CHEN.TR_EMP_SAL", line 3

ORA-04088: 靠?'CHEN.TR_EMP_SAL' 靠靠靠?

2 建立AFTER 行觸發器

為了審計DML操作，可以使用語句觸發器或ORACLE系統提供的審計功能；而為了審計資料變化，則應該使用AFTER行觸發器。下面以審計僱員工資變化為例，說明使用AFTER行觸發器的方法。在建立觸發器之前，首先應建立存放審計資料的表audit_emp_change，示例如下：

SQL> create table audit_emp_change(

2 name varchar2(10),oldsal number(6,2),newsal number(6,2),time date);

Table created.

為了審計所有僱員的工資變化和僱員工資的更新日期，必須要建立AFTER行觸發器。示例如下：

SQL> 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;

/

Trigger created.

在建立觸發器tr_sal_change之後，當修改僱員工資時，會將每個僱員的工資變化全部寫入到審計表audit_emp_change中。示例如下

SQL> update emp set sal=sal*1.5 where deptno=20;

5 rows updated.

SQL> select * from audit_emp_change;

NAME OLDSAL NEWSAL TIME

---------- ---------- ---------- ----------

SMITH 800 1200 03-6? -15

JONES 2975 4462.5 03-6? -15

SCOTT 3000 4500 03-6? -15

ADAMS 1100 1650 03-6? -15

FORD 3000 4500 03-6? -15

3 限制行觸發器

當使用行觸發器時，預設情況下會在每個被作用行行執行一次觸發器程式碼。為了使得在特定條件下執行行觸發器程式碼，就需要使用WHEN子句對觸發器條件加以限制。下面以審計崗位“SALESMAN”的僱員工資變化為例，說明限制行觸發器的方法。示例如下：

SQL>

create or replace trigger tr_sal_change

after update of sal on emp

for each row

when(old.job='CLERK')

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;

/

Trigger created.

SQL> update emp set sal=sal*1.5 where deptno=20;

5 rows updated.

SQL> select * from audit_emp_change;

NAME OLDSAL NEWSAL TIME

---------- ---------- ---------- ----------

SMITH 800 1200 03-6? -15

ADAMS 1100 1650 03-6? -15

SQL> select ename,job from emp where deptno=20;

ENAME JOB

---------- ---------

SMITH CLERK

JONES MANAGER

SCOTT ANALYST

ADAMS CLERK

FORD ANALYST

4 DML 觸發器使用注意事項

當編寫DML觸發器時，觸發器程式碼不能從觸發器所對應的基表中讀取資料。例如，如果要基於EMP表建立觸發器，那麼該觸發器的執行程式碼不能包含對EMP表的查詢操作。儘管在建立觸發器時不會出現任何錯誤，但在執行相應觸發器操作時出顯示錯誤資訊。假定希望僱員工資不能超過當前僱員最高工資，並使用觸發器實現該規則。示例如下：

SQL> create or replace trigger tr_emp_sal

2 before update of sal on emp

3 for each row

4 declare

5 maxsal number(6,2);

6 begin

7 select max(sal) into maxsal from emp;

8 if :new.sal>maxsal then

9 raise_application_error(-20010,'chao chu shang xian');

10 end if;

11 end;

12 /

Trigger created.

如上所示，當建立觸發器tr_emp_sal時，不會顯示任何錯誤。但因為觸發器程式碼引用了基表emp，所以在執行UPDATE操作時會顯示如下錯誤資訊：

SQL> select max(sal) from emp;

MAX(SAL)

----------

5000

SQL> update emp set sal=3000 where empno=7369;

update emp set sal=3000 where empno=7369

*

ERROR at line 1:

ORA-04091: ?CHEN.EMP 靠靠? 靠?靠靠靠

ORA-06512: ?"CHEN.TR_EMP_SAL", line 4

ORA-04088: 靠?'CHEN.TR_EMP_SAL' 靠靠靠?

SQL> update emp set sal=8000 where empno=7369;

update emp set sal=8000 where empno=7369

*

ERROR at line 1:

ORA-04091: ?CHEN.EMP 靠靠? 靠?靠靠靠

ORA-06512: ?"CHEN.TR_EMP_SAL", line 4

ORA-04088: 靠?'CHEN.TR_EMP_SAL' 靠靠靠?

13.2.3 使用DML 觸發器

為了確保資料庫資料滿足特定的商業規則或企業邏輯，可以使用約束、觸發器和子程式實現。因為約束效能最好，實現最簡單，所有首先約束；如果使用約束不能實現特定規則，那麼應該選擇觸發器；如果觸發器仍然不能實現特定規則，那麼應該選擇子程式(過程和函式)。DML觸發器可以用於實現資料安全保護、資料審計、資料完整性、參照完整性、資料複製等功能，下面透過示例給大家說明如何實現這些功能。

1 控制資料安全

在伺服器級控制資料安全是透過授予和收回物件許可權來實現的，例如為了使得CHEN使用者可以在SCOTT.EMP表上執行DML操作和SELECT操作，必須要為CHEN使用者授予相應的物件許可權。如下所示：

SQL> conn scott/tiger

Connected.

SQL> grant select,insert,update,delete on emp to chen;

Grant succeeded.

當使用者有了以上許可權之後，就可以隨時在EMP表上執行相應的SQL操作。為了實現更復雜的安全模式(例如現在要修改的資料，修改時間等)，就需要使用DML觸發器了。下面以限制使用者在正常工作時間(9:00~18:00)改變EMP表資料為例，說明使用DML觸發器控制資料安全的方法。示例如下：

SQL> create or replace trigger tr_emp_time

before insert or update or delete on emp

begin

if to_char(sysdate,'hh24') not between '9' and '18' then

raise_application_error(-20101,'fei gong zuo shi jian');

end if;

end;

/

Trigger created.

建立觸發器tr_temp_time之後，只能在 9:00~18:00之間在EMP表上執行DML操作。如果不在該時間段，則會顯示錯誤資訊：

SQL> update emp set sal=1200 where empno=7788;

update emp set sal=1200 where empno=7788

*

ERROR at line 1:

ORA-20101: fei gong zuo shi jian

ORA-06512: ?"CHEN.TR_EMP_TIME", line 3

ORA-04088: 靠?'CHEN.TR_EMP_TIME' 靠靠靠?

SQL> select to_char(sysdate,'hh24') from dual; --------Why???

TO

--

11

2 實現資料審計

審計用於監視非法和可疑的資料庫活動。Oracle資料庫本身提供了審計功能，例如，如果要對EMP表上的DML操作進行審計，可以執行如下命令：

SQL> audit insert,update,delete on emp by access;

SQL> show parameter audit

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

audit_file_dest string /u01/app/oracle/admin/ogg1/adu

mp

audit_sys_operations boolean FALSE

audit_syslog_level string

audit_trail string DB

SQL> conn /as sysdba

Connected.

SQL> alter system set audit_trail=db_extended scope=spfile;

System altered.

SQL> startup force

SQL> show parameter audit

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

audit_file_dest string /u01/app/oracle/admin/ogg1/adu

mp

audit_sys_operations boolean FALSE

audit_syslog_level string

audit_trail string DB_EXTENDED

SQL> conn chen/chen

Connected.

SQL> audit insert,update,delete on emp by access;

Audit succeeded.

SQL> update emp set sal=8000 where empno=7788;

1 row updated.

SQL> delete emp where depnto=10;

delete emp where depnto=10

*

ERROR at line 1:

ORA-00904: "DEPNTO": 靠靠?

SQL> delete emp where deptno=20;

5 rows deleted.

SQL> col sql_text for a45

SQL> select username,sql_text from dba_audit_trail where sql_text is not null;

USERNAME SQL_TEXT

------------------------------ ---------------------------------------------

CHEN update emp set sal=8000 where empno=7788

CHEN delete emp where depnto=10

CHEN delete emp where deptno=20

如上所示，在設定了審計選項之後，如果在EMP表上執行INSERT、UPDATE和DELETE操作，ORACLE會將關於SQL操作的資訊(使用者、時間等)寫入到資料字典中。 注意，使用資料庫審計只能審計 SQL 操作，而不會記載資料變化。為了審計SQL操作所引起的資料變化，必須要使用DML觸發器。示例如下：

SQL> 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;

/

Trigger created.

在建立了觸發器tr_sal_change之後，當修改僱員工資時，會將每個僱員的工資變化全部寫入到審計表audit_emp_change中。示例如下：

SQL> update emp set sal=sal*1.1 where deptno=30;

6 rows updated.

SQL> select * from audit_emp_change;

NAME OLDSAL NEWSAL TIME

---------- ---------- ---------- ----------

ALLEN 1600 1760 03-6? -15

WARD 1250 1375 03-6? -15

MARTIN 1250 1375 03-6? -15

BLAKE 2850 3135 03-6? -15

TURNER 1500 1650 03-6? -15

JAMES 950 1045 03-6? -15

6 rows selected.

SQL> select username,sql_text from dba_audit_trail where sql_text is not null;

USERNAME SQL_TEXT

------------------------------ ---------------------------------------------

CHEN update emp set sal=8000 where empno=7788

CHEN delete emp where depnto=10

CHEN delete emp where deptno=20

CHEN update emp set sal=sal*2 where deptno=10

CHEN update emp set sal=sal*1.1 where deptno=30

3 實現資料完整性

資料完整性用語確保資料庫資料滿足特定的商業邏輯或企業規則，資料完整性可以使用約束，觸發器和子程式實現。因為約束的實現最簡單，效能也最好，所以實現資料完整性首先約束。例如，為了限制僱員工資不能低於800元，可以選用CHENCK約束。示例如下：

SQL> alter table emp add constraint ck_sal check (sal>=800);

Table altered.

SQL> update emp set sal=8000 where empno=7369;

1 row updated.

SQL> update emp set sal=700 where empno=7788;

update emp set sal=700 where empno=7788

*

ERROR at line 1:

ORA-02290: 靠靠靠靠 (CHEN.CK_SAL)

但某些情況下使用約束無法實現特定的商業規則，此時可以使用觸發器來實現完整性。例如，假定希望僱員的新工資不能低於其原工資，並且不能高於原工資20%，使其約束顯然無法實現該規則，但透過觸發器卻可以實現該規則。示例如下：

SQL> create or replace trigger tr_check_sal

2 before update of sal on emp

3 for each row

4 when (new.sal1.2*old.sal)

5 begin

6 raise_application_error(-20931,'sal bu neng jiang,bu neng chao guo 20%');

7 end;

8 /

Trigger created.

SQL> update emp set sal=7999 where empno=7369;

update emp set sal=7999 where empno=7369

*

ERROR at line 1:

ORA-20931: sal bu neng jiang,bu neng chao guo 20%

ORA-06512: ?"CHEN.TR_CHECK_SAL", line 2

ORA-04088: 靠?'CHEN.TR_CHECK_SAL' 靠靠靠?

SQL> update emp set sal=16000 where empno=7369;

update emp set sal=16000 where empno=7369

*

ERROR at line 1:

ORA-20931: sal bu neng jiang,bu neng chao guo 20%

ORA-06512: ?"CHEN.TR_CHECK_SAL", line 2

ORA-04088: 靠?'CHEN.TR_CHECK_SAL' 靠靠靠?

SQL> update emp set sal=8500 where empno=7369;

1 row updated.

4 實現參照完整性

參照完整性是指若兩個表之間具有主從關係(也即主外來鍵關係)，當刪除主表資料時，必須確保相關的從表資料已經被刪除；當修改主表的主鍵列資料時，必須確保相關從表資料已經被修改。為了實現級聯刪除，可以在定義外部鍵約束時指定ON DELETE CASCADE關鍵字。示例如下：

SQL>alter table emp drop constraint FK_DEPTNO;

SQL>alter table EMP

add constraint FK_DEPTNO foreign key (DEPTNO)

references DEPT (DEPTNO) on delete cascade;

當用如上方式建立了外部鍵fk_deptno之後，在刪除表DEPT的資料時，會同時刪除從表EMP的所有相關資料。但使用約束卻不能實現級聯更新，如果要更新DEPT表的部門號，則會顯示如下錯誤資訊：

SQL> update dept set deptno=50 where deptno=10;

update dept set deptno=50 where deptno=10

*

ERROR at line 1:

ORA-02292: integrity constraint (CHEN.FK_DEPTNO) violated - child record found

如上所示，錯誤原因是emp表包含有該部門的相應僱員。為了實現級聯更新，可以使用觸發器。示例如下：

SQL> create or replace trigger tr_update_cascade

2 after update of deptno on dept

3 for each row

4 begin

5 update emp set deptno=:new.deptno where deptno=:old.deptno;

6 end;

7 /

Trigger created.

在建立了觸發器tr_update_cascade之後，當更新DEPT表的部門號時，會級聯更新EMP表的相應僱員的部門號。示例如下：

SQL> update dept set deptno=50 where deptno=10;

1 row updated.

SQL> select * from dept;

DEPTNO DNAME LOC

---------- ---------------------------- --------------------------

50 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

SQL> select ename,deptno from emp where deptno=50;

ENAME DEPTNO

-------------------- ----------

CLARK 50

KING 50

MILLER 50

13.3 建立INSTEAD OF 觸發器

對於簡單檢視，可以直接執行INSERT,UPDATE和DELETE操作。但對於複雜檢視，不允許直接執行INSERT,UPDATE和DELETE操作。 當檢視符合以下任何一種情況時，都不允許直接執行 DML 操作。具體情況如下：

1 具有集合運算子(UNION,UNION ALL,INTERSECT,MINUS)；

2 具體分組函式(MIN,MAX,SUM,AVG,COUNT等)；

3 具體GROUP BY,CONNECT BY或START WITH等子句；

4 具有DISTINCT關鍵字；

5 具有連線查詢。

為了在具有以上情況查詢的複雜檢視上執行DML操作，必須要基於檢視建立INSTEAD-OF觸發器。在建立INSTEAD-OF觸發器之後，就可以基於複雜檢視執行INSERT,UPDATE和DELETE語句。但建立INSTEAD-OF觸發器有以下注意事項：

1 INSTEAD OF 選項只適用於檢視；

2 當基於檢視建立觸發器時，不能指定BEFORE和AFTER選項；

3 在建立檢視時沒有指定WITH CHECK OPTION選項；

4 當建立INSTEAD OF觸發器時，必須指定FOR EACH ROW選項。

下面舉例說明覆雜檢視建立INSTEAD-OF觸發器的方法。

1 建立複雜檢視dept_emp

檢視是邏輯表，本身沒有任何資料。檢視只是對於一條SELECT語句，當查詢檢視時，其資料實際是從檢視基表上取得。為了簡化部門及其僱員資訊的查詢，應建立複雜檢視dept_emp。示例如下：

SQL> create or replace view dept_emp as

2 select a.deptno,a.dname,b.empno,b.ename from dept a,emp b where a.deptno=b.deptno;

View created.

SQL> select * from dept_emp where deptno=10;

DEPTNO DNAME EMPNO ENAME

---------- ---------------------------- ---------- --------------------

10 ACCOUNTING 7782 CLARK

10 ACCOUNTING 7839 KING

10 ACCOUNTING 7934 MILLER

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

2 建立INSTEAD-OF 觸發器

為了在複雜檢視上執行DML操作，必須要基於複雜檢視建立INSTEAD-OF觸發器。下面以在複雜檢視dept_emp上執行INSERT操作為例，說明建立INSETEAD-OF觸發器的方法。示例如下：

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;

/

Trigger created.

當建立了INSTEAD-OF觸發器tr_instead_dept_emp之後，就可以在複雜檢視dept_emp上執行INSERT操作了。示例如下：

SQL> insert into dept_emp values(50,'ADMIN','1223','MARY');

1 row created.

SQL> insert into dept_emp values(10,'ADMIN','1224','BAKE');

1 row created.

SQL> select * from dept;

DEPTNO DNAME LOC

---------- ---------------------------- --------------------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

50 ADMIN

SQL> select empno,ename,deptno from emp where empno in('1223','1224');

EMPNO ENAME DEPTNO

---------- -------------------- ----------

1223 MARY 50

1224 BAKE 10

13.4 建立系統事件觸發器

系統事件觸發器是指基於Oracle系統事件(例如LOGON和STARTUP)所建立的觸發器。透過使用系統事件觸發器，提供了跟蹤系統或資料庫變化的機制。下面介紹一下常用的系統事件屬性函式，以及建立各種事件觸發器的方法。

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(name_list OUT ora_name_list_t):用於返回在事件中被修改的物件列表；

(6) ora_dict_obj_owner:用於返回DDL操作所對應的物件的所有者名；

(7) ora_dict_obj_owner_list(owner_list OUT ora_name_list_t):用於返回在事件中被修改物件的所有者列表；

(8) ora_dict_obj_type:用於返回DDL操作所對應的資料物件的型別；

(9) ora_grantee(user_list OUT ora_name_list_t):用於返回授權事件的授權者；

(10)ora_instance_num:用於返回例程號；

(11)ora_is_alter_column(column_name IN VARCHAR2):用於檢測定列是否被刪除；

(12)ora_is_creating_nested_table:用於檢測是否正在建立巢狀表；

(13)ora_is_drop_column(column_name IN VARCHAR2):用於檢測特定列是否被刪除；

(14)ora_is_servererro(error_number):用於檢測是否返回特定ORACLE錯誤；

(15)ora_login_user:用於返回登入使用者名稱；

(16)ora_sysevent:用於返回觸發器的系統事件名。

2 建立例程啟動和關閉觸發器

為了跟蹤例程啟動和關閉事件，可以分別建立例程啟動觸發器和例程關閉觸發器。為了記載例程啟動和關閉的事件和時間，首先建立事件表event_table。示例如下：

SQL> show user

USER is "CHEN"

SQL> create table event_table(event varchar2(20),time date);

Table created.

在建立了事件表event_table之後，就可以在觸發器中引用該表了。注意，例程啟動觸發器和例程關閉觸發器只能在特權使用者下才能建立，並且例程啟動觸發器只能使用AFTER關鍵字，而例程關閉觸發器只能使用BEFORE關鍵字，示例如下：

SQL> create or replace trigger tr_startup

2 after startup on database

3 begin

4 insert into event_table values(ora_sysevent,sysdate);

5 end;

6 /

Trigger created.

SQL> create or replace trigger tr_shutdown

2 before shutdown on database

3 begin

4 insert into event_table values(ora_sysevent,sysdate);

5 end;

6 /

Trigger created.

SQL> conn /as sysdba

Connected.

SQL> shutdown immediate

SQL> startup

SQL> conn chen/chen

SQL> select event,to_char(time,'yyyy-mm-dd hh24:mi:ss') time from event_table;

EVENT TIME

---------------------------------------- --------------------------------------

SHUTDOWN 2015-06-04 14:45:42

STARTUP 2015-06-04 14:45:58

[oracle@ogg1 ~]$ vim /u01/app/oracle/diag/rdbms/ogg1/ogg1/trace/alert_ogg1.log

Shutting down instance (immediate)

Stopping background process SMCO

Shutting down instance: further logons disabled

…………………

Thu Jun 04 14:45:51 2015

PMON started with pid=2, OS id=7159

…………………

3 建立登入和退出觸發器

為了記載使用者登入和退出事件，可以分別建立登入和退出觸發器。為了記載登入使用者和退出使用者的名稱、事件和IP地址，應該首先建立專門存放登入和退出的資訊表LOG_TABLE。示例如下：

SQL> create table log_table(

2 username varchar2(20),logon_time date,logoff_time date,address varchar2(20));

Table created.

在建立了LOG_TABLE表之後，就可以在觸發器中引用該表了。注意，登入觸發器和退出觸發器一定要以特權使用者身份建立，並且登入觸發器只能使用AFTER關鍵字，而退出觸發器只能使用BEFORE關鍵字。示例如下：

SQL> create or replace trigger tr_logon

2 after logon on database

3 begin

4 insert into log_table(username,logon_time,address) values(ora_login_user,sysdate,ora_client_ip_address);

5 end;

6 /

Trigger created.

SQL> create or replace trigger tr_logoff

2 before logoff on database

3 begin

4 insert into log_table(username,logoff_time,address) values(ora_login_user,sysdate,ora_client_ip_address);

5 end;

6 /

Trigger created.

在建立了觸發器tr_logon之後，當使用者登入到資料庫之後，會執行觸發器程式碼；在建立了觸發器tr_logoff之後，當使用者斷開資料庫連線之前，會執行其觸發器程式碼。示例如下：

SQL> conn scott/tiger

Connected.

SQL> conn oe/oe

Connected.

SQL> conn / as sysdba

Connected.

SQL> conn system/oracle

Connected.

SQL> conn chen/chen

Connected.

SQL> select * from log_table;

SQL> col username for a8

SQL> col address for a20

SQL> select * from log_table order by username;

USERNAME LOGON_TIME LOGOFF_TIME ADDRESS

-------- ------------ ------------ --------------------

CHEN 04-JUN-15

OE 04-JUN-15

SCOTT 04-JUN-15

SYS 04-JUN-15

SYSTEM 04-JUN-15

4 建立DDL 觸發器

為了記載系統所發生的DDL事件(CREATE,ALTER,DROP等)，可以建立DDL觸發器。為了記載DDL事件資訊，應該建立專門的表，以便存放DDL事件資訊。示例如下：

SQL> create table event_ddl(

2 event varchar2(20),username varchar2(10),

3 owner varchar2(10),objname varchar2(20),

4 objtype varchar2(10),time date);

Table created.

在建立表EVENT_DDL之後，就可以在觸發器中引用該表了。為了記載DDL事件，應該建立DDL觸發器。注意，當建立DDL觸發器時，必須要使用AFTER關鍵字。示例如下：

SQL> create or replace trigger tr_ddl

2 after ddl on chen.schema

3 begin

4 insert into event_ddl values(ora_sysevent,ora_login_user,ora_dict_obj_owner,ora_dict_obj_name,ora_dict_obj_type,sysdate);

5 end;

6 /

Trigger created.

在建立了觸發器tr_ddl之後，如果在CHEN方案物件上執行DDL操作，則會將該資訊記載到表event_table中。示例如下：

SQL> create table t1(id number);

SQL> alter table t1 add(name varchar2(20));

SQL> drop table t1 purge;

SQL> col event for a8

SQL> col owner for a8

SQL> col objname for a8

SQL> col objtype for a8

SQL> select * from event_ddl;

EVENT USERNAME OWNER OBJNAME OBJTYPE TIME

-------- -------- -------- -------- -------- ------------

CREATE CHEN CHEN T1 TABLE 04-JUN-15

ALTER CHEN CHEN T1 TABLE 04-JUN-15

DROP CHEN CHEN T1 TABLE 04-JUN-15

13.5 管理觸發器

1 顯示觸發器資訊

建立觸發器時，oracle會將觸發器資訊寫入到資料字典中，透過查詢資料字典檢視user_triggers,可以顯示當前使用者所包含的所有觸發器資訊。示例如下：

SQL> select trigger_name,status from user_triggers;

TRIGGER_NAME STATUS

------------------------------------------------------------ ----------------

TR_INSTEAD_OF_DEPT_EMP ENABLED

TR_UPDATE_CASCADE ENABLED

TR_LOGON ENABLED

TR_STARTUP ENABLED

TR_SHUTDOWN ENABLED

TR_LOGOFF ENABLED

TR_DDL ENABLED

7 rows selected.

2 禁止觸發器

禁止觸發器是指使觸發器臨時失效。當觸發器處於ENABLE狀態時，如果表上執行DML操作，則就會觸發相應的觸發器。如果基於INSERT操作建立觸發器，當使用SQL*Loader裝載大批次資料時會觸發觸發器。為了加快資料裝載速度，應該在裝載資料之前禁止觸發器。方法如下：

SQL> alter trigger tr_ddl disable;

Trigger altered.

SQL> select trigger_name,status from user_triggers where trigger_name='TR_DDL';

TRIGGER_NAME STATUS

------------------------------------------------------------ ----------------

TR_DDL DISABLED

3 啟用觸發器

啟用觸發器是指使觸發器重新生效。當使用SQL*Loader裝載了資料之後，為了使被禁止的觸發器生效，應該啟用觸發器。方法如下：

SQL> alter trigger tr_ddl enable;

4 禁止或啟用表的所有觸發器

SQL> alter table emp disable all triggers;

SQL> alter table emp enable all triggers;

5 重新編譯觸發器

當使用ALTER TABLE命令修改表結構(例如增加列、刪除列)時，會使得其觸發器轉變為INVALID狀態。在這種情況下，為了使得觸發器繼續生效，需要重新編譯觸發器。示例如下：

SQL> alter trigger tr_ddl compile;

6 刪除觸發器

當觸發器不在需要時，可以使用drop trigger命令刪除觸發。注意，在表上的觸發器越多，對應DML操作的效能影響越大，所以一定要適度使用觸發器。刪除觸發器的示例如下：

SQL> drop trigger tr_ddl;

----- 本文摘抄自《精通oracle 10g PL/SQL 程式設計( 王海亮) 》

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

觸發器詳解

觸發器詳解

相關文章