插入觸發器後的MYSQL未執行

我對這個 MYSQL 觸發器有問題似乎無法弄清楚問題所在。我有如下表。

create table assets(

id int,
model_id int,
asset_type varchar(6)
);

create table models(
id int,
category_id int,
name varchar(20)
);

create table categories(
id int,
cat_prefix varchar(4)
);

有了這個，我希望根據 model_id 將 cat_prefix 值插入到 assets 表上的每個插入的asset_type 列中。當然，這是表格的簡化版本。

所以我在下面寫了一個插入觸發器。

DELIMITER //
CREATE TRIGGER asset_prefix_after_insert AFTER INSERT  ON assets
FOR EACH ROW
 BEGIN
DECLARE model_id INT DEFAULT NULL ;
DECLARE category_id INT DEFAULT  NULL ;
DECLARE category_prefix VARCHAR(6);
DECLARE msg varchar(128);

SELECT NEW.model_id INTO model_id;

IF (model_id IS NOT NULL) THEN

 SELECT category_id from models
 WHERE id = model_id
 INTO category_id;

END IF;
IF (category_id IS NOT NULL) THEN
 SELECT cat_prefix from categories
 WHERE id = category_id
 INTO category_prefix;
END IF;

IF (category_prefix IS NOT NULL) THEN
 INSERT INTO assets
 (asset_type)
 VALUES (category_prefix);
END IF;

END //

DELIMITER ;

但它在插入時沒有任何作用。我在這裡想念什麼？請幫忙。謝謝。

大多只是一些小錯誤，其中最大的就是聲明的模棱兩可category_id。

SELECT category_id from models
WHERE id = model_id
INTO category_id;

該語句正在將DECLARE‘d的值category_id（即DEFAULT, null ）選擇到DECLARE’dcategory_id中。這使得以下分支無法訪問，因為它們將始終評估為假。為了消除這種歧義，您可以為您的表設置別名。

SELECT  m.category_id
FROM    models m
WHERE   m.id = model_id
INTO    category_id;

這裡的教訓是總是給你的表起別名。根據自然法則，對於每一種意見，都有平等和相反的意見，但在這種情況下，我是對的。每次都為您的表命名。它極大地提高了可讀性，並有助於避免像這樣的小錯誤。

觸發器的下一個問題是您所說的內容（在每一行的列中插入一個值asset_type）和您編碼的內容（在assets每一行的表中插入一行）之間存在差異。

IF (category_prefix IS NOT NULL) THEN
 INSERT INTO assets
 (asset_type)
 VALUES (category_prefix);
END IF;

該聲明說要插入

$$ (null), (null), category_prefix $$元組到資產表中。除了可能不是實際意圖之外，由於您的觸發器是插入後觸發器，如果觸發器要實際執行此操作，它將無限循環。您需要做的是設置NEW列的值：

IF ( category_prefix IS NOT NULL ) 
THEN
 SET NEW.asset_type = category_prefix;
END IF; 

但是，MySQL 也會抱怨這一點，因為您無法在插入後觸發器中更新 NEW 列，因為寫入已經完成：它已經完成。要完成這項工作，有必要將觸發器從後插入更改為前插入。

CREATE TRIGGER asset_prefix_before_insert 
 BEFORE INSERT ON assets

通過這些更改，您最終可能會得到如下所示的內容：

DELIMITER //
CREATE TRIGGER asset_prefix_before_insert 
 BEFORE INSERT ON assets
 FOR EACH ROW
BEGIN

 DECLARE model_id INT DEFAULT NULL;
 DECLARE category_id INT DEFAULT NULL;
 DECLARE category_prefix VARCHAR( 6 );

 SELECT  NEW.model_id INTO model_id;

 IF ( model_id IS NOT NULL ) 
 THEN
   SELECT  m.category_id 
   FROM    models m
   WHERE   m.id = model_id
   INTO    category_id;
 END IF;

 IF ( category_id IS NOT NULL ) 
 THEN
   SELECT  c.cat_prefix 
   FROM    categories c
   WHERE   c.id = category_id
   INTO    category_prefix;
 END IF;

 IF ( category_prefix IS NOT NULL ) 
 THEN
   SET NEW.asset_type = category_prefix;
 END IF;

END; //

INSERT  INTO categories ( id, cat_prefix )
VALUES  ( 1, 'x' ),
       ( 2, 'y' ),
       ( 3, 'z' );

INSERT  INTO models ( id, category_id )
VALUES  ( 1, 1 ),
       ( 2, 2 ),
       ( 3, 3 );

INSERT  INTO assets ( id, model_id )
VALUES  ( 1, 1 ),
       ( 2, 2 ),
       ( 3, 3 );

SELECT  *
FROM    assets;

引用自：https://dba.stackexchange.com/questions/181550