0

I have two tables:

table1
------
id, status

table2
------
id,items

status in table1 can be one of "Paid", "Pending", "Requested".

items in table2 contains comma separated id of table1.

ie.

table1
------
1 | Requested
2 | Requested
3 | Pending

table2 
------
1 | 1,2

I wanted to update status of table1 from "Requested" to "Paid" whenever any item added into field items of table2.

Also I wanted to reset status to "Requested" if item is removed from table2 items field on update.

So created 3 triggers:

1) After INSERT:

 CREATE TRIGGER `Update status` AFTER INSERT ON `table2`
 FOR EACH ROW BEGIN
      UPDATE table1 
      SET status = 'Paid' 
      WHERE id IN (NEW.items);
 END

2) Before UPDATE:

CREATE TRIGGER `Reset on update` 
BEFORE UPDATE ON `table2`
FOR EACH ROW BEGIN
    UPDATE table1 SET status = 'Requested' WHERE 
    id IN (OLD.items)
END

3) After UPDATE:

CREATE TRIGGER `New status on update` AFTER UPDATE ON `table2`
FOR EACH ROW BEGIN
    UPDATE table1 SET status = 'Paid' WHERE id IN (NEW.items);
END

The problem is triggers are updating only one row(1st of comma separated id) of table1 in of the above all triggers.

Am I missing something?

Rahul Gavande
  • 324
  • 2
  • 11
  • It is "bad practice" to put multiple values in a single column. It is better to make another table for the many:many mapping you have. – Rick James Sep 30 '16 at 06:25
  • I have already moved my data into new table yesterday. Because its was getting hectic to manage all data in one table. Thank you Rick. – Rahul Gavande Sep 30 '16 at 07:29

1 Answers1

1

If NEW.items is 1,2,3,4,5 the statement UPDATE table1 SET status = 'Paid' WHERE id IN (NEW.items); actually means:

UPDATE table1 SET status = 'Paid' WHERE id IN ('1,2,3,4,5');

instead of:

UPDATE table1 SET status = 'Paid' WHERE id IN (1,2,3,4,5);

'1,2,3,4,5' then gets cast to an integer and becomes 1.

Vatev
  • 7,493
  • 1
  • 32
  • 39