Implementing Autonumbering Functionality : Before Event Trigger « Trigger « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Trigger
3. Before Event Trigger

SQL>
SQL>
SQL> CREATE SEQUENCE idSeq;

Sequence created.

SQL>
SQL> CREATE TABLE myTable (Name VARCHAR(50) PRIMARY KEY NOT NULL,
  2                       PhoneNo VARCHAR(15));

Table created.

SQL>
SQL> CREATE TABLE myTableAudit
  2  (id INT PRIMARY KEY NOT NULL,
  3   Operation VARCHAR(10),
  4   RecordedOn DATE DEFAULT SysDate,
  5   OldName VARCHAR(50),
  6   NewName VARCHAR(50),
  7   OldPhone VARCHAR(15),
  8   NewPhone VARCHAR(15));

Table created.

SQL>
SQL> CREATE OR REPLACE TRIGGER idAutonumberTrigger
  2  BEFORE INSERT ON myTableAudit
  3  FOR EACH ROW
  4  BEGIN
  5     SELECT idSeq.NEXTVAL
  6     INTO :NEW.id FROM DUAL;
  7  END;
  8  /

Trigger created.

SQL>
SQL> drop SEQUENCE idSeq;

Sequence dropped.

SQL>
SQL> drop table myTable;

Table dropped.

SQL>
SQL> drop table myTableAudit;

Table dropped.

SQL>