刪除更新後 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 語句也被撤銷。