觸發器中獲取SQL語句

從Oracle9i開始，Oracle增加了在觸發器中獲取觸發語句的功能。

9i開始，在系統定義事件屬性中，增加了ORA_SQL_TXT屬性，可以返回觸發觸發器的SQL語句。

使用方法很簡單，看一個簡單的例子：

SQL> CREATE TABLE T (ID NUMBER, NAME VARCHAR2(30));

表已建立。

SQL> CREATE TABLE T_LOG (EXECUTE_DATE DATE, SQL_STATMENT CLOB);

表已建立。

SQL> CREATE OR REPLACE TRIGGER T
2 BEFORE INSERT OR UPDATE OR DELETE ON T
3 FOR EACH ROW
4 DECLARE
5 V_SQL_OUT ORA_NAME_LIST_T;
6 V_NUM NUMBER;
7 V_SQL_STATMENT VARCHAR2(32767);
8 BEGIN
9 V_NUM := ORA_SQL_TXT(V_SQL_OUT);
10 FOR I IN 1 .. V_NUM LOOP
11 V_SQL_STATMENT := V_SQL_STATMENT || V_SQL_OUT(I);
12 END LOOP;
13 INSERT INTO T_LOG (EXECUTE_DATE, SQL_STATMENT)
14 VALUES (SYSDATE, V_SQL_STATMENT);
15 END;
16 /

觸發器已建立

SQL> INSERT INTO T VALUES (1, 'A');

已建立 1 行。

SQL> INSERT INTO T (ID) VALUES (2);

已建立 1 行。

SQL> UPDATE T SET NAME = 'B' WHERE ID = 2;

已更新 1 行。

SQL> DELETE T;

已刪除2行。

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

會話已更改。

SQL> SELECT * FROM T_LOG;

EXECUTE_DATE SQL_STATMENT
------------------- -----------------------------------------------
2007-09-13 22:55:35 INSERT INTO T VALUES (1, 'A')
2007-09-13 22:55:46 INSERT INTO T (ID) VALUES (2)
2007-09-13 22:55:54 UPDATE T SET NAME = 'B' WHERE ID = 2
2007-09-13 22:56:00 DELETE T
2007-09-13 22:56:00 DELETE T

也可以利用VARCHAR2(4000)來代替CLOB，不過在插入的時候需要注意擷取前4000個字元。

注意，從9206及以上版本，Oracle不再允許DML觸發器中使用這個函式，只有在系統事件觸發器中才能獲取到SQL語句。

試圖在DML觸發器中使用ORA_SQL_TXT函式，這個函式返回NULL，而且傳入的OUT變數將不會被初始化。