试图使用||连接到RAISE()函数中导致语法错误 [英] trying to concat in the RAISE() function using || results in a syntax error
问题描述
我是尝试创建一个触发器来捕获到Viewings表中的插入内容,其中外键(viewings.location)与Places表(places.location)中的现有主键不对应。据我所知,该逻辑按预期工作。但是我的问题来自尝试将尝试的值连接到raise函数中的错误消息中。
I am trying to create a trigger that catches inserts into the Viewings table where the foreign key (viewings.location) does not correspond to an existing primary key in the Places table (places.location). The logic, from what I can tell, works as expected. However my issue comes from trying to concatenate the attempted value into the error-message in the raise function. Is this not allowed?
create trigger catchForeignKeyError BEFORE INSERT ON VIEWINGS
BEGIN
SELECT CASE
WHEN NEW.location NOT IN (SELECT PLACES.location FROM PLACES) THEN
RAISE(ABORT, 'Error: Insert into the VIEWINGS table references location '''||NEW.location||''' that is not found in the PLACES table.')
END;
END;
推荐答案
在SQLite语法中,RAISE的第二个参数()表达式不是字符串而是名称:
In the SQLite grammar, the second parameter of the RAISE() expression is not a string but a name:
RAISE(ABORT, some_error)
标识符可以用双引号引起来,并且由于历史原因,SQLite 接受可接受标识符的字符串(带单引号),但必须是单个字符串,而不是由其他值组成的字符串表达式:
Identifiers can be quoted with double quotes, and for historical reasons, SQLite accepts a string (with single quotes) where an identifier is expected, but then it must be a single string, not a string expression composed of other values:
RAISE(ABORT, "some error")
除了为此创建用户定义的函数外,没有机制可以将动态值添加到错误消息中。
There is no mechanism to get a dynamic value into the error message, except by creating a user-defined function for this.
这篇关于试图使用||连接到RAISE()函数中导致语法错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!