CODEWITHSUNDEEP
sqloracle-databasefunctionsequencedatabase-trigger
Pankaj Kumar
Pankaj Kumar

Reputation: 41

Specific Sequence using Trigger & Sequence in Oracle SQL

I have a SQL Table for which I have written Triggers and Sequence which inserts 'I', 'U', 'D' in a column every time a record is inserted, deleted or updated. I also needed a sequence every time a new record is inserted in the table so I used a sequence for that.

 CREATE TABLE "USER_DETAILS" 
   (  
  "ID" NUMBER(10,0) GENERATED ALWAYS as IDENTITY(START with 1 INCREMENT by 1), 
  "NAME" VARCHAR2(50 BYTE), 
  "CONTACT" NUMBER(10,0),
  "STATUS" CHAR(1),
  SEQUENCE VARCHAR2(50)
);

My output is like this, the sequence number starts with A00001 as per the sequence I wrote.

ID  NAME     CONTACT  STATUS SEQUENCE

1   USER1   123456789   I     A00001
2   USER2   123456789   I     A00002

This is my trigger for "STATUS"

create or replace trigger insert_trigger before insert on USER_DETAILS for each row

begin
  insert into USER_DETAILS
    (ID,NAME,CONTACT,STATUS, SEQUENCE)
  values
    (:new.id,:new.name,:new.contact,'I',NULL);

end;

and this is my Sequence for the field "Sequence".

create or replace trigger insert_trigger before insert on USER_DETAILS for each row
begin
  :new.SEQUENCE := 'A'||to_char(REF_SEQUENCE.nextval,'FM00000');
 end;

But now the requirement is to start the sequence with the first alphabet of the field "Name". For example: If the Name is "User1" then the sequence should be "U00001" ,if the Name is "Random1" then the sequence should be "R00001"

I don't know how to achieve his functionality. Help!!!.

Upvotes: 1

Views: 1733

Answers (1)

Barbaros Özhan
Barbaros Özhan

Reputation: 65218

You may create sequence like the following by using lpad and substr string operators :

create or replace trigger insert_trigger before insert on USER_DETAILS for each row
begin
  :new.SEQUENCE := substr(:new.name,1,1)||lpad(to_char(REF_SEQUENCE.nextval),5,'0');
 end;

P.S. do not use deprecated data type CHAR, use VARCHAR2 for all of your string type columns, instead.

Upvotes: 2

Related Questions