pl sql trigger. inserted and deleted tables

Question

In sql server when firing a trigger, 2 temporarily tables are created. One is the inserted table which contains the rows of the original table the trigger belongs to in which an insertion or update has occurred (it contains the :NEW values); the other one is the deleted table, which similarly contains the OLD values.

In sql Server it is possible to use these tables inside the trigger in order to have informations on OLD and NEW values. (to make an easy axample: it is possible, inside the trigger to make a "SELECT * FROM INSERTED", so to select all the rows in which an update has been made or all the inserted rows in case of an insertion).

The cool thing about this is that it becomes possible to dynamically call the :NEW.value and :OLD.value, if, like in my case, I want to usse the same trigger on more tables (which are also different one another).

Is there something similar fo Oracle pl sql?

Answer 1

You want to use a referencing clause to your trigger. The official diagram is here, but I find this page explains it a bit better.

Basically, you create your trigger as such:

CREATE OR REPlACE TRIGGER trigger_name
AFTER DELETE OR INSERT OR UPDATE
ON table_name
REFERENCING OLD AS oldAlias AND NEW AS newAlias
...

Now you can reference the pre-trigger and post-trigger values as :oldAlias.column_name and :newAlias.column_name.

For example, this would check to see if the personnel_type value was updated from 1 to 2:

IF :oldAlias.personnel_type = 1 AND :newAlias.personnel_type = 2 THEN 
  -- do something
END IF ;

From what I can gather, it sounds like you're wanting to create one single trigger to monitor multiple tables. Oracle triggers don't work like that. Each table you wish to monitor must have its own separate trigger. You could, however, either:

• Call a procedure/function from the trigger, passing it the values in question (just make sure not to get stuck in a recursive/mutating trigger doing this
• Create a separate audit table with its own trigger. When your source tables are updated, you push changes/data (such as table names or columns) to the audit table. Then the audit table's trigger could deal with that however you wish.

Answer 2

I think you are taking about audting the table,please find the trigger for auditing on insertion ,updation and deletion

EDIT :As phantom suggest you need to check each column for new and old value before inserting into audit table .This way you can achieve what column value changes .But for that you need to insert multiple rows into audit table ,if multiple column have been updated of the same record.

My idea is to create a replica of the same record before updating /deleting /inserting into audit table.And when you want the change ,compare with the previous record in audit table based on source_action/source_rowid

    CREATE TABLE table_for_audit
    (
       col1       NUMBER,
       col2       NUMBER,
       col3       NUMBER,
       cre_date   DATE,
       cre_user   VARCHAR2 (200)
    );

    CREATE TABLE audit_table
    (
       col1            NUMBER,
       col2            NUMBER,
       col3            NUMBER,
       cre_date        DATE,
       cre_user        VARCHAR2 (200),
       source_action   VARCHAR2 (1),
       source_rowid
    );

    CREATE OR REPLACE TRIGGER trg_table_audit
       BEFORE INSERT OR UPDATE OR DELETE
       ON table_for_audit
       REFERENCING NEW AS NEW OLD AS OLD
       FOR EACH ROW
    DECLARE
       v_source_action   VARCHAR2 (1);
    BEGIN
       IF INSERTING
       THEN
          v_source_action := 'I';
          v_source_rowid := :NEW.ROWID;
       ELSIF UPDATING
       THEN
          v_source_action := 'U';
          v_source_rowid := :OLD.ROWID;
       ELSIF DELETING
       THEN
          v_source_action := 'D';
          v_source_rowid := :OLD.ROWID;
       END IF;


       IF INSERTING OR UPDATING
       THEN
          INSERT INTO audit_table (col1,
                                   col2,
                                   col3,
                                   cre_date,
                                   cre_user,
                                   source_action,
                                   source_rowid)
               VALUES (:NEW.col1,
                       :NEW.col2,
                       :NEW.col3,
                       SYSDATE,
                       SYS_CONTEXT ('USERENV', 'CURRENT_USER'),
                       v_source_action,
                       v_source_rowid);
       ELSIF DELETING
       THEN
          INSERT INTO audit_table (col1,
                                   col2,
                                   col3,
                                   cre_date,
                                   cre_user,
                                   source_action,
                                   source_rowid)
               VALUES (:OLD.col1,
                       :OLD.col2,
                       :OLD.col3,
                       SYSDATE,
                       SYS_CONTEXT ('USERENV', 'CURRENT_USER'),
                       v_source_action,
                       v_source_rowid);
       END IF;
    END;
    /

Please correct if i am wrong in my unnderstanding .thanks