triggers using sequence to compile

From: Norman Dunbar <Norman.Dunbar_at_lfs.co.uk>
Date: Wed, 2 Apr 2003 13:17:58 +0100
Message-ID: <E2F6A70FE45242488C865C3BC1245DA70380DF76@lnewton.leeds.lfs.co.uk>

Afternoon,

Should the trigger be checking for zero ? I suspect it should be checking for NULL instead.

Try this code :

create or replace trigger com_group_inc
before insert on com_groups
for each row
begin

    if :new.group_id is null then

        select com_group_autoinc.nextval into :new.group_id;     end if;
end;

The above will only assign a group_id if one is not passed. If the user does pass one, then the trigger above will leave it alone. This may not be what you want. If you do not want the user to supply a group_id for new groups, then the following will fix that :

create or replace trigger com_group_inc
before insert on com_groups
for each row
begin

    select com_group_autoinc.nextval into :new.group_id; end;

As far as I can see, your original trigger leaves the group_id set to zero if it is passed in as zero, and only assigns a value from the sequence if it is NOT zero.

Regards,
Norman.

---

Norman Dunbar
Database/Unix administrator
Lynx Financial Systems Ltd.
mailto:Norman.Dunbar_at_LFS.co.uk

Tel: 0113 289 6265
Fax: 0113 289 3146
URL: http://www.Lynx-FS.com


-------------------------------------

-----Original Message-----
From: ypoizeau_at_ifrance.com (yoann) [mailto:ypoizeau_at_ifrance.com] Posted At: Wednesday, April 02, 2003 11:01 AM Posted To: server
Conversation: triggers using sequence to compile Subject: triggers using sequence to compile

i have modify a table and some of my triggers have becomed invalid so i have compiled THEM using the
DBMS_UTILITY.COMPILE_SCHEMA(schema_name); function but know i have a problem. when i use sequence in a trigger, the trigger doesn't work anymore
MY TRIGGER:
<SNIP> Received on Wed Apr 02 2003 - 06:17:58 CST