Ještě dva trigery, pro DML

SQL> create table critical_tab_audit (
 2 SDDELN NUMBER,
 3 AFTER_SDDELN NUMBER,
 4 SDDOCO NUMBER,
 5 AFTER_SDDOCO NUMBER,
 6 SDLNID NUMBER,
 7 AFTER_SDLNID NUMBER,
 8 user_name VARCHAR2(30),
 9 v_addr VARCHAR2(11),
 10 TERMINAL varchar2(255),
 11 HOST varchar2(255),
 12 action VARCHAR2(30),
 13 upd_date DATE);

Table created.

SQL> CREATE OR REPLACE TRIGGER audit_critical_tab
 2 AFTER UPDATE OF SDDELN, SDDOCO, SDLNID ON user1.critical_tab
 3 FOR EACH ROW when (new.SDDELN=0)
 4 BEGIN
 5 INSERT INTO critical_tab_audit VALUES (:old.SDDELN,:new.SDDELN,:old.SDDOCO,:new.SDDOCO,:old.SDLNID,:new.SDLNID,User,o
SERENV','TERMINAL'),sys_context('USERENV','HOST'),'UPDATE',Sysdate);
 6 END;
 7 /

Trigger created.

SQL> CREATE TABLE AUDIT_DDL (
 2 d date,
 3 OSUSER varchar2(255),
 4 CURRENT_USER varchar2(255),
 5 HOST varchar2(255),
 6 TERMINAL varchar2(255),
 7 owner varchar2(30),
 8 type varchar2(30),
 9 name varchar2(30),
 10 sysevent varchar2(30));

Table created.

SQL> create or replace trigger AUDIT_DDL
 2 AFTER UPDATE OF SDDELN, SDDOCO, SDLNID ON user1.critical_tab
 3 FOR EACH ROW when (new.SDDELN=0)
 4 begin
 5 insert into audit_ddl(d, osuser,current_user,host,terminal,owner,type,name,sysevent)
 6 values(
 7 sysdate,
 8 sys_context('USERENV','OS_USER') ,
 9 sys_context('USERENV','CURRENT_USER') ,
 10 sys_context('USERENV','HOST') ,
 11 sys_context('USERENV','TERMINAL') ,
 12 ora_dict_obj_owner,
 13 ora_dict_obj_type,
 14 ora_dict_obj_name,
 15 ora_sysevent
 16 );
 17 end;
 18 /

Trigger created.

SQL> select * from critical_tab_audit;

SDDELN AFTER_SDDELN SDDOCO AFTER_SDDOCO SDLNID AFTER_SDLNID USER_NAME V_ADDR
------ ------------ ------ ------------ ------ ------------ -------------------- -----------
TERMINAL
-------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
HOST
-------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
ACTION UPD_DATE
---------- ---------
 ### 0 ### ### ### ### CONSULT
unknown
ntb01prg
UPDATE 25-MAY-14

SQL> select * from audit_ddl;

D
---------
OSUSER
-------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
CURRENT_USER
-------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
HOST
-------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
TERMINAL
-------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
OWNER TYPE NAME SYSEVENT
------------------------------ ------------------------------ ------------------------------ ----------------------
25-MAY-14
tscpkc2
SYSTEM
PC01KAN2
unknown

Komentáře