Oracle uses triggers to generate auto-increment primary keys for tables

-- Create t_account

Create Table t_account (
Accountid number (8) Not null,
Accountname varchar (100) not null
) Tablespace portalone;
Alter table t_account add constraint pk_account primary key (accountid) using index tablespace portaloneindx;

 

-- Sequence Creation
Create sequence s_account
Minvalue 1
Max value 99999999
Start with 1
Increment by 1
Cache 20;

 

-- Create a trigger
Create or replace trigger trigger_t_account
Before insert on t_account
For each row when (New. accountid is null)
Declare
Begin
-- Obtain the new sequence number from the sequence and assign it to the ID field of the new row
Select s_account.nextval into: New. accountid from dual;
-- Obtain the guid/UUID of the system to generate the primary key.
-- Select Sys. GUID () into: New. accountid from dual;
-- Obtain the system time as a field value of the table
-- Select to_char (sysdate, "YYYY-MM") into: New. year_month from dual;
End trigger_t_account;

 

-- Create t_order table
Drop table t_order;
Create Table t_order (
Orderid number (8) Not null,
Accountid number (8) Not null,
Ordername varchar (100) not null
) Tablespace portalone;
Alter table t_order add constraint pk_order primary key (orderid) using index tablespace portaloneindx;
Alter table t_order add constraint fk_order foreign key (accountid) References t_account (accountid );

 

-- Create Sequence
Create sequence s_order
Minvalue 1
Max value 99999999
Start with 1
Increment by 1
Cache 20;

 

-- Create a trigger
Create or replace trigger trigger_t_order
Before insert on t_order
For each row when (New. orderid is null)
Declare
Begin
Select s_order.nextval into: New. orderid from dual;
Select s_order.currval into: New. accountid from dual;
End trigger_t_order;

 

-- Create Table t_orderitem
Drop table t_orderitem;
Create Table t_orderitem (
Orderitemid number (8) Not null,
Orderid number (8) Not null,
Orderitemname varchar (100) not null
) Tablespace portalone;
Alter table t_orderitem add constraint pk_orderitem primary key (orderitemid) using index tablespace portaloneindx;
Alter table t_orderitem add constraint fk_orderitem foreign key (orderid) References t_order (orderid );

 

-- Sequence Creation
Create sequence s_orderitem
Minvalue 1
Max value 99999999
Start with 1
Increment by 1
Cache 20;

 

-- Create a trigger
Create or replace trigger trigger_t_orderitem
Before insert on t_orderitem
For each row when (New. orderitemid is null)
Declare
Begin
Select s_orderitem.nextval into: New. orderitemid from dual;
Select s_orderitem.currval into: New. orderid from dual;
End trigger_t_orderitem;

Share: