create trigger updating a table column by inserting data into another table is throwing an error on-CodePudding

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

CodePudding user response：

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