Oracle create trigger error (bad bind variable)

Question

I at trying to create trigger with the following code.

CREATE OR REPLACE TRIGGER MYTABLE_TRG 
BEFORE INSERT ON MYTABLE 
FOR EACH ROW 
BEGIN 
 select MYTABLE_SEQ.nextval into :new.id from dual; 
END;

I am getting error

Error(2,52): PLS-00049: bad bind variable 'NEW.ID'

Any ideas? Thanks.

Solution 1

It seems like the error code is telling you there's no such column ID in your table...

Solution 2

Somehow your environment is treating your code as SQL instead of a DDL statement. This works for me (running in sqlplus.exe from a command prompt):

SQL> create sequence mytable_seq;

Sequence created.

SQL> create table mytable (id number);

Table created.

SQL> CREATE OR REPLACE TRIGGER MYTABLE_TRG
  2  BEFORE INSERT ON MYTABLE
  3  FOR EACH ROW
  4  BEGIN
  5   select MYTABLE_SEQ.nextval into :new.id from dual;
  6  END;
  7  /

Trigger created.

Note the trailing "/" - this might be important in the application you are compiling this with.