MYSQL after insert trigger is not running
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;