-- After Insert/Update/Delete Trigger to 
-- populate mfg_operation_id, initial and
-- current mfg_status for auditing purposes
-- (can be enhanced to include username etc.)

CREATE OR REPLACE TRIGGER mfg_sch_status_aiud_trig
 AFTER INSERT OR UPDATE OR DELETE ON 
 Mfg_Schedule_Status
 FOR EACH ROW
BEGIN
 IF INSERTING THEN
   INSERT INTO Mfg_Schedule_Status_Audit
    (mfg_operation_id, initial_mfg_status, created_date)
   SELECT
    :NEW.mfg_operation_id, :NEW.mfg_status, SYSDATE
   FROM DUAL;
 ELSIF UPDATING THEN
   INSERT INTO Mfg_Schedule_Status_Audit
    (mfg_operation_id,
     initial_mfg_status, current_mfg_status, updated_date)
   SELECT
    :NEW.mfg_operation_id,
    :OLD.mfg_status, :NEW.mfg_status, SYSDATE
   FROM DUAL;
 ELSIF DELETING
   INSERT INTO Mfg_Schedule_Status_Audit
    (mfg_operation_id, current_mfg_status, updated_date)
   SELECT
    :OLD.mfg_operation_id, :OLD.mfg_status, SYSDATE 
   FROM DUAL;
 END IF;

END mfg_sch_status_aiud_trig;
/

DESC Mfg_Schedule_Status_Audit
Name                          Null?      Type
----------------------------- ---------- ---------------
mfg_operation_id              NOT NULL   NUMBER
initial_mfg_status                       VARCHAR2(30)
current_mfg_status                       VARCHAR2(30)
created_date                             DATE
updated_date                             DATE