CODEWITHSUNDEEP
sqloracle-databaseplsqltriggers
steff_bdh
steff_bdh

Reputation: 1188

pl sql trigger insert values from a sequence

create table userdetail(
  userdetailsid number(3) not null,
  userid varchar2(5),
  firstname varchar2(15),
  lastname varchar2(15),
  email varchar2(15),
  primary key (userdetailsid)
);

create sequence Iseq
start with 1
increment by 1;

create or replace trigger userDetailInsert
before insert on userdetail
begin
  :userdetailsid:=Iseq.nextval;
end;

the error I get is as follows:

Error at line 2: PLS-00049: bad bind variable 'USERDETAILSID'

Upvotes: 1

Views: 2758

Answers (1)

Trinimon
Trinimon

Reputation: 13957

Remove : from :userdetailsid or use :old./:new in combination with for each row:

create or replace trigger userDetailInsert
before insert on userdetail for each row
begin
   :new.userdetailsid:=Iseq.nextval;
end;

If you use a : the column name is considered to be a bind variable.

Generally said, if you want to reference old or new values use :old.userdetailsid resp. :new.userdetailsid.

Upvotes: 2

Related Questions