MySQL After Insert Trigger Not Running – Troubleshooting Guide

MySQLmysql-5.6trigger

Im having problems with this MYSQL trigger cant seem to figure it out what the problem is. i have tables like the following.

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

With that i want to have the cat_prefix value to be inserted in the asset_type column for every insert on the assets table based on model_id. Of course this are simplified versions of the tables.

So i wrote an insert trigger below.

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 ;

But it does nothing on insert. What im i missing here?. Please help. Thanks.

Best Answer

Mostly just a few little mistakes, the biggest of which is the ambiguous declaration of category_id.

SELECT category_id from models
WHERE id = model_id
INTO category_id;

This statement is selecting value of the DECLARE'd category_id ( which is the DEFAULT, null ), into the DECLARE'd category_id. This renders the following branches unreachable, as they will always evaluate as false. To eliminate this ambiguity, you can alias your table.

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

The lesson here is to always alias your tables. As per natural law, for every opinion there is an equal and opposite opinion, but in this case, I'm right. Alias your tables, all the time, every time. It drastically improves readability and serves to avoid little bugs like this one.

The next issue with the trigger is a discrepancy between what you have said ( insert a value into the asset_type column for each row ) and what you've coded ( insert a row into the assets table for each row ).

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

This statement says to insert the [ (null), (null), category_prefix ] tuple into the asset table. In addition to likely not being the actual intent, as your trigger is an after-insert trigger, if the trigger were to actually perform this operation it would infinitely loop. What you need to do is to set the value of the NEW column:

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

However, MySQL will also complain about this because you can't update a NEW column in an after-insert trigger, as the write has already completed: It's already done. To make this work, it's necessary to change the trigger from an after-insert to a before-insert.

CREATE TRIGGER asset_prefix_before_insert 
  BEFORE INSERT ON assets

With those changes, you may end up with something that looks a little like the following:

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;
Related Question