problem with trigger and insert ... select
I'm working on a legacy system and was trying to solve some issues with triggers on the database side in order to avoid making major changes to the system. I'm having a problem when dealing with the following trigger:
CREATE TRIGGER ins_search AFTER INSERT ON mst
FOR EACH ROW
BEGIN
INSERT INTO mst1
(
subfieldid, bibid, tag, tagorder, tag_indicator,
subfieldcode, subfieldorder, subfieldvalue, marcfield
)
SELECT
subfieldid, bibid, tag, tagorder, tag_indicator,
subfieldcode, subfieldorder,
IF(ISNULL(NEW.subfieldvalue), b.subfieldvalue, m.subfieldvalue) AS subfieldvalue
CONCAT(tag,subfieldcode)
FROM mst m
LEFT JOIN mbs b ON NEW.valuebloblink=b.blobidlink
WHERE subfieldid=NEW.subfieldid;
END;
The system before every insert on mst evaluates whether the length of the data for subfieldvalue is <=255 or >255.
For the cases when this field's data length exceeds 255, such data is inserted at mbs.subfieldvalue rather than at mst.subfieldvalue.
The goal is to store allways on a table 'mst1', regardless the data length of subfieldvalue.
So, when the system inserts on mst, there are 2 posibilities; either is length(mst.subfieldvalue) > 255 or not.
i) case length(mst.subfieldvalue) > 255
the data for this particular field is inserted in mbs.subfieldvalue (longtext) rather than in mst.subfieldvalue (varchar(255)), mst.subfielvalue is set to null and mst.valuebloblink is set to max(mbs.blobidlink).
ii) case length(mst.subfieldvalue) <= 255
the data for this particular field is inserted in mst.subfieldvalue (varchar(255)) and mst.valuebloblink is set to null.
Brief intro made, now the problem:
When the trigger executes, for case (ii) everything works fine and the correct data is inserted at mst1.
For case (i) the trigger exits ok, but data at mst1 is not inserted properly: mst1.subfielvalue is set to null (but all other fields are ok).
Any suggestions?
Tks!