Using Triggers and Sequences

From: Mark Powell <mark_at_u-net.net>
Date: 1998/03/24
Message-ID: <6f89jt$t9e$1@news.u-net.com>#1/1

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 Tue Mar 24 1998 - 00:00:00 CST