试图使用||连接到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屋！
