CODEWITHSUNDEEP
sqloracle-databaseplsql
user2501165
user2501165

Reputation: 191

Updating Trigger Table Values Oracle SQL

I am not able to update a table after the following trigger added. Here is what I have so far:

CREATE TABLE ITEM(
item_id  DECIMAL(10) NOT NULL,
description  VARCHAR(30),
price  DECIMAL(10),
PRIMARY KEY (item_id));

CREATE TABLE Item_price_history (
history_id  DECIMAL(10) NOT NULL,
item_id  DECIMAL(10) NOT NULL,
line_price DECIMAL(10,2),
new_line_price DECIMAL(10,2),
modified DATE,
PRIMARY KEY (HISTORY_ID),
FOREIGN KEY (ITEM_ID) REFERENCES item);

CREATE OR REPLACE TRIGGER Item_price_history
AFTER UPDATE OR INSERT ON ITEM 
FOR EACH ROW  
BEGIN
   INSERT INTO item_price_history(item_id, line_price, 
    new_line_price,modified) 
   select item_id,:OLD.price,:New.price, SYSTIMESTAMP from item;
END;

Now if I use the following Update commands, I get this error:

UPDATE item_price_history
SET line_price = 4
WHERE ITEM_ID=ITEM.ITEM_ID and ITEM.DESCRIPTION='Spoon';

*Error report - SQL Error: ORA-00904: "ITEM"."DESCRIPTION": invalid identifier 00904. 00000 - "%s: invalid identifier" *Cause:
Action:

Any help or suggestions or advice?

Upvotes: 0

Views: 131

Answers (3)

praveen muppala
praveen muppala

Reputation: 177

You can try the below Merge as well. This will not update anything as there are no records in item table with Spoon Description and item_id. Not sure what you wanted to achieve. I see you created a trigger. The trigger fires only when there is an insert/update on item not on item_price_history.

merge INTO item_price_history tgt USING
(SELECT item_id, description FROM item
) src ON (tgt.item_id = src.item_id AND src.description = 'Spoon')
WHEN matched THEN
  UPDATE SET line_price = 4;

--Updates zero records

Upvotes: 1

MG_Bautista
MG_Bautista

Reputation: 2653

Try this...

update ITEM_PRICE_HISTORY SET line_price = 4 WHERE item_id IN (SELECT i.item_id FROM ITEM WHERE i.description = 'Spoon');

Upvotes: 0

Eray Balkanli
Eray Balkanli

Reputation: 7960

Could you please try this:

UPDATE item_price_history 
SET line_price = 4
WHERE item_id IN (SELECT item_id
                  FROM ITEM
                  WHERE description = 'Spoon');

Upvotes: 1

Related Questions