Autonumbering 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>
Related examples in the same category