Home » SQL & PL/SQL » SQL & PL/SQL » Trigger Problem (Oracle 10)
Trigger Problem [message #327200] Sat, 14 June 2008 17:28 Go to next message
New_Start
Messages: 8
Registered: June 2008
Junior Member
Item_of_Invoice
Invoice_nr---Item_Id---Quantity---ISBN

Book
ISBN--Title--In_Stock---price---Author_ID--Type_Id--Supp_Id

Invoice
Invoice_nr---Inv_date--Paym_date---Amount--Cust_Id

Now how do i construct a trigger which after inserting an invoice in table Item_of_Invoice, then adjusts the column In_Stock in table Book by reducing the amount of books and then updates the table Invoice with the same amount, assuming we don't have the table Order?


Here is what i've tried as a beginner

CREATE TRIGGER Test AFTER INSERT ON Item_of Invoice
FOR EACH ROW
Begin
UPDATE Book SET In_Stock = Book.in_stock-New.quantity
Where (Item_of_invoice.ISBN=New.ISBN);
END;
Re: Trigger Problem [message #327202 is a reply to message #327200] Sat, 14 June 2008 20:38 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow Posting Guidelines as stated in URL above

Please use the SEARCH function on this forum for the FAQs on "trigger".
Re: Trigger Problem [message #327204 is a reply to message #327200] Sat, 14 June 2008 22:23 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8621
Registered: November 2002
Location: California, USA
Senior Member
You have to put a colon in front of the new.

:new.column_name

[Updated on: Sat, 14 June 2008 22:24]

Report message to a moderator

Re: Trigger Problem [message #327208 is a reply to message #327200] Sun, 15 June 2008 00:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Of course this an excercise as it will not work in real world (unless you accept to have negative stock).

In real world, you should not use trigger for this but a procedure that explicitly expose what is done in the transaction and not do it as side effect.

Regards
Michel
Re: Trigger Problem [message #327216 is a reply to message #327204] Sun, 15 June 2008 06:07 Go to previous messageGo to next message
New_Start
Messages: 8
Registered: June 2008
Junior Member
I still get the error "trigger created with compilation ...." even when i put the colon, what can i do to correct this error
Re: Trigger Problem [message #327218 is a reply to message #327216] Sun, 15 June 2008 06:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You first have to put our code and compilation error otherwise we can't guess waht it is.
Use SQL*Plus and copy and paste your session.
Use "show errors" or query user_errors to get the errors.

Before please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.

Regards
Michel
Re: Trigger Problem [message #327224 is a reply to message #327218] Sun, 15 June 2008 07:33 Go to previous messageGo to next message
New_Start
Messages: 8
Registered: June 2008
Junior Member
Sorry Michel, but i don't seem to see this SQL *Plus "Show error" thing, but when i put my code which is:

CREATE TRIGGER Test
AFTER INSERT ON Item_Of_Invoice
FOR EACH ROW
BEGIN
UPDATE Book
SET In_Stock = Book.In_Stock - :New.Quantity
WHERE (Item_Of_Invoice.Isbn = New.Isbn);
END;
/

i get the error:
"Trigger created with compilation error"
which is in the language of the OS (not english).
I'm Sorry to all forum members for not understanding
Re: Trigger Problem [message #327235 is a reply to message #327224] Sun, 15 June 2008 09:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
but i don't seem to see this SQL *Plus "Show error" thin

Do you know SQL*Plus?
See SQL*Plus® User's Guide and Reference (forget all what is i SQL*Plus).

As Barbara said:
Quote:
You have to put a colon in front of the new.
:new.column_name

Reread you code.

Regards
Michel

[Updated on: Sun, 15 June 2008 11:15]

Report message to a moderator

Re: Trigger Problem [message #327242 is a reply to message #327235] Sun, 15 June 2008 10:46 Go to previous messageGo to next message
New_Start
Messages: 8
Registered: June 2008
Junior Member
Please if i knew what should be done i would have done it, i don't know thats why im asking.
Re: Trigger Problem [message #327243 is a reply to message #327242] Sun, 15 June 2008 10:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ok I point you to the line:
Quote:
WHERE (Item_Of_Invoice.Isbn = New.Isbn);

I repeat Barbara post:
Quote:
You have to put a colon in front of the new.
:new.column_name

Now, do you see what you have to do?

Regards
Michel
Re: Trigger Problem [message #327244 is a reply to message #327243] Sun, 15 June 2008 10:58 Go to previous messageGo to next message
New_Start
Messages: 8
Registered: June 2008
Junior Member
Thanks Michel, but I have done that but i still get the error.
Re: Trigger Problem [message #327245 is a reply to message #327244] Sun, 15 June 2008 11:05 Go to previous messageGo to next message
New_Start
Messages: 8
Registered: June 2008
Junior Member
Michel,This is how my tables look

Item_of_Invoice
Invoice_nr|Item_Id|Quantity|ISBN

Book
ISBN|Title|In_Stock|price|Author_ID|Type_Id|Supp_Id

So i just want that on insert on table Item_of_Invoice, the In_Stock on table books is reduces with the same amount.
Re: Trigger Problem [message #327246 is a reply to message #327244] Sun, 15 June 2008 11:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
New_Start wrote on Sun, 15 June 2008 17:58
Thanks Michel, but I have done that but i still get the error.

As you don't post as requested nobody can guess what is your error.

Regards
Michel

Re: Trigger Problem [message #327247 is a reply to message #327246] Sun, 15 June 2008 11:26 Go to previous messageGo to next message
New_Start
Messages: 8
Registered: June 2008
Junior Member
Please what is it i'm doing wrong? i've given all the information and the only error i get is "Created with compilation error", ok i'll just give up thanks for your help.
Re: Trigger Problem [message #327248 is a reply to message #327247] Sun, 15 June 2008 11:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Please what is it i'm doing wrong?

Michel Cadot wrote on Sun, 15 June 2008 13:28
Use SQL*Plus and copy and paste your session.
Use "show errors" or query user_errors to get the errors.

Until you use it nobody can help you, so DO IT!

Regards
Michel

Re: Trigger Problem [message #327251 is a reply to message #327248] Sun, 15 June 2008 13:01 Go to previous messageGo to next message
New_Start
Messages: 8
Registered: June 2008
Junior Member
SQL> CREATE TRIGGER Test AFTER INSERT ON Item_of_Invoice
2 FOR EACH ROW
3 Begin
4 UPDATE Book SET In_Stock = Book.in_stock-:New.quantity
5 Where (Item_of_invoice.ISBN=:New.ISBN);
6 END;
7 /

Varning: Trigger skapad med kompileringsfel.

SQL> show errors;
Fel för TRIGGER TEST:

LINE/COL ERROR
-------- --------------------------------------------------------------
2/1 PL/SQL: SQL Statement ignored
3/8 PL/SQL: ORA-00904: "ITEM_OF_INVOICE"."ISBN": ogiltig
identifierare
Re: Trigger Problem [message #327252 is a reply to message #327251] Sun, 15 June 2008 13:13 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
4 UPDATE Book SET In_Stock = Book.in_stock-:New.quantity
5 Where (Item_of_invoice.ISBN=:New.ISBN);

Get it?

Next time USE CODE TAGS.

Regards
Michel
Previous Topic: Fast refresh won't empty the mlog table.
Next Topic: getting ORA-01438 error (field presion) on update from fields of the same presision
Goto Forum:
  


Current Time: Sat Dec 03 01:12:05 CST 2016

Total time taken to generate the page: 0.24066 seconds