Re: Oracle Triggers & Sequences

On Fri, 13 Feb 1998 18:26:10 -0000, Vinay Joshi <vjoshi_at_pinnacle.co.uk> wrote: Hi,

check what Vinaj said.

There's a sightly shorter version of your trigger:

 CREATE OR REPLACE TRIGGER myobj_ins_row  BEFORE INSERT ON <myobjects> or <myobj> FOR EACH ROW  BEGIN
     SELECT obj_id_seq.nextval INTO :new.id FROM dual;  END myobj_ins_row;

>Hi,
>Looking at your code below,
>the line
> BEFORE INSERT ON myobjects FOR EACH ROW
>mentions table name as ^^^^^^^^^^^^ MYOBJECTS where as in the line
>before that
> ie
> After creating a sequence (below) called obj_id_seq and a table
>called
> myobj which has a column call obj_id, I use the following code :
>
>you are mentioning the tablename as myobj. This could be the problem.
>If not do you get any error messages ?
>
>Oracle DBA/Support
>Vinay Joshi
>Pinnacle Insurance Plc
>Vjoshi_at_pinnacle.co.uk
>
>> -----Original Message-----
>> From: Benjamin Davies [SMTP:benpdavies_at_ibm.net]
>> Posted At: 13 February 1998 12:07
>> Posted To: server
>> Conversation: Exp using Direct problem
>> Subject: Oracle Triggers & Sequences
>>
>> I have recently installed Oracle 7.3.3 on an HP-UX Box to set up a
>> simple
>> database. I found some sample code for using a trigger to populate a
>> primary key from a sequence. The sample code will not work on my
>> system
>> and I've checked 12 of the leading Oracle texts (including Oracle's
>> own)
>> and find similar code in all.
>>
>> Does anybody have a piece of code that works for doing this? Triggers
>> and
>> sequences both function correctly on my system, its combining the two
>> that
>> screws things up.
>>
>> After creating a sequence (below) called obj_id_seq and a table called
>> myobj which has a column call obj_id, I use the following code :
>>
>> CREATE SEQUENCE obj_id_seq
>> START WITH 1
>> INCREMENT BY 1
>> NOMAXVALUE;
>>
>> CREATE OR REPLACE TRIGGER myobj_ins_row
>> BEFORE INSERT ON myobjects FOR EACH ROW
>> DECLARE
>> new_id NUMBER;
>> BEGIN
>> SELECT obj_id_seq.nextval INTO new_id FROM dual;
>> :new.id := new_id;
>> END myobj_ins_row;
>>
>> Any pointers would be immensly appreciated!
>>
>> Ben.
>>
>> --
>>
>> Ben Davies
>> Principal
>>
>> KEIR CONSULTANTS INC.
>> Tel: 1 (416) 234 2040 | 3331 Bloor St. West
>> Fax: 1 (416) 234 5953 | Etobicoke, Ontario
>> E-Mail: benpdavies_at_ibm.net | M8X 1E7 CANADA
>>
>> HICOM ENVIRONMENTAL SDN. BHD.
>> Tel: 6 (03) 202 8444 | Suite 2.3, Level 2,
>> Wisma
>> HICOM
>> Fax: 6 (03) 202 8401 | No. 2, Jalan U1/8
>> (Glenmarie)
>>
>> E-Mail: benpdavies_at_ibm.net | 40150 Shah Alam, Selangor,
>> Malaysia
>> << File: Card for Davies, Benjamin >>
>

--

Regards

Matthias Gresz    :-)

GreMa_at_T-online.de