Re: Using Triggers and Sequences

You must have been reading the Oracle documentation. I have seen references in the manuals to direct assignment of sequences in this manner, but have never been able to get the code to compile cleanly.

I have always had to reference the sequence in another dml statement as the other posts responding to this suggest.

Does anyone know why we cannot do a direct assignment as this example is attempting?

It seems inefficient to reference another table to retrieve the value of a sequence in this situation, although I suspect that dual is probably a permanent member of the disk buffer in most instances.

Mark Powell wrote in message <6f89jt$t9e$1_at_news.u-net.com>...
>Hi there,
>
>Having a few problems when trying to create a trigger.
>
>I'm creating the trigger to try and make an autoincrement type field, it
>expects a record to be insert or updated on the table "E_SUB" and is
>supposed to alter the field "AG_NUM" if the value is "0".
>
>The sequence seems to be okay...
>
>CREATE SEQUENCE USER.S_AG_NUM INCREMENT BY 1 MAXVALUE 999999 MINVALUE 90000
>NOCYCLE CACHE 20 ORDER;
>
>The trigger....
>
>CREATE OR REPLACE TRIGGER USER.SUBS_TRIG
>BEFORE INSERT OR UPDATE OF AG_NUM ON USER.E_SUBS
>FOR EACH ROW
>WHEN (new.AG_NUM = 0 )
>BEGIN
> :new.AG_NUM := USER.S_AG_NUM.NEXTVAL;
>END;
>
>....complains with an error "MGR-00073: Warning: TRIGGER USER.SUBS_TRIG
>created with compilation errors." when trying to create it. I believe this
>is to do with my poor PL/SQL as if I change the line ":new.AG_NUM :=
>USER.S_AG_NUM.NEXTVAL;" to "new:AG_NUM := 1;" it works... surely I must be
>able to reference a sequence from within the PL/SQL.
>
>Any help would be gratefully appreciated (as I'm pulling my hair out!)
>
>Thanks.
>
>Mark Powell
>
>Using Oracle8 (Solaris 2.6) & SQL Worksheet (WinNT)
>
>
>
Received on Sat Mar 28 1998 - 00:00:00 CST