Trigger + sequence

I have a problem to realize a trigger to insert sequence-numbers into a primary key.

the table and the sequence itself works fine, if I do it by sql-insert-statement like this:

insert into departement (dept_id, dept) values (dept_id_seq.nextval, 'XXX') but I need it automatically because of an external application via ODBC. Here is my Trigger-construction and the error-message:

create trigger dept_insert
BEFORE insert on departement
for each row
begin

   insert into departement values (dept_id_seq.nextval, :new.dept ); end;

Statement processed.

SQLWKS> insert into departement (dept_id, dept) values(null, 'XXXX');

ORA-01722: invalid number
ORA-06512: at "RZADMIN.DEPT_INSERT", line 2
ORA-04088: error during execution of trigger 'RZADMIN.DEPT_INSERT'

The null-value should be overritten by the trigger !?

        Can anybody help about that ? thanks Detlef Received on Mon Jun 07 1999 - 14:44:58 CDT