Re:Oracle Triggers & Sequences

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 >>
Received on Fri Feb 13 1998 - 00:00:00 CST