Re: Using Triggers and Sequences

Hi ,

you've got to do something like:

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
BEGIN
         Select DECODE(:new.AG_NUM,0,0, USER.S_AG_NUM.NEXTVAL) into :new.AG_NUM from dual; END; On Sat, 28 Mar 1998 06:25:56 -0500, "Thierry Lach" <tlach_at_webvisn.com> wrote:

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

--

Regards

Matthias Gresz    :-)

GreMa_at_T-online.de