SQL触发器在满足条件时停止更新

本文介绍了SQL触发器在满足条件时停止更新的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有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?

推荐答案

存在一些语法错误.

DECLARE在BEGIN语句之前.

INTO在SELECT之后且在FROM之前.

在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!');

DECLARE goes before the BEGIN statement.

INTO goes after SELECT and before FROM.

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;

希望这会有所帮助.