Re: Auditing and Use the Triggers

From: Thomas J Kyte <tkyte_at_us.oracle.com>
Date: 1996/04/26
Message-ID: <4lqs3l$s6a_at_inet-nntp-gw-1.us.oracle.com>#1/1

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
>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. Received on Fri Apr 26 1996 - 00:00:00 CEST