Create Mysql Trigger increment/decrement attribute

Asked

Viewed 1,194 times

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 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".

1 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

  • Hello, I tested your code and always shows the following error: #1064 - You have an error in your SQL syntax; check the manual that Corresponds to your Mysql server version for the right syntax to use near ''count_apps_insert' AFTER INSERT ON 'app_category' FOR EACH ROW BEGIN UP' at line 1?

  • 1

    Alfredbaudisch your code worked with the following changes, the removal of BEGIN and END at the end of the triggers, after removing them the Rigger was created and as I already tested here worked perfectly, edit your reply when possible, I will mark it as correct, obg.

  • 1

    @Marcosjunior you noticed before I posted the correction :D Attach a Sqlfiddle showing working.

Browser other questions tagged

You are not signed in. Login or sign up in order to post.