自动通过trigger捕获DML语句
这是一个通过触发器来监控DML语句的例子.
SQL> desc t1
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
A NUMBER
自动通过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;
我在原创的基础上加了时间字段,这样就可以看到是什么时间操作的。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7490392/viewspace-1008315/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/7490392/viewspace-1008315/