mysql - 带有更新的 MYSQL 触发器
问题描述
在更新同一个表之后,我正在尝试通过引用另一个表来构建一个触发器来更新表字段。我已经尝试了以下代码,但每次尝试都会收到错误 #1064。有人可以提出什么问题。引用尝试 1。
BEGIN
UPDATE `incent` a INNER JOIN `inc_prog` b
SET a.`earned` =
CASE WHEN a.incactualn < b.n1 THEN 0
CASE WHEN a.incactualn BETWEEN b.n1 AND b.m1 THEN b.arp1
CASE WHEN a.incactualn BETWEEN b.n2 AND b.m2 THEN b.arp1+b.arp2
CASE WHEN a.incactualn BETWEEN b.n3 AND b.m3 THEN b.arp1+b.arp2+b.arp3
CASE WHEN a.incactualn BETWEEN b.n4 AND b.m4 THEN b.arp1+b.arp2+b.arp3+b.arp4
CASE WHEN a.incactualn BETWEEN b.n5 AND b.m5 THEN b.arp1+b.arp2+b.arp3+b.arp4+b.arp5
END
===结束尝试 1
尝试 2
BEGIN
UPDATE
a
SET
a.earned =
CASE WHEN a.incactualn < b.n1 THEN '0'
CASE WHEN a.incactualn BETWEEN b.n1 AND b.m1 THEN b.arp1
CASE WHEN a.incactualn BETWEEN b.n2 AND b.m2 THEN b.arp1+b.arp2
CASE WHEN a.incactualn BETWEEN b.n3 AND b.m3 THEN b.arp1+b.arp2+b.arp3
CASE WHEN a.incactualn BETWEEN b.n4 AND b.m4 THEN b.arp1+b.arp2+b.arp3+b.arp4
CASE WHEN a.incactualn BETWEEN b.n5 AND b.m5 THEN b.arp1+b.arp2+b.arp3+b.arp4+b.arp5
FROM
incent a
INNER JOIN
inc_prog b
ON
a.Incent_name = b.incname && a.period = b.finyear
END
======= 结束尝试 2
取消报价
解决方案
您只需将单词 CASE 放在 CASE 表达式的开头。只需删除第二个和后续副本。所以你的第一个触发器变成:
BEGIN
UPDATE `incent` a INNER JOIN `inc_prog` b
SET a.`earned` =
CASE WHEN a.incactualn < b.n1 THEN 0
WHEN a.incactualn BETWEEN b.n1 AND b.m1 THEN b.arp1
WHEN a.incactualn BETWEEN b.n2 AND b.m2 THEN b.arp1+b.arp2
WHEN a.incactualn BETWEEN b.n3 AND b.m3 THEN b.arp1+b.arp2+b.arp3
WHEN a.incactualn BETWEEN b.n4 AND b.m4 THEN b.arp1+b.arp2+b.arp3+b.arp4
WHEN a.incactualn BETWEEN b.n5 AND b.m5 THEN b.arp1+b.arp2+b.arp3+b.arp4+b.arp5
END;
END
在您的第二次尝试中,您缺少 CASE 表达式末尾的 END 以及子查询周围的 (SELECT and ):
BEGIN
UPDATE a
SET a.earned = (SELECT
CASE WHEN a.incactualn < b.n1 THEN '0'
WHEN a.incactualn BETWEEN b.n1 AND b.m1 THEN b.arp1
WHEN a.incactualn BETWEEN b.n2 AND b.m2 THEN b.arp1+b.arp2
WHEN a.incactualn BETWEEN b.n3 AND b.m3 THEN b.arp1+b.arp2+b.arp3
WHEN a.incactualn BETWEEN b.n4 AND b.m4 THEN b.arp1+b.arp2+b.arp3+b.arp4
WHEN a.incactualn BETWEEN b.n5 AND b.m5 THEN b.arp1+b.arp2+b.arp3+b.arp4+b.arp5
END
FROM incent a
INNER JOIN inc_prog b ON a.Incent_name = b.incname && a.period = b.finyear);
END
推荐阅读
- angular - 如何将 *ngIf 值从一个组件传递到另一个组件?
- compiler-construction - 为什么在 ocaml 编译器源代码中打印 Parsetree.implementation 失败的数量?
- javascript - 如何在 http 调用中禁用和启用 Angular 按钮?
- c - 无法从c中的堆栈结构中复制字符串
- javalite - ActiveWeb 视图 - <@compress single_line=true>
- javascript - 使用 expressJs nodeJs Handlebars 更新部分 HTML 页面而不重新加载页面
- ios - 更改警报标题和消息
- gltf - GlTF2 存取器单元长度
- angular - how can I call a function and pass parameter when value in select tags changes? 角 6
- amazon-s3 - Electron 最新版本下载 URL 的最佳实践