英文:
PL/SQL compilation syntax error for trigger
问题
我一直在尝试找到解决我的SQL查询错误的方法,但似乎找不到错误的原因/不理解错误的原因。
我试图创建一个触发器,如果表的主要值之一得到更新(性别和出生年份),则更新表的主键。
以下是我的代码:
CREATE OR REPLACE TRIGGER ChevauxUpdateId
BEFORE UPDATE OF anneenais, sexe ON chevaux
FOR EACH ROW
BEGIN
:NEW.idcheval := TO_CHAR(:NEW.anneenais | :NEW.sexe | SUBSTR(:OLD.idcheval, 6, 5));
UPDATE proprietede
SET idcheval = :NEW.idcheval
WHERE idcheval = :OLD.idcheval;
UPDATE resultats
SET idcheval = :NEW.idcheval
WHERE idcheval = :OLD.idcheval;
END;
它给我报错:
2/86 PLS-00103: 在期望以下符号之一时遇到符号“)”:. ( * % & | - + / at mod remainder rem => .. <an exponent (**)> || multiset
11/4 PLS-00103: 遇到符号“end-of-file”时,期望以下符号之一:end not pragma final instantiable persistable order overriding static member constructor map
它在这一行给我报错:
:NEW.idcheval := TO_CHAR(:NEW.anneenais | :NEW.sexe | SUBSTR(:OLD.idcheval, 6, 5));
英文:
I have been trying to find the solution to my error in my sql query for a while and can't seem to find the cause/don't understand the error.
I'm trying to create a trigger that updates the primary key of a table if one of the main values gets updates (sexe and anneenais(anneenais is year of birth in french))
Here is my code
CREATE OR REPLACE TRIGGER ChevauxUpdateId
BEFORE UPDATE OF anneenais, sexe ON chevaux
FOR EACH ROW
BEGIN
:NEW.idcheval := TO_CHAR(:NEW.anneenais | :NEW.sexe | SUBSTR(:OLD.idcheval, 6, 5));
UPDATE proprietede
SET idcheval = :NEW.idcheval
WHERE idcheval = :OLD.idcheval;
UPDATE resultats
SET idcheval = :NEW.idcheval
WHERE idcheval = :OLD.idcheval;
END;
It gives me the error:
> 2/86 PLS-00103: Encountered the symbol ")" when expecting one of
> the following: . ( * % & | - + / at mod remainder rem => .. <an
> exponent (**)> || multiset
>
> 11/4 PLS-00103: Encountered the symbol "end-of-file" when
> expecting one of the following: end not pragma final instantiable
> persistable order overriding static member constructor map
it gives me the error at this line:
:NEW.idcheval := TO_CHAR(:NEW.anneenais | :NEW.sexe | SUBSTR(:OLD.idcheval, 6, 5));
答案1
得分: 3
这是因为连接运算符是两个连续的竖线符号 ||
,而不是一个竖线 |
。
:NEW.idcheval := TO_CHAR(:NEW.anneenais || :NEW.sexe || SUBSTR(:OLD.idcheval, 6, 5));
你本可以使用 concat
函数,但如果要连接超过两个字符串,会变得复杂,因为 concat
只接受两个参数。然后你必须嵌套它们,就像我说的,会变得复杂。
英文:
That's because concatenation operator is two consecutive pipe signs ||
, not just one |
.
:NEW.idcheval := TO_CHAR(:NEW.anneenais || :NEW.sexe || SUBSTR(:OLD.idcheval, 6, 5));
You could have used concat
function, but it gets ugly if there are more than two strings you're concatenating because it (concat
) accepts only two parameters. Then you have to nest them and - as I said - it gets ugly.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论