sql - 有人可以帮助我在PL / SQL Oracle中实现以下触发器吗？(Can someone help me implement the following trigger in PL/SQL Oracle?)

Question

So,

(所以，)

I have 4 tables:

(我有4张桌子：)

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;

  ask by Randiir translate from so

Answer 1

等待大神答复