MySql Trigger After Update does not update even when all is correct

Question

Tab_1

"id"    "name"      "addr"  "country"   "status"
"1"     "norman"    "1st"   "US"        "2"
"2"     "kirk"      "2nd"   "US"        "2"

Tab_2

"id"    "name"      "addr"  "country"   "pos"   "total"
"1"     "norman"    "1st"   "US"        "0"     "0"
"2"     "kirk"      "2nd"   "US"        "0"     "0"

I'm using the below trigger after update on Tab_1 to update Tab_2 when the status in Tab_1 is set to 0. Problem is, It'll do nothing even when It's correct. How do I set this right?

CREATE DEFINER=`root`@`localhost` TRIGGER `tab_2_upd` AFTER UPDATE ON `tab_1` 
FOR EACH ROW BEGIN
if new.status = '0' then
update tab_2 set pos = pos+1, total = total+1 where id = new.id;
//Should it be new.id or old.id? I tried both but still no luck.
end if;
END

score 1 · Accepted Answer · edited May 23 '17 at 11:50

This is another case of multiple statements being broken because you haven't changed the delimiter. It's a pretty common error.

Try this:

DELIMITER $$

DROP TRIGGER IF EXISTS  `tab_2_upd`$$

CREATE DEFINER=`root`@`localhost` TRIGGER `tab_2_upd` AFTER UPDATE ON `tab_1` 
FOR EACH ROW BEGIN
    if new.status = '0' then
        update tab_2 set pos = pos+1, total = total+1 where id = new.id;
    end if;
END$$

DELIMITER ;

ON UPDATE triggers have both OLD and NEW values, and since I don't think that value of id is ever going to change (it shouldn't) it is safe to use any value (OLD or NEW).

There's a nice answer on delimiters here.

MySql Trigger After Update does not update even when all is correct

1 Answers1