Re: Database Triggers Question

Alan D. Mills wrote:

> I can create triggers for events (insert, update, delete) for each
> record
> of a particular table in order to perform record level auditing.
>
> I have, for each table, three separate triggers, one for each action
> and
> the code is very similar in each. In fact, i's identical except the
> value
> inserted into the audit table that states that it is an insert, update
> or a
> delete taking place.
>
> I would like to combine these triggers into one trigger which fires on
> any
> of insert, update and delete. I know the syntax for this but is there
>
> anyway from within the trigger to identify the action that fired the
> trigger so that I could dynamically insert the value 'INSERT',
> 'UPDATE' or
> 'DELETE' into my audit table as appropriate.
>
> Any help appreciated.
>
> Alan D> Mills

   To find out which DML has caused to fire a trigger of INSERT or DELETE or UPDATE can be determined by the three functions defined in DBMS_STANDARD package in PL/SQL

1. INSERTING
2. DELETING
3. UPDATING. These functions are valid for both row level and statement level triggers.

A sample trigger is as follow:

CREATE OR REPLACE TRIGGER EMP_TRIGGER
AFTER INSERT OR UPDATE OR DELETE ON emp
BEGIN
   IF INSERTING THEN // TRUE when INSERT statement is issued on emp table.

      ...
   END IF;
   IF UPDATING THEN // TRUE when UPDATE statement is issued on emp table.

      ...
   END IF;
   IF DELETING THEN // TRUE when DELETING statement is issued on emp table.

      ...
   END IF;
END; I think this will help you, if you need any explanation please feel free to contact me.

Jamshaid Raza
jamshaid.raza_at_cressoft.com.pk Received on Wed Dec 31 1997 - 00:00:00 CST