MySQL trigger not working after delete update

Question

Sorry to ask this question again as there is a lot on the forum about this issue. But I hope my problem is different from others. Sorry for my bad English.

First I have 2 tables (parent table and child table) Parent(Position)

loc_id	loc_size
1	100
2	75

Children (area)

ar_id	ar_loc_id	ar_size
1	2	35
2	2	40

This is my post-delete trigger.

CREATE TRIGGER after_delete_area_location
AFTER DELETE
   ON area FOR EACH ROW

BEGIN

UPDATE location SET loc_size = loc_size + old.ar_size WHERE loc_id=old.ar_loc_id

END;

For example, if I want to delete ar_id = '2', then ar_size will be updated in position loc_size.

Since the loc_size value is "0", the value will be 40 after the trigger runs.

The problem is that the trigger is not running correctly, after the trigger runs loc_size is not updated at all, just the "0" value.

Am I missing something or something is happening because it simply doesn't work.

Please help me solve this problem. Thank you very much.

Answer 1

I see there is nothing wrong with your trigger. I tested it and it's working. To make it complete, I added an insert trigger on table region.

create table location(loc_id int,loc_size int);
create table area(ar_id int,ar_loc_id int,ar_size int);


delimiter //

CREATE TRIGGER after_insert_area_location
AFTER insert
   ON area FOR EACH ROW

BEGIN

UPDATE location SET loc_size = loc_size - new.ar_size WHERE loc_id=new.ar_loc_id;

-- Note: In reality, you should throw in an if statement before the UPDATE to make sure there is enough loca_size to be taken away by the ar_size.
END//


CREATE TRIGGER after_delete_area_location
AFTER DELETE
   ON area FOR EACH ROW

BEGIN

UPDATE location SET loc_size = loc_size + old.ar_size WHERE loc_id=old.ar_loc_id;

END//
delimiter ;

insert into location values(1,100),(2,75);

select * from location;
+--------+----------+
| loc_id | loc_size |
+--------+----------+
|      1 |      100 |
|      2 |       75 |
+--------+----------+

select * from area;
Empty set (0.00 sec)


-- Test the insert
insert into area values(1,2,35),(2,2,40);
select * from area;
+-------+-----------+---------+
| ar_id | ar_loc_id | ar_size |
+-------+-----------+---------+
|     1 |         2 |      35 |
|     2 |         2 |      40 |
+-------+-----------+---------+

 select * from location;
+--------+----------+
| loc_id | loc_size |
+--------+----------+
|      1 |      100 |
|      2 |        0 |
+--------+----------+

-- test the delete
delete from area where ar_id=2;

select * from area;
+-------+-----------+---------+
| ar_id | ar_loc_id | ar_size |
+-------+-----------+---------+
|     1 |         2 |      35 |
+-------+-----------+---------+

select * from location;
+--------+----------+
| loc_id | loc_size |
+--------+----------+
|      1 |      100 |
|      2 |       40 |
+--------+----------+

As you can see, in both insert and delete operations, the values ​​are updated accordingly.
-- Update from here --

This is an enhanced version of trigger after_insert_area_location where I will demonstrate the newly added IF statement to maintain data integrity. Note that the SIGNAL statement is used to raise an error, which acts as a warning message and terminates the operation.

-- First of all, I truncated my table so we can start from scratch.
-- Make sure the original trigger is dropped before creating the newer one to avoid conflicts.

drop trigger after_insert_area_location //
CREATE TRIGGER after_insert_area_location
AFTER insert
   ON area FOR EACH ROW

BEGIN
if (select loc_size from location where loc_id=new.ar_loc_id) - new.ar_size < 0 then
signal sqlstate '77777' set message_text='You cannot take more land than you have'; -- Note: the sqlstate code and the message_text can be modified to your liking
end if;
UPDATE location SET loc_size = loc_size - new.ar_size WHERE loc_id=new.ar_loc_id;

END//

delimiter ;

-- then let's populate the location table:
insert into location values(1,100),(2,75);

select * from location;
+--------+----------+
| loc_id | loc_size |
+--------+----------+
|      1 |      100 |
|      2 |       75 |
+--------+----------+

select * from area;
Empty set (0.00 sec)

-- Now we test the insert one row at a time:
 insert into area values(1,2,35);

 select * from location;
+--------+----------+
| loc_id | loc_size |
+--------+----------+
|      1 |      100 |
|      2 |       40 |
+--------+----------+

 select * from area;
+-------+-----------+---------+
| ar_id | ar_loc_id | ar_size |
+-------+-----------+---------+
|     1 |         2 |      35 |
+-------+-----------+---------+

-- now we add another row with an ar_size more than its location can afford:

insert into area values(2,2,80);
ERROR 1644 (77777): You cannot take more land than you have

As you can see, the SIGNAL statement in the trigger's IF statement raised an error with the SQL_STATE code 77777 and the message: Is Preliminary set. This will undo the changes made since the new row was inserted.

select * from location;
+--------+----------+
| loc_id | loc_size |
+--------+----------+
|      1 |      100 |
|      2 |       40 |
+--------+----------+

select * from area;
+-------+-----------+---------+
| ar_id | ar_loc_id | ar_size |
+-------+-----------+---------+
|     1 |         2 |      35 |
+-------+-----------+---------+

In short, we can use IF statements in triggers to have some control over the data flow. The SIGNAL statement can be used to intentionally invoke an error to stop/undo the execution of a trigger and start the action of a trigger. As mentioned above, not only is the UPDATE in the trigger not executed, the insert statement that triggered the trigger is also undone.