oracle dml触发器 获取当前执行sql语句,oracle9i自动通过trigger捕获DML 语句

SQL> create table t1(a number);

表已创建。

SQL> create table t_sql(username varchar2(30),

2  client_ip varchar2(20),sql_text varchar2(4000),

3  table_name varchar2(30),owner varchar2(30));

表已创建。

SQL> create or replace trigger capt_sql

2   BEFORE DELETE OR INSERT OR UPDATE ON t1

3   declare

4   n        number;

5   stmt     varchar2(4000);

6   sql_text ora_name_list_t;

7   begin

8   n := ora_sql_txt(sql_text);

9   FOR i IN 1..n LOOP

10    stmt := stmt || sql_text(i);

11   END LOOP;

12

13   insert into t_sql(USERNAME,CLIENT_IP,SQL_TEXT,TABLE_NAME,OWNER)

14               values(user,sys_context('userenv','ip_address'),stmt,'T1','RAINY');

15

16   end;

17  /

触发器已创建

SQL>

SQL> insert into t1 values(1);

已创建 1 行。

SQL> commit;

提交完成。

SQL> select * from t_sql

2  ;

USERNAME                       CLIENT_IP

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

SQL_TEXT

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

TABLE_NAME                     OWNER

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

SYSTEM

insert into t1 values(1)

T1                             RAINY