oracle9i自动通过trigger捕获DML 语句(zz)

最新推荐文章于 2023-08-04 11:35:31 发布

llmmysun

最新推荐文章于 2023-08-04 11:35:31 发布

阅读量1.7k

点赞数

分类专栏： ORACLE 数据库管理文章标签： oracle sql insert table compilation login

ORACLE 数据库管理专栏收录该内容

40 篇文章 0 订阅

订阅专栏

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

另外:

ora_sql_txt是9i才支持的东西；那么8i下通过event trigger如何得到sql那

quote:

SQL> create or replace trigger snapshot_too_old
2 after servererror on database
declare
l_sql_text ora_name_list_t;
3 4 5 l_n number;
6 begin
7 if ( is_servererror(1555) )
8 then
9 insert into oerr_tri_tab values ( 'ora_sysevent = ' || ora_sysevent );
insert into oerr_tri_tab values ( 'ora_login_user = ' || ora_login_user );
10 11 insert into oerr_tri_tab values ( 'ora_server_error = ' || ora_server_error(1));
12 l_n := ora_sql_txt( l_sql_text );
13 for i in 1 .. l_n
loop
14 15 insert into oerr_tri_tab values ( 'l_sql_text(' || i || ') = ' || l_sql_text(i) );
16 end loop;
17 end if;
end; 18
19 /

Warning: Trigger created with compilation errors.

SQL> show errors
Errors for TRIGGER SNAPSHOT_TOO_OLD:

LINE/COL ERROR
-------- -----------------------------------------------------------------
10/12 PL/SQL: Statement ignored
10/19 PLS-00201: identifier 'ORA_SQL_TXT' must be declared
SQL> drop trigger snapshot_too_old;

Trigger dropped.