PLSQL - Triggers: Cannot modify a column which maps to a non key-preserved table

Answer 1

You're trying to update a join view in your trigger, which has several constraints on when this can be done; see the Oracle documentation for more details.

This UPDATE should do what you're trying to achieve:

UPDATE inventory i 
 SET i.quantity = :new.quantity 
 WHERE i.inventory_id = 
  (SELECT c.inventory_id 
    FROM concessions c 
     WHERE c.concession_id = :new.concession_id)

Answer 2

First of all if you use a "For Each Row" trigger then you MUST NOT operate on the whole table, just one row, so

select i.quantity 
from inventory i, concession c
where i.inventory_id = c.inventory_id and c.concession_id = cs.concession_id

should be changed to

select i.quantity 
from inventory i, concessions_sold cs, concession c
where i.inventory_id = c.inventory_id and c.concession_id = :new.concession_id

Second, the update should be something like:

update inventory
set quantity = :new.quantity
where inventory_id = ( 
  select inventory_id from concession c where concession_id = :new.concession_id 
) ;

So the trigger should look like this:

create or replace 
trigger LabEx5_1 after insert on Concessions_sold
for each row
begin
  if inserting then
    update inventory
      set quantity = :new.quantity
    where inventory_id = ( 
      select inventory_id from concession c 
      where concession_id = :new.concession_id 
    ) ;
  end if;
end LabEx5_1;

Answer 3

Couple of things:

1. the "after insert" and "If (inserting)" are redundent. Remove the "If (inserting)" it's unnecessary since your trigger is limited to AFTER INSERT. Just adds more code to read.

2. It seems you are trying lower your inventory when something is sold. I don't see that you actually do that.

3. This is embeded query doesn't have a key associated with it. (This is were your error message comes from).

select i.quantity 
from inventory i, concessions_sold cs, concession c
where i.inventory_id = c.inventory_id and c.concession_id = cs.concession_id

1. You don't have a where clause and your inline-select doesn't limit the rows coming back from the inventory table. If you do get this to work, you are going to update every row in your Inventory table.

To make your actual update clause work.

  UPDATE (
    SELECT **i.inventory_id**, i.quantity   
    FROM Inventory i
      , Concessions_sold cs
      , Concessions c   
    WHERE i.inventory_id = c.inventory_id   
    AND c.concession_id = **:NEW.concession_id** 
  )
  SET quantity = :new.quantity

Now it works there are a few problems: 1. Linking table is unnecessary. 2. The inline SQL is making this UPDATE statement more difficult to read and hence more difficult to modify in the future.

I would be much more explicit:

UPDATE Inventory
SET quatity = quanaity - :new.quanity
WHERE inventory_id IN (
  SELECT inventory_id
  FROM Conessions c
  JOIN c.concession_id = :NEW.concession_id
  )

Obviously there's no checks to see that inventory quantity actually exists, something else you might want to take into consideration.

So your new trigger would look like this.

CREATE or REPLACE TRIGGER LabEx5_1 AFTER INSERT ON Concessions_sold
FOR EACH ROW
BEGIN
  UPDATE Inventory
  SET quatity = quanaity - :NEW.quanity
  WHERE inventory_id IN (
    SELECT inventory_id
    FROM Conessions c
    JOIN c.concession_id = :NEW.concession_id
    );
END LabEx5_1;