CODEWITHSUNDEEP
mysqltriggerssql-updatesql-delete
clzola
clzola

Reputation: 2025

How to update rows in table in after delete trigger

I have a table country_group

+------+----------+--------+
| code | group_id | weight |
+------+----------+--------+
| me   | 1        | 50.00  |
+------+----------+--------+
| me   | 2        | 10.00  |
+------+----------+--------+
| me   | 3        | 40.00  |
+------+----------+--------+

To me, it is very important that for each country total sum of column weight is 100. What I want do here is when I delete one of rows I need to change weight values for that country so that sum of all weights for that country is still 100. I want to have trigger in my database for this.

For example if I delete row where weight is 10.00 then table should look like this:

+------+----------+--------+
| code | group_id | weight |
+------+----------+--------+
| me   | 1        | 55.00  |
+------+----------+--------+
| me   | 3        | 45.00  |
+------+----------+--------+

So this is my trigger:

CREATE DEFINER = CURRENT_USER TRIGGER   extensionui2.country_group_AFTER_DELETE 
AFTER DELETE ON country_group 
FOR EACH ROW
BEGIN
    DECLARE w FLOAT(5,2);
    DECLARE c INT;
    DECLARE s FLOAT(5,2);

    SET w = OLD.weight;
    SELECT count(*) into c FROM country_group WHERE code = OLD.code;

    SET s = w / c;
    UPDATE country_group SET weight = weight + s WHERE code = OLD.code;
END;

But I get this error:

Operation failed: There was an error while applying the SQL script to the database.
Executing:
DELETE FROM `extensionui2`.`country_group` WHERE `code`='ms' and`group_id`='9';

ERROR 1442: 1442: Can't update table 'country_group' in stored  function/trigger because it is already used by statement which invoked this stored function/trigger.
SQL Statement:
DELETE FROM `extensionui2`.`country_group` WHERE `code`='ms' and`group_id`='9'

What do I need to change in my trigger code to make this work?

Upvotes: 0

Views: 1059

Answers (1)

spencer7593
spencer7593

Reputation: 108400

MySQL ERROR 1442 is due to a restriction in MySQL. The trigger is not allowed to update any rows in the table that the trigger is defined on.

A stored function or trigger cannot modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger.

So, it's not possible to do what you are trying to do within the context of a trigger.

Upvotes: 2

Related Questions