Create Mysql Trigger increment/decrement attribute

Question

I have the following tables

-> app(id_app, nome_app)
-> category(id_category, nome_category, total_apps)
-> app_category(id_app_fk, id_category_fk)

What I want to do is this, that whenever I add data to the table "app_category" it checks how many apps were added related to that category and increments the value+1 to the field "total_app" in the table "Category", this field would be the total of apps belonging to each category.

The same should be done when deleting a row from the table "app_category" 1 must be decremented from the field "total_apps".

Details: how can realize the fields of the table app_category are foreign keys that reference the table "app" the table "Category".

Answer 1

You can use this pair of TRIGGERS:

CREATE TRIGGER `count_apps_insert` AFTER INSERT ON `app_category`
FOR EACH ROW
    UPDATE category
    SET total_apps = total_apps + 1
    WHERE id_category = NEW.id_category_fk;


CREATE TRIGGER `count_apps_delete` AFTER DELETE ON `app_category`
FOR EACH ROW
    UPDATE category
    SET total_apps = total_apps - 1
    WHERE id_category = OLD.id_category_fk;

In operation: http://sqlfiddle.com/#! 9/60b60/1/0