英文:
MySQL Trigger After Deleted Update not working
问题
I'm sorry for making this question again since there is so much in the forum about this. but i hope my problem is different from others. and sorry for my bad english.
First i have 2 Table (parent and child)
Parent (Location)
loc_id | loc_size |
---|---|
1 | 100 |
2 | 75 |
Child (Area)
ar_id | ar_loc_id | ar_size |
---|---|---|
1 | 2 | 35 |
2 | 2 | 40 |
this is my after 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;
example, if i want to delete ar_id = '2' then the ar_size will be updated in the location loc_size.
since loc_size value is '0' then the value will be 40 after the trigger running.
the problem is the trigger is not running correctly, the loc_size not updating at all just '0' value after the trigger running.
what i'm missing or what happens since it's not working at all.
please kindly help me fix this issue.
Thank you so much in advance.
英文:
i'm sorry for making this question again since there is so much in the forum about this. but i hope my problem is different from others. and sorry for my bad english.
First i have 2 Table (parent and child)
Parent (Location)
loc_id | loc_size |
---|---|
1 | 100 |
2 | 75 |
Child (Area)
ar_id | ar_loc_id | ar_size |
---|---|---|
1 | 2 | 35 |
2 | 2 | 40 |
this is my after 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;
example, if i want to delete ar_id = '2' then the ar_size will be updated in the location loc_size.
since loc_size value is '0' then the value will be 40 after the trigger running.
the problem is the trigger is not running correctlly, the loc_size not updating at all just '0' value after the trigger running.
what i'm missing or what happen since its not working at all.
please kindly to help me fix this issue.
Thank you so much in advanced.
答案1
得分: 1
I see no problem in your trigger. I tested it and it is working. To make it complete, I added the insert trigger on table area
.
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 the insert and delete operations, the values have been updated accordingly.
Here is an amped-up version of trigger after_insert_area_location
, in which I shall demonstrate the newly added IF statement to retain data integrity. Note, the SIGNAL
statement is used to raise an error which serves as a warning message as well as 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 see, an error is raised by the SIGNAL
statement in the trigger's IF
statement with the SQL_STATE code 77777
and the message which are preset. This revokes the changes made since the insert of the new row.
select * from location;
+--------+----------+
| loc_id | loc_size |
+--------+----------+
| 1 | 100 |
| 2 | 40 |
+--------+----------+
select * from area;
+-------+-----------+---------+
| ar_id | ar_loc_id | ar_size |
+-------+-----------+---------+
| 1 | 2 | 35 |
+-------+-----------+---------+
To put it in a nutshell, we can use an IF statement in a trigger to exercise some control over the flow of data. And the SIGNAL
statement can be used to deliberately invoke an error to stop/undo the execution of the trigger as well as the operation that initiated the trigger. As denoted above, not only the UPDATE
in the trigger didn't get executed, the insert
statement which evoked the trigger has also been undone.
英文:
I see no problem in your trigger. I tested it and it is working. To make it complete, I added the insert trigger on table area
.
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 the insert and delete operations, the values have been updated accordingly .<br>
-- UPDATED FROM HERE -- <br>
<br>
Here is an amped-up version of trigger after_insert_area_location
, in which I shall demonstrate the newly added IF statement to retain data integrity. Note, the SIGNAL
statment is used to raise an error which serves as a warning message as well as 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 see , an error is raised by the SIGNAL
statement in the trigger's IF
statement with the SQL_STATE code 77777
and the message which are preset. This revokes the changes made since the insert of the new row.
select * from location;
+--------+----------+
| loc_id | loc_size |
+--------+----------+
| 1 | 100 |
| 2 | 40 |
+--------+----------+
select * from area;
+-------+-----------+---------+
| ar_id | ar_loc_id | ar_size |
+-------+-----------+---------+
| 1 | 2 | 35 |
+-------+-----------+---------+
To put it in a nutshell, we can use an IF statement in a trigger to exercise some control over the flow of data. And the SIGNAL
statement can be used to deliberately invoke an error to stop/undo the execution of the trigger as well as the operation that initiated the trigger. As denoted above, not only the UPDATE
in the trigger didn't get executed, the insert
statement which evoked the trigger has also been undone.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论