Oracle9i automatically captures DML statements through trigger |
|
|
SQL> Create Table T1 (a number); the table has been created. 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); the table has been created. 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/The trigger has created an SQL> SQL> insert into T1 values (1); 1 row has been created. SQL> commit; submitted completely. SQL> select * From t_ SQL 2; username client_ip -------------------------------------------------- SQL _text ---------------------------------------------------- Table_name owner --------------------------------------------------- System Insert into T1 values (1) T1 rainy |
|
In addition:
Ora_ SQL _txt is supported by 9i, so how can I obtain the SQL statement through event trigger in 8i?
Quote:
SQL> Create or replace trigger snapshot_too_old
2 after servererror on Database
Declare
Rochelle 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.
After searching, both ora_ SQL _txt and 9i documents are supported;
In 8i, how does one obtain the SQL statement that triggers the trigger in event trigger?