Re: Auditing and Use the Triggers

Thomas J Kyte (tkyte_at_us.oracle.com) wrote:
: create or replace trigger testing
: after insert or update or delete on T
: for each row
: begin
: if UPDATING then
: ....
: elsif INSERTING then
: ....
: elsif DELETING then
: ....
: end;
: end;
: /
 

: UPDATING, INSERTING, DELETING are builtins used within the scope of a trigger,
: they act like boolean functions and will tell you if you are being fired due to
: an insert or an update or a delete.
 

: This is documented in the pl/sql guide as well as the application developers
: guide.
 

: mlanda_at_vnet.ibm.com wrote:
: >I must be missing something simple!
: >I am trying to audit the use of some tables, specifically, inserts,
: >updates and deletes. Is there a way within a DB trigger to determine
: >what type of action the user is attempting (i.e., update)? It would

	I do not know exactly what you are looking for , but one way
	of doing this is by writing a trigger like this:-

		create or replace trigger test 
		before insert or delete or update on Table
		for each row
		begin
		IF INSERTING THEN
			insert into table_log
				values(:new..............,'insert');
		else if deleting then
			insert into table_log
				values(:old..............,'delete');
		else 
			insert into table_log
				values(:old..............,'old');
			insert into table_log
				values(:new..............,'new');		
		end if;
		end if;
		end;

: >be nice if there were some kind of variable I could look at such
: >as the "user" variable. I have not been able to find anything in
: >the docs. What have I tried so far? I have set auditing on for a
: >given table (insert,update,delete by access whenever successful)
: >and can see the action that took place by looking at
: >Audit_Trail.Action_Name using a select statement after the DML operation
: >(let's say an update statement). But, if I use the same statement in a
: >procedure called by a DB trigger (on update,delete,insert, after,
: >for each row) it seems that the trigger fires BEFORE the view "Audit_Trail"
: >(I have also tried view User_Audit_Object) is updated with the current
: >audit data. Therefore, the trigger is returning the previous DML
: >operation (action) and not the current one. Also, it seems that if you
: >rollback a transaction the auditing data is not rolled back. So if
: >I just did an insert on the audited table, then issued rollback, the
: >transaction gets rolled back but I still see the audit data for
: >that transaction in view audit_trail. Thanks.

: >M.Landa

: Thomas Kyte
: tkyte_at_us.oracle.com
: Oracle Government

: --------------------------------------------------------
: opinions and statements are mine and do not necessarily
: reflect the opinions of Oracle Corporation.

                                                                Snehasis Received on Sun Apr 28 1996 - 00:00:00 CEST