Can someone help me implement the following trigger in PL/SQL Oracle?

Question

So,

I have 4 tables:

Suppliers( id_sup, name, city)

Products (id_prod, name, city)

Companies (id_co, name, city)

Deliveries (id_sup, id_prod, id_co)

I need a trigger so that if I want to update the city of a Supplier, I am not allowed if that supplier has a delivery where the product it delivers and the company it delivers to have the same city as it.

This is what I've tried so far, but it's not working:

CREATE OR REPLACE TRIGGER secure_suppliers

BEFORE UPDATE ON Suppliers

BEGIN

IF UPDATING ('city') THEN

IF (suppliers.id_sup IN (SELECT id_sup FROM Deliveries) AND suppliers.city = (Select p.city From Products p INNER JOIN Deliveries d ON (p.id_prod = d.id_prod)) AND suppliers.city = (Select c.city From Companies c INNER JOIN Deliveries d ON (c.id_co = d.id_co))) THEN

RAISE_APPLICATION_ERROR(-20500, 'Can't update city!');

End if;

End;

Answer 1

You can use one query to find if product, supplier and company are in one city with new value of the supplier then proceed with error handling in the trigger as following:

CREATE OR REPLACE TRIGGER secure_suppliers
    For each row -- row level trigger
    BEFORE UPDATE OF city ON Suppliers -- trigger will execute on update of column city only

DECLARE
cnt NUMBER := 0;

BEGIN    
select count(1)
Into cnt
From deliveries d
Join companies c on d.id_co = c.id_co
Join products p on d.id_prod = p.id_prod
Where p.city = c.city
And p.city = :new.city;

If cnt > 0 then
RAISE_APPLICATION_ERROR(-20500, 'Can''t update city!'); 
End if;

End;
/

Cheers!!