-- Create a database Sequence
CREATE SEQUENCE mfg_operation_seq 
START WITH 1 INCREMENT BY 1
NOCYCLE;

-- Before Insert Trigger:
-- To populate mfg_operation_id with a unique 
-- value on insert of a row into the table 
-- mfg_schedule_status

CREATE OR REPLACE TRIGGER mfg_operation_id_bi_trig  
BEFORE INSERT ON mfg_schedule_status
FOR EACH ROW

BEGIN
  /*-- Old syntax
  SELECT mfg_operation_seq.NEXTVAL
  INTO   :NEW.mfg_operation_id
  FROM   DUAL;
  */

 :NEW.mfg_operation_id := mfg_operation_seq.NEXTVAL;  

END mfg_operation_id_bi_trig;
/

-- After Update Trigger:
-- This overwrites the updated date for every update

CREATE OR REPLACE TRIGGER mfg_operation_au_trig 
AFTER UPDATE OF mfg_status ON mfg_schedule_status
FOR EACH ROW

BEGIN
  IF :NEW.mfg_status = 'Complete' THEN
    :NEW.completed_date := SYSDATE;
  ELSE
    :NEW.updated_date := SYSDATE;
  END IF;
END mfg_operation_au_trig;
/