Re: Sequence Number and Triggers

>Every time a record is inserted into a table I would like the trigger to
>update the PK before insert with a sequence number.
>We are unable to alter our current insert statements to complete the
>operation. The inserts are also used on Ms Sql Server and Ms Access.
>Below I have listed some examples that do not work yet. Please help.
>
>
>Andy Robbins
>ProCard Inc
>arobbins_at_procard.com
>====================
>
>CREATE OR REPLACE TRIGGER TR_ANDY_TEST
> BEFORE INSERT ON TB_ANDY_TEST
> FOR EACH ROW
> BEGIN
> :NEW.DT= DT_SEQ.NEXTVAL
> END;
>/
>
>CREATE OR REPLACE TRIGGER TR_ANDY_TEST
> BEFORE INSERT ON TB_ANDY_TEST
> FOR EACH ROW
> DECLARE newrow number;
>
> BEGIN
> newrow = DT_SEQ.NEXTVAL
> END;/
>

Assuming the PK in your table is the column DT, then you should be able to create a trigger

CREATE OR REPLACE TRIGGER TR_ANDY_TEST
 BEFORE INSERT ON TB_ANDY_TEST
  FOR EACH ROW
   BEGIN
     :NEW.DT := SELECT DT_SEQ.NEXTVAL FROM DUAL;    END; Make sure the creator of the trigger either owns the sequence DT_SEQ, or has SELECT (and a synonym) to it.

Normally, you need to do a SELECT to get a sequence value.

Dan Received on Wed Mar 11 1998 - 00:00:00 CET