On Tue, 24 Mar 1998 12:42:16 -0000, "Mark Powell" <mark_at_u-net.net> wrote:
Hi,
the shortcut:
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
Select
USER.S_AG_NUM into :new.AG_NUM
From
DUAL;
END;
>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
Received on Wed Mar 25 1998 - 00:00:00 CST