SQL触发器在满足条件时停止更新 [英] SQL trigger to stop update when a condition is met

问题描述

我有3个表:Projects，Components和Suppliers.

我想做的是编写一个触发器，如果​​组件和项目与供应商所在的城市相同，则不允许修改city的值.

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.

到目前为止我尝试过的事情:

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

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

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:

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

要摆脱这些错误需要做什么?

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

推荐答案

存在一些语法错误.

1. DECLARE在BEGIN语句之前.
2. INTO在SELECT之后且在FROM之前.
3. 在raise_application_error(-20111,'Can't change the city for this supplier!');，您不能编写Can't，因为第一个单引号将以Can't的引号结束，从而导致字符串在此结束.因此，您应该删除它或执行以下操作:raise_application_error(-20111,'Can''t change the city for this supplier!');

1. DECLARE goes before the BEGIN statement.
2. INTO goes after SELECT and before FROM.
3. At 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. So you should remove it or do: 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;

希望这会有所帮助.

这篇关于SQL触发器在满足条件时停止更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！