Oracle Database Unique ID In Oracle 12c
Auto Numbering In Oracle 12c - without trigger
 
Oracle Database Unique ID Trigger
The before insert trigger shown below can be used to auto populate unique identifiers during data load into an Oracle database table. The after update trigger can be used to manage data (status) change. If each update date/time and other details needs to be captured, an audit table has to be created and a trigger to track the changes.

Sequence and Before Insert Trigger
-- 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
-- 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;
/

Oracle registered trademark of Oracle Corporation.

Last Revised On: February 23, 2012

  377