删除更新后 MySQL 触发器不起作用

Question

很抱歉再次提出这个问题，因为论坛上有很多关于此问题的内容。但我希望我的问题与其他人不同。抱歉我的英语不好。

首先我有 2 个表（父表和子表） 父级（位置）

loc_id	loc_size
1	100
2	75

儿童（区域）

ar_id	ar_loc_id	ar_size
1	2	35
2	2	40

这是我的删除后触发器。

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;

例如，如果我想删除 ar_id = '2'，那么 ar_size 将在位置 loc_size 中更新。

由于 loc_size 值为“0”，因此触发器运行后该值将为 40。

问题是触发器未正确运行，触发器运行后 loc_size 根本没有更新，只是“0”值。

我缺少什么或者由于它根本不起作用而发生了什么。

请帮我解决这个问题。 非常感谢您。

Answer 1

我发现你的触发器没有问题。我测试了它并且它正在工作。为了使其完整，我在表区域上添加了插入触发器。

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 |
+--------+----------+

如您所见，在插入和删除操作中，值都已相应更新。
-- 从此处更新 --

这是触发器 after_insert_area_location 的增强版本，我将在其中演示新添加的 IF 语句以保持数据完整性。请注意，SIGNAL 语句用于引发错误，该错误充当警告消息并终止操作。

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

如您所见，触发器的 IF 语句中的 SIGNAL 语句引发了一个错误，其中包含 SQL_STATE 代码 77777 和消息：是预设的。这将撤销自插入新行以来所做的更改。

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

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

简而言之，我们可以在触发器中使用 IF 语句来对数据流进行一些控制。 SIGNAL 语句可用于故意调用错误来停止/撤消触发器的执行以及启动触发器的操作。如上所述，不仅触发器中的 UPDATE 没有执行，引发触发器的 insert 语句也被撤消。