Autonumbering Trigger : Create Trigger « Trigger « Oracle PL / SQL






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

1.create or replace trigger
2.Oracle's syntax for creating a trigger based on two tables
3.Trigger on each row
4.Use Sequence in a trigger
5.Empty trigger(before insert or update or delete)
6.Create tigger on wrapper table
7.This trigger sends messages over a pipe to record inserts into myStudent.
8.Cascade inserts into myStudent into session and lecturer.
9.Trigger is autonomous and hence the changes will be logged even if the original transaction rolls back.
10.Trigger Which Modifies a Mutating Table
11.Creating a Trigger with cursor inside
12.Use RAISE_APPLICATION_ERROR in a trigger
13.Show errors for a trigger
14.Submit job from a trigger
15.Use sysdate and user function in a trigger