oracle触发器捕获dml语句,自动通过trigger捕获DML语句

自动通过trigger捕获DML语句

这是一个通过触发器来监控DML语句的例子.

SQL> desc t1

Name Null? Type

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

A NUMBER[@more@]

自动通过trigger捕获DML语句

SQL> desc t1

Name Null? Type

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

A NUMBER

SQL> desc t_sql

Name Null? Type

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

USERNAME VARCHAR2(30)

CLIENT_IP VARCHAR2(20)

SQL_TEXT VARCHAR2(4000)

TABLE_NAME VARCHAR2(30)

OWNER VARCHAR2(30)

fromdate date

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,fromdate)

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

15

16 end;

17 /

Trigger created.

SQL> insert into t1(a) values(1);

1 row created.

SQL> update t1 set a = 2 where a = 1;

1 row updated.

SQL> delete t1;

3 rows deleted.

SQL> commit;

Commit complete.

SQL> select * from t_sql;

我在原创的基础上加了时间字段，这样就可以看到是什么时间操作的。