hey guys I'm having trouble creating a trigger statement for my tables. I'm using the gui interface provided in phpmyadmin to create the statement, but whenever I run the insert command on the list_item table it throws the error #1054 - Unknown column 'list_item.list_item_id' in 'where clause'. I'm not sure what I've done wrong here, i do notice in the gui interface in the where clause list_item doesn't get highlighted like the other table names do. any tips on how I can fix this would be greatly appreciated!!
also i feel like i should mention what im trying to accomplish is when i add an item to the list_item table the count column in the list table should be incremented by 1 based on the list_item_id of the inserted item.
list_item table:
| id | list_item_id |
| -- | ------------ |
| 1 | 1 |
| 2 | 2 |
| 3 | 1 |
list table:
| id | list_id | count |
| -- | --------| ----- |
| 1 | 1 | 2 |
| 2 | 2 | 1 |
CREATE TRIGGER `insert_update`
AFTER INSERT ON `list_item`
FOR EACH ROW
UPDATE list
SET list.count = list.count + 1
WHERE list.list_id = list_item.list_item_id
The error is thrown because you don't invoke list_item in the update statement. However if you did another error would be thrown and would be logically incorrect, you should be testing against NEW. values
WHERE list.list_id = NEW.list_item_id
Review the manual for more details https://dev.mysql.com/doc/refman/8.0/en/trigger-syntax.html