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