Trigger and CSV Import

I am prototyping a new application which will include existing data that is in a series of excel sheets which are drive a series of fairly complex calculations. I want to avoid doing the calculations when the data is viewed by the users as the response time is then slow. So trying to set a few triggers to run when I import the data. There will then also be a set to deal with updates that will be done from within the new application rather than the existing excel based app. Owning up to not being experienced with SQL at this point….

The key trigger works fine if I manually drive it from an INSERT in the Query Builder but not quite if I drive from a CSV Import. Suspect it is my lack of understanding.

There are three tables each loaded form a CSV Import
poomsaerefereeevents – no trigger, no calculated field
poomsaerefereeexperiences – two triggers on insert, one on before inserts which updates based on a field in poomsae refereeevenst ./ this trigger works, and a one after inserts which updates multiple fields in the table poomsaerefereedetails
poomsaerefereedetails – no trigger but multiple fields calculated based on the trigger on poomsaerefereeexperiences.

The tables are load in the order of poomsaerefereeevents, poomsaerefereedetails, and the poomsaerefereeexperiences so the calculation can be handled successfully.

The trigger nearly works and I have restructured and split up multiples differents ways to fix the remain issue (hence the rather odd layout) which I am now struck with so any help would be some much appreciated. The remaining is that
SET poomsaerefereedetails.PointsSincePromotion = 0
does not fire on import but does on a manual INSERT. Even though the other clauses in this branch in the trigger do appear to work, so for example poomsaerefereedetails.Class = NEW.NewClass does work even though it is in the same SQL statement.

Thank you for any suggestions to try.

Btw, great plugin. Been really easy to mock up the rest of the application, just need to be able to get the data load to work 100%.

Thanks. Gerry
—–

CREATE TRIGGER updateRefereeRecord AFTER INSERT ON poomsaerefereeexperiences
FOR EACH ROW
BEGIN
DECLARE CurrentLastExperience DATE;
DECLARE CurrentLastPromotion DATE;
DECLARE CurrentEventDate DATE;

SELECT  LastExperience, LastPromotion INTO CurrentLastExperience, CurrentLastPromotion
FROM poomsaerefereedetails WHERE NEW.refereename= poomsaerefereedetails.refereename;

SELECT EventDate INTO CurrentEventDate 
FROM poomsaerefereeevents WHERE NEW.EventName = poomsaerefereeevents.EventName;

UPDATE poomsaerefereedetails SET poomsaerefereedetails.PointsLifeTime = poomsaerefereedetails.PointsLifeTime + NEW.Experience WHERE NEW.refereename= poomsaerefereedetails.refereename;
/* If this is the latest experience then record the date */
IF (CurrentEventDate > CurrentLastExperience) THEN
    UPDATE poomsaerefereedetails SET poomsaerefereedetails.LastExperience = CurrentEventDate WHERE NEW.refereename= poomsaerefereedetails.refereename;
END IF;
/* Update Class but error check that this promotion is latest */
/* Assumes that a course resulting in a promotion will not have experience points */
IF (NEW.NewClass LIKE '%Class%' AND CurrentEventDate > CurrentLastPromotion) THEN
        UPDATE poomsaerefereedetails SET poomsaerefereedetails.PointsSincePromotion = 0,
        poomsaerefereedetails.Class = NEW.NewClass,
         poomsaerefereedetails.LastPromotion = CurrentEventDate
        WHERE NEW.refereename= poomsaerefereedetails.refereename;
ELSE
    UPDATE poomsaerefereedetails SET poomsaerefereedetails.PointsSincePromotion = poomsaerefereedetails.PointsSincePromotion + New.Experience WHERE NEW.refereename= poomsaerefereedetails.refereename;
END IF;
IF (NEW.Comment LIKE '%Para%') THEN
    UPDATE poomsaerefereedetails SET poomsaerefereedetails.Para = true
    WHERE NEW.refereename= poomsaerefereedetails.refereename;
END IF;
IF (NEW.Comment LIKE '%Freestyle%') THEN
    UPDATE poomsaerefereedetails SET poomsaerefereedetails.FreeStyle = true
    WHERE NEW.refereename= poomsaerefereedetails.refereename;
END IF;
IF (NEW.Comment LIKE '%Chief%') THEN
    UPDATE poomsaerefereedetails SET poomsaerefereedetails.ChiefReferee = true
    WHERE NEW.refereename= poomsaerefereedetails.refereename;
END IF;
END;
/* Need to active record, is last experience less than 2 year */
/