Der MySQL-Trigger funktioniert nach dem Löschupdate nicht

Question

Es tut mir leid, diese Frage noch einmal zu stellen, da es im Forum viel zu diesem Thema gibt. Aber ich hoffe, dass mein Problem anders ist als andere. Entschuldigung für mein schlechtes Englisch.

Zuerst habe ich 2 Tische (übergeordneter Tisch und untergeordneter Tisch) Elternteil (Standort)

loc_id	loc_size
1	100
2	75

Kinder (Bereich)

ar_id	ar_loc_id	ar_size
1	2	35
2	2	40

Das ist mein Auslöser nach dem Löschen.

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;

Wenn ich beispielsweise ar_id = '2' löschen möchte, wird ar_size an der Position loc_size aktualisiert.

Da der loc_size-Wert „0“ ist, beträgt der Wert 40, nachdem der Trigger ausgeführt wurde.

Das Problem besteht darin, dass der Trigger nicht korrekt ausgeführt wird. Nachdem der Trigger ausgeführt wurde, wird loc_size überhaupt nicht aktualisiert, sondern nur der Wert „0“.

Übersehe ich etwas oder passiert etwas, weil es einfach nicht funktioniert?

Bitte helfen Sie mir, dieses Problem zu lösen. Vielen Dank.

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 语句也被撤消。