mysql trigger after insert trigger update value

Question

I want to update list status which is in list table according to task status which is in task table, both the table have listid as common field if all taskid task status is 1 then change the list status to 1 if all taskid taskstatus is not 1 then change the list status to 0 below is the trigger. i am getting sql syntex error, please let me know what is wrong

  DROP TRIGGER IF EXISTS tada.list//

  CREATE TRIGGER tada.list
  AFTER INSERT ON tada.task
  FOR EACH ROW
 BEGIN
 UPDATE list t1 , task t2 SET t1.liststatus = t2.taskstatus FROM list t1 , task t2 WHERE t1.taskid = t2.taskid;
 END//

Start by isolating the problem. Try replacing the UPDATE statement with SELECT 1 and see if you still have an error. It might be in the DDL/TRIGGER and it might be in the DML. — AJ., Sep 21 '12 at 04:31
why does a list have a taskid? should the task have a listid instead? — iouri, Sep 21 '12 at 04:48

iouri · Answer 1 · 2012-09-21T05:51:09.590

I would get the count of all tasks for that list and subtract it from the sum of task statuses (if 1 and 0 are the only statuses you have) then add 1 to the result, then use GREATEST(result,0) in your update statement.

For example, you have 10 tasks, 8 have status of 1:

UPDATE list l set status = GREATEST((SELECT SUM(t.status)-count(*)+1 from task t
where t.listid = 100), 0) where l.listid = 100;

This would set it to 0, since 8-10+1 = -1 and GREATEST will pick 0. If all tasks are done, math would be 10-10+1 = 1 and GREATEST will use 1.

You can put a conditional in the trigger to only update list if value is 1, otherwise it will trigger an update each time task is inserted.

You should also rename your trigger, having it as tada.list doesn't make sense.

CREATE TRIGGER update_list_status AFTER INSERT ON tada.task
FOR EACH ROW BEGIN
  UPDATE list l set status = GREATEST((SELECT SUM(t.status)-count(*)+1 from task t
  where t.listid = new.listid), 0) where l.listid = new.listid;
END

You might need to replace listid with whatever unique id you are using that both tasks and list have. Like I said in my comment, taskid doesn't make sense on the list.

i am getting the following error #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT SUM(taskstatus)-count(*)+1 from task where listid = new.listid, 0) where ' at line 2 — user1662799, Sep 21 '12 at 05:14
CREATE TRIGGER update_list_status AFTER INSERT ON tada.task FOR EACH ROW BEGIN UPDATE list set liststatus = GREATEST(SELECT SUM(taskstatus)-count(*)+1 from task where listid = new.listid, 0) where listid = new.listid; END — user1662799, Sep 21 '12 at 05:16
Put inner select into (). GREATEST((SELECT SUM(taskstatus)-count(*)+1 from task where listid = new.listid), 0) — iouri, Sep 21 '12 at 05:52

mysql trigger after insert trigger update value

1 Answers1