Search code examples
oracleoracle11gdatabase-trigger

Bind variable in SQL Developer with Oracle 11g

I have problem when trying to excute a sequence with a before insert trigger.

CREATE TABLE personne (ID number, nom varchar2(250 char));

CREATE SEQUENCE s_inc_pers START WITH 1 INCREMENT BY 1;

CREATE TRIGGER tr_inc_pers ON t1 BEFORE INSERT
FOR EACH ROW
DECLARE
BEGIN
  select s_inc_pers into :new.t1.ID from DUAL;
END.
Solution

  • Oracle reports a bad bind variable if you try to reference something with :NEW that is not a column in the target table. In this case it may be prompting you for a bind value as the statement is malformed.

    The NEW pseudorecord refers to the row in the triggering table that is being affected by the statement. You don't need to (and mustn't) include the table name when you use the pseudorecord field, so :new.t1.ID should just be :new.ID.

    To get the next value from the sequence you need to use nextval, you can't only provide the sequence name.

    Your clauses are also in the wrong order, you need the DML event (insert) befroe the target table.

    CREATE TRIGGER tr_inc_pers BEFORE INSERT ON t1
FOR EACH ROW
BEGIN
  select s_inc_pers.nextval into :new.ID from DUAL;
END;

    As you are using 11g you don't even need to select from dual, you can just assign the column value:

    CREATE TRIGGER tr_inc_pers BEFORE INSERT ON t1
FOR EACH ROW
BEGIN
  :new.ID := s_inc_pers.nextval;
END;