CODEWITHSUNDEEP
sqloracle-databasetriggers
lbear
lbear

Reputation: 820

Use trigger to generate a primary key in oracle

New I want to create a trigger in my oracle database to generate the primary key for every new row. The pk contains two parts, the firset is to_char(sysdate, 'yyyyMMddHH24mmss') and the second part is an ID that is generated by a sequence, like to_char(SEQ_A_ID,FM000000). SEQ_A is an int sequence starts from 1 and the increment is 1. My pk data type is a varchar2(20) .

Now I write SQL like:

CREATE OR REPLACE TRIGGER "DEMO"."TRIGGER_A_ID" BEFORE INSERT ON "DEMO"."A" REFERENCING OLD AS "OLD" NEW AS "NEW" FOR EACH ROW ENABLE
BEGIN
   select to_char(sysdate,‘yyyyMMddHH24mmss’) || to_char(SEQ_A_ID.nextval,'FM00000') into :new.id  from dual;
END;;

The SQL above has some mistakes, but I don't know the right way to put the result of select statement into my pk.

Upvotes: 1

Views: 3536

Answers (1)

Rene
Rene

Reputation: 10541

create or replace trigger "DEMO"."TRIGGER_A_ID"
   before insert on "DEMO"."A"
   referencing old as "OLD" new as "NEW"
   for each row enable

   l_id varchar2(20);
begin
   select to_char(sysdate, ‘yyyymmddhh24mmss’) || to_char(seq_a_id.nextval, 'FM00000')
     into l_id
     from dual;

     :new.id:= l_new_id;
end;

Upvotes: 2

Related Questions