PL/SQL编译语法错误,出现在触发器中。

huangapple go评论64阅读模式
英文:

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.

huangapple
  • 本文由 发表于 2023年2月24日 04:10:11
  • 转载请务必保留本文链接:https://go.coder-hub.com/75549838.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定