Odtnhj

I am trying to build a trigger to update a table field by referring to another table, after the same table has been updated. I have tried the following pieces of code but keep getting error #1064 for every attempt. Could someone suggest what is wrong.
quote
attempt 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

===end attempt 1

attempt 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

======= end attempt 2

UNQUOTE

You only put the word CASE at the beginning of a CASE expression. Just delete the second and subsequent copies. So your first trigger becomes:

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

In your second attempt you are missing the END at the end of the CASE expression, as well as the (SELECT and ) around the subquery:

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