Re: Trigger + sequence

Answer inline...

Detlef Kandziora wrote:

> 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 );

instead of "insert" statement, try :

    select dept_id_seq.nextval

      into :new.dept_id
      from dual;

> 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 - 15:12:35 CDT