Home » SQL & PL/SQL » SQL & PL/SQL » Trigger help
Trigger help [message #275954] Tue, 23 October 2007 08:43 Go to next message
quakeworld2007@gmail.com
Messages: 9
Registered: October 2007
Junior Member
The two tables I am using...
CREATE TABLE "PRODUCT" 
   (	"PRODUCT_ID" NUMBER(5,0), 
	"PRODUCT_NAME" VARCHAR2(30 BYTE), 
	"QUANTITY_IN_STOCK" NUMBER(3,0), 
	"REORDER_POINT" NUMBER(2,0), 
	"PRICE" NUMBER(5,2), 
	"SUPPLIER_ID" VARCHAR2(4 BYTE), 
	"REORDER_QTY" NUMBER(2,0) }


  CREATE TABLE "PURCHASE_ORDER" 
   (	"PO_NO" VARCHAR2(4 BYTE), 
	"PO_DATE" DATE, 
	"PRODUCT_ID" NUMBER(5,0), 
	"QUANTITY" NUMBER(3,0), 
	"SUPPLIER_ID" VARCHAR2(4 BYTE) }

The trigger checks to see if quantity_in_stock < reorder_qty if it is a new row added to purchsae_order that includes existing supplier_no, and the quantity = to the reorder_qty.

The trigger...
create or replace
TRIGGER ex3c_product_reorder_au 
AFTER UPDATE of quantity_in_stock ON product
referencing new as new old as old

FOR EACH ROW

BEGIN
  IF :new.quantity_in_stock < :old.reorder_qty THEN
    INSERT into purchase_order (po_no, po_date, product_id, quantity, supplier_id )
      VALUES(order_sequence.nextval, sysdate, :new.product_id, :old.reorder_qty, :new.supplier_id);
  END IF;
END;

Errors I get...

Error(10,5): PL/SQL: SQL Statement ignored
Error(11,14): PL/SQL: ORA-02289: sequence does not exist

[Updated on: Tue, 23 October 2007 08:48]

Report message to a moderator

Re: Trigger help [message #275960 is a reply to message #275954] Tue, 23 October 2007 08:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ It seems that either order_sequence doesn't exist or you don't have access to it

2/ This will never work in multi-user environment unless you lock the tables

Regards
Michel
Re: Trigger help [message #275968 is a reply to message #275960] Tue, 23 October 2007 09:18 Go to previous messageGo to next message
quakeworld2007@gmail.com
Messages: 9
Registered: October 2007
Junior Member
There is a sequence that starts at PO11 and goes to PO16. IS the sequence suppose to have a name or do I name it because I just named it order_sequence.
Re: Trigger help [message #275969 is a reply to message #275954] Tue, 23 October 2007 09:20 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
There needs to be a sequence in your database called order_sequence.

The user running the sql needs to be granted access to it.
Re: Trigger help [message #275970 is a reply to message #275969] Tue, 23 October 2007 09:21 Go to previous messageGo to next message
quakeworld2007@gmail.com
Messages: 9
Registered: October 2007
Junior Member
How do I create the sequence then, I can not create it in the trigger correct?
Re: Trigger help [message #275973 is a reply to message #275968] Tue, 23 October 2007 09:35 Go to previous messageGo to next message
iamdurai
Messages: 96
Registered: April 2007
Location: Chennai
Member

Hi,

   Is it possible to start sequence value like "P011".

Thanx & Reds
Thangam

Re: Trigger help [message #275974 is a reply to message #275973] Tue, 23 October 2007 09:40 Go to previous messageGo to next message
quakeworld2007@gmail.com
Messages: 9
Registered: October 2007
Junior Member
I got it Smile
Re: Trigger help [message #275983 is a reply to message #275974] Tue, 23 October 2007 09:59 Go to previous messageGo to next message
iamdurai
Messages: 96
Registered: April 2007
Location: Chennai
Member

Laughing

 Hi
 what you got it? 
 Please post the answer to the third or else atleast inform to the third 
you are not expecting to any more clarifications here.

Thanx & Reds 
Thangam.

[Updated on: Tue, 23 October 2007 11:02] by Moderator

Report message to a moderator

Re: Trigger help [message #275988 is a reply to message #275983] Tue, 23 October 2007 10:15 Go to previous messageGo to next message
quakeworld2007@gmail.com
Messages: 9
Registered: October 2007
Junior Member
I had the sequence misspelled it was orders_sequence instead of order_sequence.
Re: Trigger help [message #275990 is a reply to message #275988] Tue, 23 October 2007 10:19 Go to previous messageGo to next message
iamdurai
Messages: 96
Registered: April 2007
Location: Chennai
Member

Quote:


There is a sequence that starts at PO11 and goes to PO16. IS the sequence suppose to have a name or do I name it because I just named it order_sequence.



Please tell us how you have created the sequence like start value " P011"

Thanx & Reds
Thangam.
Re: Trigger help [message #275995 is a reply to message #275990] Tue, 23 October 2007 10:33 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
There is no way to create a true alpha-numeric sequence on an Oracle Database (as far as I know).

You could use a function to get the next sequence value and prepend your appropriate prefix on the database column and store the entire value.

You could keep both the prefix and the sequence in the database and concatenate them whenever a display or report is needed.

If your prefix is always the same you could simply affix it to the beginning without storing it in the database.

Hope this helps.

Re: Trigger help [message #275997 is a reply to message #275995] Tue, 23 October 2007 10:43 Go to previous message
iamdurai
Messages: 96
Registered: April 2007
Location: Chennai
Member

Hi Marcl,

Thanks for the clarification.

why am i asking again and again this question.

Quote:



There is a sequence that starts at PO11 and goes to PO16. IS the sequence suppose to have a name or do I name it because I just named it order_sequence.




"quakeworld2007" possted like that thats why i got little bit confusion.

Thanx & Reds
Thangam.
Previous Topic: From the stored procedure itself how to find out its name ?
Next Topic: cannot perform a dml operation inside a query
Goto Forum:
  


Current Time: Tue Dec 06 08:11:56 CST 2016

Total time taken to generate the page: 0.08491 seconds