OraFAQ Forum: SQL & PL/SQL » Trigger on table to update other table

Home » SQL & PL/SQL » SQL & PL/SQL » Trigger on table to update other table (APEX, Oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Trigger on table to update other table [message #638980]

Sun, 28 June 2015 02:48

luijk008
Messages: 1
Registered: June 2015
Location: Netherlands

Junior Member

I'm a student and I have, probably, a very easy to solve PL/SQL problem. I have two tables, one is called "ITEMS" and the other one is called "VERHUUR". The ITEMS tables consists the amount of items that can be rented by people. In the table VERHUUR I store information when people rent an item. I want a trigger on the start date field in the table "VERHUUR" and when that field is filled the amount of items should decrease with 1. I keep getting this error: PL/SQL: ORA-00904: "items.itnr": invalid identifier.
Can somebody please have a look at this? Thanks.

create or replace trigger "VERHUUR_UPDATE_MIN1"
AFTER
insert or update on "VERHUUR"
for each row
begin
update items
set "voorraad" = "voorraad" -1
where "verhuur.itnr" = "items.itnr";
end;

Report message to a moderator

Re: Trigger on table to update other table [message #638981 is a reply to message #638980]

Sun, 28 June 2015 03:30

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Hi,

Welcome to the forum!

Please read and follow the OraFAQ Forum Guide and How to use [code] tags

Report message to a moderator

Re: Trigger on table to update other table [message #638982 is a reply to message #638980]

Sun, 28 June 2015 03:40

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

luijk008 wrote on Sun, 28 June 2015 13:18

update items
set "voorraad" = "voorraad" -1
where "verhuur.itnr" = "items.itnr";

Think about the basic update statement syntax and you will know where you are doing wrong. Also, you need to reference the itnr value in the predicate i.e. you want those rows to be updated in items which has the itnr value that of updated rows in vehuur.

Report message to a moderator

Re: Trigger on table to update other table [message #638985 is a reply to message #638980]

Sun, 28 June 2015 04:16

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

insert or update on "VERHUUR"

Quote:

update items
where "verhuur.itnr" = "items.itnr";

"VERHUUR" != "verhuur"
items != "items"

Are you sure you have some table or column names are in lower case?

[Updated on: Sun, 28 June 2015 04:18]

Report message to a moderator

Re: Trigger on table to update other table [message #639018 is a reply to message #638985]

Mon, 29 June 2015 14:03

Bill B
Messages: 1971
Registered: December 2004

Senior Member

surrounding a column name with double quotes means use the EXACT cast of the string. Without the double quotes it will always loot for the column or table in uppercase

[Updated on: Mon, 29 June 2015 14:04]

Report message to a moderator

Previous Topic:	Execute a sql script depending on number of records in a table
Next Topic:	is there any way to convert number into integer?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 02:50:40 CDT 2024