ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle9i 怎样自动通过trigger捕获DML 语句(转)

oracle9i 怎样自动通过trigger捕获DML 语句(转)

原创 Linux操作系统 作者:lottery009 时间:2019-01-31 17:12:05 0 删除 编辑
oracle9i 怎样自动通过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)

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 /

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;

USERNAME CLIENT_IP SQL_TEXT
------------------------------ -------------------- ------------------------------
TABLE_NAME OWNER
------------------------------ ------------------------------
RAINY 10.1.30.19 insert into t1(a) values(1)
T1 RAINY

RAINY 10.1.30.19 update t1 set a = 2 where a =
1
T1 RAINY

RAINY 10.1.30.19 delete t1
T1 RAINY

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/644902/viewspace-159246/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2005-10-18

  • 博文量
    10
  • 访问量
    5588