oracle - SQL触发器在满足条件时停止更新(SQL trigger to stop update when a condition is met)

Question

I have 3 tables: Projects , Components , and Suppliers .

(我有3个表格： Projects ， Components和Suppliers 。)

What I am trying to do is writing a trigger that doesn't allow the value of city to be modified if the component and the project have the same city as the supplier.

(我要做的是编写一个触发器，如果??组件和项目与供应商位于同一城市，则不允许修改city的值。)

What I have tried so far:

(到目前为止我尝试过的是：)

create or replace TRIGGER Supplier_control
BEFORE UPDATE of city
ON Suppliers
BEGIN
    DECLARE v_counter NUMBER := 0;
    SELECT COUNT(*) FROM (SELECT * FROM Suppliers s JOIN Projects p ON (s.city=p.city) JOIN Components c ON (c.city=s.city)) INTO v_counter;
    IF (v_counter != 0)
    THEN
        raise_application_error(-20111,'Can't change the city for this supplier!');
    END IF;
END;

After trying to run this, I am getting the following error:

(尝试运行此命令后，出现以下错误：)

Error at line 3: PLS-00103: Encountered the symbol "JOIN" when expecting one of the following:

   ) , with group having intersect minus order start union where
   connect

Please note that the line number refers to the number of the line after BEGIN!

(请注意，行号是指BEGIN之后的行号！)

I have also tried writing the declare part before BEGIN, I am getting the following error:

(我也尝试过在BEGIN之前编写声明部分，但出现以下错误：)

Error at line 3: PL/SQL: SQL Statement ignored

What needs to be done in order to get rid of these errors?

(为了摆脱这些错误需要做什么？)

  ask by MCM translate from so

Answer 1

There are some syntax errors.

(有一些语法错误。)

1. DECLARE is before the BEGIN statement.

   (DECLARE在BEGIN语句之前。)

2. INTO is after SELECT and before FROM .

   (INTO在SELECT之后，在FROM之前。)

3. At raise_application_error(-20111,'Can't change the city for this supplier!');

   (在raise_application_error(-20111,'Can't change the city for this supplier!');)

   you cannot write Can't because the first single quote will end at the quote of Can't causing the string to end there.

   (您不能写Can't因为第一个单引号将以“ Can't导致字符串在此结束”的引号结束。)

   So you should remove it or do: raise_application_error(-20111,'Can''t change the city for this supplier!');

   (因此，您应该删除它或执行以下操作： raise_application_error(-20111,'Can''t change the city for this supplier!');)

With all that being said, the full code should look like:

(话虽如此，完整的代码应如下所示：)

CREATE OR REPLACE TRIGGER Supplier_control
BEFORE UPDATE of city
ON Suppliers
DECLARE v_counter NUMBER := 0;
BEGIN

    SELECT COUNT(*) 
    INTO v_counter
    FROM (SELECT * FROM Suppliers s JOIN Projects p ON (s.city=p.city) JOIN Components c ON (c.city=s.city));

    IF (v_counter != 0) THEN
        raise_application_error(-20111,'Can''t change the city for this supplier!');
    END IF;

END;

Hope this helps.

(希望这可以帮助。)