NEW es una pseudovariable que solamente hace referencia los campos del registro entrante.
No funciona en ese contexto (dentro de un SELECT en un TRIGGER).
Lo que te conviene es declarar una variable al inicio de cuerpo del TRIGGER y usar esa variable para recibir el valor del SELECT, y luego usar SET para asignarlo al campo correspondiente.
Mira este ejemplo, en que lo que se hace es cargar el valor de un subitem en la tabla, al modo del detalle de una factura:
Código SQL:
Ver originalDROP TRIGGER IF EXISTS SET_ITEMFACTURA;
DELIMITER $$
CREATE TRIGGER SET_ITEMFACTURA BEFORE INSERT
ON DETALLEFACTURA
FOR EACH ROW
BEGIN
IF(SELECT MAX(ITEM_FACT) FROM DETALLEFACTURA WHERE FACTURA_ID=NEW.FACTURA_ID) IS NULL THEN
SET NEW.ITEM_FACT = 1;
ELSE
SET NEW.ITEM_FACT = (SELECT MAX(ITEM_FACT) FROM DETALLEFACTURA WHERE FACTURA_ID = NEW.FACTURA_ID)+1;
END IF;
END$$
DELIMITER ;