I have a table set up like this (simplified for this example)
ID | int |Primary auto increment
Name | text |
ContainerID | int |
ContainsCount | int |
I am using the container id to refer to the table itself. So if i have a row with
ID:3 Name:Test1 ContainerID:0 ContainsCount:0
ID:4 Name:Test2 ContainerID:3 ContainsCount:0
ID:5 Name:Test3 ContainerID:3 ContainsCount:0
It means that the row 4 and 5 is inside row 3.
I would like to count how many row are inside row 3, and store it in the ContainsCount column. I know i can get this value manually by doing an additional query for every row that i need to read. But i would like to avoid the strain on the server.
I have tried setting up a trigger that increments the value every time a new row with the corresponding id is added
BEGIN
UPDATE Stock_Items Set Name = "Testing" Where ID = 2;
END
But it turns out i cannot edit the same table using a trigger.
Is there any other way to achive this?
