--为了获取针对bruce表的DML语句建立的triger create or replace trigger get_bruce_SQL BEFORE DELETE OR INSERT OR UPDATE ON bruce declare n number; sqltxt varchar2(4000); sql_text ora_name_list_t; begin n := ora_sql_txt(sql_text);FOR i IN 1..n LOOP sqltxt := sqltxt || sql_text(i); END LOOP;
结合biti的9i自动捕获DML的试验过程结合biti的9i自动捕获DML的试验过程 --建立测试表brucecreate table bruce(a number(1));--建立存储DML语句的表bruce_sqlcreate table bruce_sql( USERNAME VARCHAR2(30), CLIENT_IP VARCHAR2(20), SQL_TEXT VARCHAR2(4000), TABLE_NAME VARCHAR2(30) ); --为了获取针对bruce表的DML语句建立的triger create or replace trigger get_bruce_SQL BEFORE DELETE OR INSERT OR UPDATE ON bruce declare n number; sqltxt varchar2(4000); sql_text ora_name_list_t; begin n := ora_sql_txt(sql_text);FOR i IN 1..n LOOP sqltxt := sqltxt || sql_text(i); END LOOP;--以下二选一 /*如果要保留客户机ip*/ insert into bruce_sql(USERNAME,CLIENT_IP,SQL_TEXT,TABLE_NAME) values(user,sys_context('userenv','ip_address'),sqltxt,'bruce');/*如果要保留服务器ip*/ insert into bruce_sql(USERNAME,CLIENT_IP,SQL_TEXT,TABLE_NAME) values(user,utl_inaddr.get_host_address,sqltxt,'bruce');end; /--测试ok注意是当DML没有commit的时候,也会出现在bruce_sql表中的,所以不要完全的依靠这个表作为依据,当然只要语句执行rollback后,就会从bruce_sql中消失,如:SQL> insert into bruce values(1);1 row created.SQL> select * from bruce_sql;USERNAME CLIENT_IP ------------------------------ -------------------- SQL_TEXT ------------------------------------------------------------------------------ TABLE_NAME ------------------------------ BILL insert into bruce values(1) bruce SQL> rollback;Rollback complete.SQL> select * from bruce_sql;no rows selected附: ora_name_list_t is datatype ora_sql_text is function
--为了获取针对bruce表的DML语句建立的triger
create or replace trigger get_bruce_SQL
BEFORE DELETE OR INSERT OR UPDATE ON bruce
declare
n number;
sqltxt varchar2(4000);
sql_text ora_name_list_t;
begin
n := ora_sql_txt(sql_text);FOR i IN 1..n LOOP
sqltxt := sqltxt || sql_text(i);
END LOOP;
结合biti的9i自动捕获DML的试验过程结合biti的9i自动捕获DML的试验过程
--建立测试表brucecreate table bruce(a number(1));--建立存储DML语句的表bruce_sqlcreate table bruce_sql(
USERNAME VARCHAR2(30),
CLIENT_IP VARCHAR2(20),
SQL_TEXT VARCHAR2(4000),
TABLE_NAME VARCHAR2(30)
);
--为了获取针对bruce表的DML语句建立的triger
create or replace trigger get_bruce_SQL
BEFORE DELETE OR INSERT OR UPDATE ON bruce
declare
n number;
sqltxt varchar2(4000);
sql_text ora_name_list_t;
begin
n := ora_sql_txt(sql_text);FOR i IN 1..n LOOP
sqltxt := sqltxt || sql_text(i);
END LOOP;--以下二选一
/*如果要保留客户机ip*/
insert into bruce_sql(USERNAME,CLIENT_IP,SQL_TEXT,TABLE_NAME)
values(user,sys_context('userenv','ip_address'),sqltxt,'bruce');/*如果要保留服务器ip*/
insert into bruce_sql(USERNAME,CLIENT_IP,SQL_TEXT,TABLE_NAME)
values(user,utl_inaddr.get_host_address,sqltxt,'bruce');end;
/--测试ok注意是当DML没有commit的时候,也会出现在bruce_sql表中的,所以不要完全的依靠这个表作为依据,当然只要语句执行rollback后,就会从bruce_sql中消失,如:SQL> insert into bruce values(1);1 row created.SQL> select * from bruce_sql;USERNAME CLIENT_IP
------------------------------ --------------------
SQL_TEXT
------------------------------------------------------------------------------
TABLE_NAME
------------------------------
BILL
insert into bruce values(1)
bruce
SQL> rollback;Rollback complete.SQL> select * from bruce_sql;no rows selected附:
ora_name_list_t is datatype
ora_sql_text is function