Monday, June 17, 2013

Oracle Pl/Sql Trigger

Simple trigger when Insert,Update and Delete a record.Normally trigger use for several scenarios and following example shows adding a rerecord to history table for one of scenario described in above.

CREATE OR REPLACE TRIGGER CCBS2.TRIG_IUD_TEMP_TABLE_SAMPLE
 AFTER
  INSERT OR UPDATE OR DELETE
 ON TEMP_TABLE_SAMPLE REFERENCING NEW AS NEW OLD AS OLD
 FOR EACH ROW
DECLARE

  vc_operation VARCHAR2(10)  := NULL;--this used for handle scenarios 
  wk_Field1        TEMP_TABLE_SAMPLE.Field1%type;
  wk_Field2        TEMP_TABLE_SAMPLE.Field2%type;
  wk_Field3           TEMP_TABLE_SAMPLE.Field3%type;
  wk_reason        varchar2(30);

BEGIN

 IF INSERTING THEN
    vc_operation    := 'INSERT'; 
    wk_Field1       := :NEW.Field1;   
    wk_Field2       := :NEW.Field2;
    wk_Field3       := :NEW.Field3;
    wk_reason       := 'Inserting record';
 END IF;

 IF UPDATING THEN
    vc_operation    := 'UPDATE'; 
    wk_Field1       := :NEW.Field1;   
    wk_Field2       := :NEW.Field2;
    wk_Field3       := :NEW.Field3;
    wk_reason       := 'Updating record';   
 END IF;

 IF DELETING THEN
    vc_operation    := 'DELETE'; 
    wk_Field1       := :NEW.Field1;   
    wk_Field2       := :NEW.Field2;
    wk_Field3       := :NEW.Field3;
    wk_reason       := 'deleting records';   
 END IF;

BEGIN
   INSERT INTO CCBS2.TEMP_TABLE_SAMPLE_H(       Field1_h,
                                                Field2_h,
                                                Field3_h
                                                    )
                                 VALUES (       wk_Field1,
                                                wk_Field2,
                                                wk_Field3
                                                    );
EXCEPTION
     WHEN OTHERS THEN
       RAISE;

END;

END;
/

No comments:

Post a Comment