Create trigger MySql increment / decrement attribute

Question

Create trigger MySql increment / decrement attribute

Navigation

#1 by (1 votes)

1

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 that whenever I add data to the table "app_category" it checks how many apps were added related to that category and increment the value + 1 to the "total_app" field in the "category" table, that field would be the total of apps per category.

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

Details: How can you notice the fields in the app_category table are foreign keys that refer to the "app" table in the "category" table.

mysql sql trigger

asked by anonymous 05.08.2015 / 02:49

1 answer

Update mysql table with image How to remove redirect loop in Wp?

score 1 · Accepted Answer

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: link