Mysterious Trigger错误DB2 UDB v8.1.5 [英] Mysterious Trigger error DB2 UDB v8.1.5
问题描述
我正在尝试创建一个重复的预防触发器：
CREATE TRIGGER is3.ard_u_unique
更新前act_recov_date ON is3.flushes
参考新的N /
每个行模式DB2SQL
WHEN（N.act_recov_date IS NOT NULL）
BEGIN ATOMIC
选择计数（*）到v_n
来自is3.flushes
其中bhid = N.bhid
和act_recov_date = N.act_recov_date;
如果v_n> 0则
SIGNAL SQLSTATE？75000？
SET MESSAGE_TEXT =？重复实际恢复日期？;
结束if;
END
当我运行它时，我收到错误消息：
DB2 SQL错误：SQLCODE：-7，SQLSTATE：42601，SQLERRMC：?; SIGNAL SQLSTATE
消息：字符 "在SIGNAL SQLSTATE之后无效。
行：12
据我所知，SIGNAL语句形成良好，所以错误
在其他地方，但我没有看到错误。
任何帮助都将不胜感激。
PS是否有任何语法''更新或插入之前''？我没有找到它，
但这并不意味着很多兆字节（千兆字节？）的
文档，
Bob Stearns写道：我正在尝试创建一个重复的预防触发器：
CREATE TRIGGER is3.ard_u_unique
更新act_recov_date ON is3.flushes
为每个行模式DB2SQL引用新的
WHEN（N.act_recov_date不为空）
BEGIN ATOMIC 从is3.flushes中选择计数（*）到v_n
其中bhid = N.bhid
和act_recov_date = N.act_recov_date;
如果v_n> 0则
SIGNAL SQLSTATE？75000？
SET MESSAGE_TEXT =？重复实际恢复日期？;
结束if;
结束
当我运行它时，我收到错误消息：
DB2 SQL错误：SQLCODE：-7，SQLSTATE：42601，SQLERRMC：?; SIGNAL SQLSTA TE
消息：角色 "在SIGNAL SQLSTATE之后无效。
行：12
这是用于LUW的DB2 UDB吗？我对SQLCODE -7感到惊讶。
期望-104。
无论如何在LUWSELECT INTO中触发器不支持。
无论哪种方式进行计数（*）只是为了测试存在是一种昂贵的模式
的操作（最好不要运行RS或RR隔离......！）
试试这个：
CREATE TRIGGER is3.ard_u_unique
更新之前act_recov_date ON is3.flushes
参考新的N /
每个行模式DB2SQL
什么时候存在（从is3.flushes中选择
WHERE bhid = N .bhid
和act_recov_date = N.act_recov_date
和N.act_recov_date IS NOT NULL）
SIGNAL SQLSTATE？75000？
SET MESSAGE_TEXT =？重复实际恢复日期？;
你会对野兽的敏捷感到惊喜，
特别是对于DPF： - ）
PS更新或插入之前是否有任何语法？我没有找到它，
但这并不意味着很多兆字节（千兆字节？）的文档，
令人震惊的是，引用相当有条理; - ）< br>- 创建触发器 - 触发器名称 - + - 之前没有CASCADE - + ---------->
+ -AFTER ------------- +
''-INSTEAD OF ------ - ''
- + - INSERT -------------------------- + - ON - + -table-name - + ------>
+ -DELETE --------------------- ----- +''-view-name--''
'' - UPDATE - + ------------------- - + - ''
| .-，-----------。 |
| V | |
''-OF ---- column-name - + - ''
没有环回覆盖INSERT，DELETE，UPDATE。所以答案是
没有。没有必要去寻找其他文档。
说实话。您的请求一次性弹出，但通常
的反应是：哦，好吧......。您可以使用触发器中的CALL来封装和分享更复杂的逻辑。
干杯
Serge
-
Serge Rielau
DB2 SQL编译器开发
IBM多伦多实验室
< blockquote> Serge Rielau写道：
Bob Stearns写道：
我正在尝试创建一个重复的预防触发器：
CREATE TRIGGER is3.ard_u_unique
在更新act_recov_date ON之前is3.flushes
为每个行模式DB2SQL引用新的
WHEN（N.act_recov_date IS NOT NULL）
BEGIN ATOMIC
从is3.flushes选择计数（*）到v_n
其中bhid = N.bhid
和act_recov_date = N.act_recov_date;
如果v_n> 0然后
SIGNAL SQLSTATE？75000？
SET MESSAGE_TEXT =？重复实际恢复日期？;
结束if;
结束
当我运行它时，我收到错误消息：
DB2 SQL错误：SQLCODE：-7，SQLSTATE：42601，SQLERRMC：?; SIGNAL SQLSTATE
消息：角色 "在SIGNAL SQLSTATE之后无效。
行：12
这是DB2 UDB for LUW吗？我对SQLCODE -7感到惊讶。
期待-104。
无论如何LUWSELECT INTO触发器不支持。
无论哪种方式进行计数（*）只是为了测试存在是一种昂贵的操作模式（最好不要运行RS或RR隔离......！）<试试这个：
CREATE TRIGGER is3.ard_u_unique
在更新act_recov_date之前是is3.flushes
为每个行模式DB2SQL引用新的
什么时候存在（SELECT FROM is3.flushes
WHERE bhid = N.bhid
和act_recov_date = N.act_recov_date
和N.act_recov_date IS NOT NULL）
SIGNAL SQLSTATE？75000？
SET MESSAGE_TEXT =？复制实际恢复日期？;
你会对野兽的快速感到惊喜，特别是在DPF上:-)
PS是否有''更新或插入前'的语法？我没有找到它，但这并不意味着在兆字节（千兆字节？）的文档中，
令人震惊的是引用相当有条理;-)
>> -CREATE TRIGGER - 触发器名称 - + - 没有CASCADE之前 - + - -------->
+ -AFTER ------------- +
''-INSTEAD OF-- ------''
> - + - INSERT ----------------------- --- + - ON - + - 表名 - + ------>
+ -DELETE --------------- ----------- +''-view-name--''
'' - UPDATE - + ----------------- ---- + - ''
| .-，-----------。 |
| V | |
''-OF ----列名 - + - ''
没有环回覆盖INSERT，DELETE，UPDATE。所以答案是否定的。没有必要去寻找其他文档。
说实话。您的请求一次性弹出，但通常
反应是：哦，好吧......。您可以使用触发器中的CALL来包含和分享更复杂的逻辑。
Serge
Serge
谢谢，我不记得EXISTS谓词。我的例子中出现了一个NULL用法/
解释问题。鉴于
和act_recov_date = N.act_recov_date
短语，不是这句话
和N.act_recov_date IS NOT NULL
多余？我认为NULL = NULL是未知的，因此不是真的。
这是SQL Reference Volume 2手册中的定义，我相信
相信，我同意你的看法。没有后来的定义是很难确定的。
。 IBM何时计划（根据您的知识）
来创建一套完整的可下载手册？
（假设，我知道，我知道）版本9或更早一点出现？
它会减少（不消除）搜索多个需求来源，
特别是那些必须在线搜索的问题。
Serge Rielau写道：
Bob Stearns写道：
我正在尝试创建一个重复的预防触发器：
CREATE TRIGGER is3.ard_u_unique
在更新之前act_recov_date ON is3.flushes
为每个行模式DB2SQL引用新的
WHEN（N.act_recov_date不为空）
BEGIN ATOMIC
select count（*）从is3.flushes进入v_n
其中bhid = N.bhid
和act_recov_date = N.act_recov_date;
如果v_n> 0那么
SIGNAL SQLSTATE？75000？
SET MESSAGE_TEXT =？重复实际恢复da te ?;
结束if;
结束
当我运行它时，我收到错误消息：
DB2 SQL错误：SQLCODE：-7， SQLSTATE：42601，SQLERRMC：?; SIGNAL SQLSTATE
消息：字符 "在SIGNAL SQLSTATE之后无效。
行：12
这是DB2 UDB for LUW吗？我对SQLCODE -7感到惊讶。
期待-104。
无论如何LUWSELECT INTO触发器不支持。
无论哪种方式进行计数（*）只是为了测试存在是一种昂贵的操作模式（最好不要运行RS或RR隔离......！）<试试这个：
CREATE TRIGGER is3.ard_u_unique
在更新act_recov_date之前是is3.flushes
为每个行模式DB2SQL引用新的
什么时候存在（SELECT FROM is3.flushes
WHERE bhid = N.bhid
和act_recov_date = N.act_recov_date
和N.act_recov_date IS NOT NULL）
SIGNAL SQLSTATE？75000？
SET MESSAGE_TEXT =？复制实际恢复日期？;
你会对野兽的快速感到惊喜，特别是在DPF上:-)
PS是否有''更新或插入前'的语法？我没有找到它，但这并不意味着在兆字节（千兆字节？）的文档中，
令人震惊的是引用相当有条理;-)
>> -CREATE TRIGGER - 触发器名称 - + - 没有CASCADE之前 - + - -------->
+ -AFTER ------------- +
''-INSTEAD OF-- ------''
> - + - INSERT ----------------------- --- + - ON - + - 表名 - + ------>
+ -DELETE --------------- ----------- +''-view-name--''
'' - UPDATE - + ----------------- ---- + - ''
| .-，-----------。 |
| V | |
''-OF ----列名 - + - ''
没有环回覆盖INSERT，DELETE，UPDATE。所以答案是否定的。没有必要去寻找其他文档。
说实话。您的请求一次性弹出，但通常
反应是：哦，好吧......。你可以在Trigger中使用CALL来包含和分享更复杂的逻辑。
Serge
Serge
我们俩错过了错误的实际原因。我从手册中复制了一些代码
，并且撇号不是撇号（ASCII 39）但是
而不是高位字符（ASCII 146）这几乎是一个撇号在
外观。
现在谈到下一个问题。在用几乎
撇号纠正问题之后，我尝试构建触发器，并给出了
以下消息：
DB2 SQL错误：SQLCODE：-104，SQLSTATE：42601，SQLERRMC：WHEN
EXISTS（SELECT; ACH ROW MODE DB2SQL
;< space>
消息：一个意外的令牌WHEN EXISTS（在&b $ b&b之后找到了选择）ACH ROW MODE DB2SQL
" ;.期望的令牌可能包括： << space>"。
根据我对语法的解释，有三个问题
图：''没有CASCADE''是需要的在''之前''之前;'WHEN'条件下需要一组括号
;并且'SIGNAL'之后不允许使用分号
。 ..''这种格式的声明。当所有错误
被纠正后，触发器按照需要构建。
原始触发的错误信息r定义（在几乎
撇号被纠正之后显然是错误的。它是：
DB2 SQL错误：SQLCODE：-104，SQLSTATE：42601，SQLERRMC：v_n;选择
count（*）into;< space> ;
消息：意外的令牌v_n在选择
count（*）into之后被发现。预期的令牌可能包括：< space>。
它应该是：
DB2 SQL错误：SQLCODE：-104 ，SQLSTATE：42601，SQLERRMC：into;选择
count（*）;< space>
消息：意外令牌进入在select
count（*）之后被发现。预期的令牌可能包括：< space>。
但语法扫描程序无法将（关键字）从
区分为（列别名）。能够在select语句中强制执行''AS''
会很高兴;它必须是某种pragma
或保持标准纯粹主义者快乐的选项，但它会使消息更容易解释
解释。
I am trying to create a duplicate prevention trigger:
CREATE TRIGGER is3.ard_u_unique
BEFORE UPDATE OF act_recov_date ON is3.flushes
REFERENCING NEW AS N
FOR EACH ROW MODE DB2SQL
WHEN (N.act_recov_date IS NOT NULL)
BEGIN ATOMIC
select count(*) into v_n
from is3.flushes
where bhid=N.bhid
and act_recov_date=N.act_recov_date;
if v_n>0 then
SIGNAL SQLSTATE ?75000?
SET MESSAGE_TEXT=?Duplicate actual recovery date?;
end if;
END
When I run it I receive the error message:
DB2 SQL error: SQLCODE: -7, SQLSTATE: 42601, SQLERRMC: ;SIGNAL SQLSTATE
Message: The character " " following "SIGNAL SQLSTATE" is not valid.
Line: 12
As far as I can see, the SIGNAL statement is well formed, so the error
is elsewhere, but I don''t se the error.
Any help would be appreciated.
PS Is there any syntax for ''BEFORE UPDATE OR INSERT''? I didn''t find it,
but that don''t mean much in the many megabytes (gigabytes?) of
documentation,
Bob Stearns wrote:I am trying to create a duplicate prevention trigger:
CREATE TRIGGER is3.ard_u_unique
BEFORE UPDATE OF act_recov_date ON is3.flushes
REFERENCING NEW AS N
FOR EACH ROW MODE DB2SQL
WHEN (N.act_recov_date IS NOT NULL)
BEGIN ATOMIC
select count(*) into v_n
from is3.flushes
where bhid=N.bhid
and act_recov_date=N.act_recov_date;
if v_n>0 then
SIGNAL SQLSTATE ?75000?
SET MESSAGE_TEXT=?Duplicate actual recovery date?;
end if;
END
When I run it I receive the error message:
DB2 SQL error: SQLCODE: -7, SQLSTATE: 42601, SQLERRMC: ;SIGNAL SQLSTATE
Message: The character " " following "SIGNAL SQLSTATE" is not valid.
Line: 12 Is this DB2 UDB for LUW? I''m a tad surprised by the SQLCODE -7. Would
expect a -104.
Anyway in LUW "SELECT INTO" is not supported in a trigger.
Either way doing a count(*) just to test existence is one expensive mode
of operation (and you better not running RS or RR isolation....!)
Try this:
CREATE TRIGGER is3.ard_u_unique
BEFORE UPDATE OF act_recov_date ON is3.flushes
REFERENCING NEW AS N
FOR EACH ROW MODE DB2SQL
WHEN EXISTS(SELECT FROM is3.flushes
WHERE bhid=N.bhid
and act_recov_date=N.act_recov_date
and N.act_recov_date IS NOT NULL)
SIGNAL SQLSTATE ?75000?
SET MESSAGE_TEXT=?Duplicate actual recovery date?;
You will be pleasantly surprised by the snappyness of the beast,
especially on DPF :-)
PS Is there any syntax for ''BEFORE UPDATE OR INSERT''? I didn''t find it,
but that don''t mean much in the many megabytes (gigabytes?) of
documentation, Shockingly enough the reference is rather organized ;-)
-CREATE TRIGGER--trigger-name--+-NO CASCADE BEFORE-+---------->
+-AFTER-------------+
''-INSTEAD OF--------''
--+-INSERT--------------------------+--ON--+-table-name-+------>
+-DELETE--------------------------+ ''-view-name--''
''-UPDATE--+---------------------+-''
| .-,-----------. |
| V | |
''-OF----column-name-+-''
There is no loop-back covering INSERT, DELETE, UPDATE. So the answer is
no. No need to go hunting thorugh the other gig of docs.
Truth be told. Your request popps up once in a whiel, but typically the
reaction is: "Oh well..". You can use CALL in Trigger to encapuslate and
share more complex logic.
Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Serge Rielau wrote:
Bob Stearns wrote:I am trying to create a duplicate prevention trigger:
CREATE TRIGGER is3.ard_u_unique
BEFORE UPDATE OF act_recov_date ON is3.flushes
REFERENCING NEW AS N
FOR EACH ROW MODE DB2SQL
WHEN (N.act_recov_date IS NOT NULL)
BEGIN ATOMIC
select count(*) into v_n
from is3.flushes
where bhid=N.bhid
and act_recov_date=N.act_recov_date;
if v_n>0 then
SIGNAL SQLSTATE ?75000?
SET MESSAGE_TEXT=?Duplicate actual recovery date?;
end if;
END
When I run it I receive the error message:
DB2 SQL error: SQLCODE: -7, SQLSTATE: 42601, SQLERRMC: ;SIGNAL SQLSTATE
Message: The character " " following "SIGNAL SQLSTATE" is not valid.
Line: 12
Is this DB2 UDB for LUW? I''m a tad surprised by the SQLCODE -7. Would
expect a -104.
Anyway in LUW "SELECT INTO" is not supported in a trigger.
Either way doing a count(*) just to test existence is one expensive mode
of operation (and you better not running RS or RR isolation....!)
Try this:
CREATE TRIGGER is3.ard_u_unique
BEFORE UPDATE OF act_recov_date ON is3.flushes
REFERENCING NEW AS N
FOR EACH ROW MODE DB2SQL
WHEN EXISTS(SELECT FROM is3.flushes
WHERE bhid=N.bhid
and act_recov_date=N.act_recov_date
and N.act_recov_date IS NOT NULL)
SIGNAL SQLSTATE ?75000?
SET MESSAGE_TEXT=?Duplicate actual recovery date?;
You will be pleasantly surprised by the snappyness of the beast,
especially on DPF :-)PS Is there any syntax for ''BEFORE UPDATE OR INSERT''? I didn''t find
it, but that don''t mean much in the many megabytes (gigabytes?) of
documentation,
Shockingly enough the reference is rather organized ;-)>>-CREATE TRIGGER--trigger-name--+-NO CASCADE BEFORE-+---------->
+-AFTER-------------+
''-INSTEAD OF--------''>--+-INSERT--------------------------+--ON--+-table-name-+------>
+-DELETE--------------------------+ ''-view-name--''
''-UPDATE--+---------------------+-''
| .-,-----------. |
| V | |
''-OF----column-name-+-''
There is no loop-back covering INSERT, DELETE, UPDATE. So the answer is
no. No need to go hunting thorugh the other gig of docs.
Truth be told. Your request popps up once in a whiel, but typically the
reaction is: "Oh well..". You can use CALL in Trigger to encapuslate and
share more complex logic.
Cheers
Serge
Thank you, I didn''t remember the EXISTS predicate. A NULL usage /
interpretation question from your example occurs to me. Given the
and act_recov_date=N.act_recov_date
phrase, isn''t the phrase
and N.act_recov_date IS NOT NULL
redundant? I thought NULL=NULL was unknown, therefore not true.
That is the definition from the ''SQL Reference Volume 2'' manual, I
believe, and I agree with you. That there are no later definitions is
harder to determine. When does IBM plan (to your knowledge, of course)
to create a new set of complete downloadable manuals? At the advent of
(the hypothetical, I know, I know) Version 9 or at some earlier point?
It would reduce (not eliminate) the need for searching multiple sources,
especially those which must be searched on line, for such questions.
Serge Rielau wrote:
Bob Stearns wrote:I am trying to create a duplicate prevention trigger:
CREATE TRIGGER is3.ard_u_unique
BEFORE UPDATE OF act_recov_date ON is3.flushes
REFERENCING NEW AS N
FOR EACH ROW MODE DB2SQL
WHEN (N.act_recov_date IS NOT NULL)
BEGIN ATOMIC
select count(*) into v_n
from is3.flushes
where bhid=N.bhid
and act_recov_date=N.act_recov_date;
if v_n>0 then
SIGNAL SQLSTATE ?75000?
SET MESSAGE_TEXT=?Duplicate actual recovery date?;
end if;
END
When I run it I receive the error message:
DB2 SQL error: SQLCODE: -7, SQLSTATE: 42601, SQLERRMC: ;SIGNAL SQLSTATE
Message: The character " " following "SIGNAL SQLSTATE" is not valid.
Line: 12
Is this DB2 UDB for LUW? I''m a tad surprised by the SQLCODE -7. Would
expect a -104.
Anyway in LUW "SELECT INTO" is not supported in a trigger.
Either way doing a count(*) just to test existence is one expensive mode
of operation (and you better not running RS or RR isolation....!)
Try this:
CREATE TRIGGER is3.ard_u_unique
BEFORE UPDATE OF act_recov_date ON is3.flushes
REFERENCING NEW AS N
FOR EACH ROW MODE DB2SQL
WHEN EXISTS(SELECT FROM is3.flushes
WHERE bhid=N.bhid
and act_recov_date=N.act_recov_date
and N.act_recov_date IS NOT NULL)
SIGNAL SQLSTATE ?75000?
SET MESSAGE_TEXT=?Duplicate actual recovery date?;
You will be pleasantly surprised by the snappyness of the beast,
especially on DPF :-)PS Is there any syntax for ''BEFORE UPDATE OR INSERT''? I didn''t find
it, but that don''t mean much in the many megabytes (gigabytes?) of
documentation,
Shockingly enough the reference is rather organized ;-)>>-CREATE TRIGGER--trigger-name--+-NO CASCADE BEFORE-+---------->
+-AFTER-------------+
''-INSTEAD OF--------''>--+-INSERT--------------------------+--ON--+-table-name-+------>
+-DELETE--------------------------+ ''-view-name--''
''-UPDATE--+---------------------+-''
| .-,-----------. |
| V | |
''-OF----column-name-+-''
There is no loop-back covering INSERT, DELETE, UPDATE. So the answer is
no. No need to go hunting thorugh the other gig of docs.
Truth be told. Your request popps up once in a whiel, but typically the
reaction is: "Oh well..". You can use CALL in Trigger to encapuslate and
share more complex logic.
Cheers
Serge
We both missed the actual cause of the error. I copied some of the code
from the manual, and the apostrophes were not apostrophes (ASCII 39) but
rather a high bit character (ASCII 146) which is almost an apostrophe in
appearance.
Now on to the next problem. After correcting the problem with the almost
apostrophes, I tried building the trigger as you gave it and got the
following message:
DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: WHEN
EXISTS(SELECT;ACH ROW MODE DB2SQL
;<space>
Message: An unexpected token "WHEN EXISTS(SELECT" was found following
"ACH ROW MODE DB2SQL
". Expected tokens may include: "<space>".
There were three problems according to my interpretation of the syntax
diagram: ''NO CASCADE'' is needed before ''BEFORE''; a set of parentheses is
needed around the ''WHEN'' condition; and the semicolon is not allowed
after the ''SIGNAL ...'' statement in this format. When all the errors
were corrected, the trigger built as desired.
The error message for the original trigger definition (after the almost
apostrophes were corrected is apparently wrong. It is:
DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: v_n;select
count(*) into;<space>
Message: An unexpected token "v_n" was found following "select
count(*) into". Expected tokens may include: "<space>".
It should be:
DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: into;select
count(*) ;<space>
Message: An unexpected token "into" was found following "select
count(*)". Expected tokens may include: "<space>".
but the syntax scanner can not differentiate into (the keyword) from
into (the column alias). It would be nice to be able to make ''AS''
mandatory in select statements; it would have to be some sort of pragma
or option to keep the standard purists happy, but it would make message
interpretation easier.
这篇关于Mysterious Trigger错误DB2 UDB v8.1.5的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
