MySQL - execute a trigger after another trigger

Question

I use MySQL 5.1 and Workbench. I need to fill a table t1 from multiple tables t2, t3 and t4 when new records are inserted into t2 (from an external form). I created a trigger on t2 (after insert) to do so. However, whenever I have new records in t2, it fills t1 with all records again (the new ones and the old ones). Thus, I tried creating a second trigger on t1 (after insert) and delete all rows in t2, t3 and t4. The result is that the external form crashes and doesn't want to export anymore the data into t2, t3 and t4. I assume it is because there is no timing between the two triggers, and that it may ty to delete the rows from t2, t3, t4 meanwhile is inserting those rows into t1. How could I do otherwise?

Please note that there is a serial primary key on t1, that is not identic in t2, t3, t4.

Here my first (INSERT) trigger (which works):

delimiter |
CREATE TRIGGER nmp_odk.trigger_maj_baznat AFTER INSERT
ON nmp_odk.DEMO_OISEAUX12_CORE
FOR EACH ROW
BEGIN
INSERT INTO nmp_baznat_test.OBSERVATION (ID_PROJET,DATE_OBS,PRECISION_DATE,DATE_SAISIE,METHODE_LOCALISATION, CREATION, id_proprietaire,id_structure,
    CODE_INSEE, ID_SITE, LONGWGS84,LATWGS84, Z, ZPRECISION,CD_DEPARTEMENT, DEPARTEMENT) 

    SELECT  PROJET_ID, DATE_OBS, 'date précise', _SUBMISSION_DATE, METHODE_LOC, DATE_OBS, CONTRIB, nmp_odk.DEMO_OISEAUX12_CORE.ID_STRUCTURE,
            insee_zerofill, NULL as id_site , GPS_TEL_LNG, GPS_TEL_LAT, GPS_TEL_ALT, NULL as alt_precis, code_dept, nom_dept_min
    FROM nmp_odk.DEMO_OISEAUX12_REPEAT_LOC, nmp_odk.test_insee, nmp_odk.DEMO_OISEAUX12_CORE
        LEFT JOIN nmp_baznat.AUTEUR 
            ON nmp_baznat.AUTEUR.ID_AUTEUR = nmp_odk.DEMO_OISEAUX12_CORE.CONTRIB
    WHERE METHODE_LOC = '13'
    AND nmp_odk.GISWithin(GeomFromText(CONCAT('POINT(',`GPS_TEL_LNG`,' ',`GPS_TEL_LAT`,')')), SHAPE)
    AND nmp_odk.DEMO_OISEAUX12_CORE._URI = nmp_odk.DEMO_OISEAUX12_REPEAT_LOC._TOP_LEVEL_AURI

    UNION

    SELECT PROJET_ID, DATE_OBS, 'date précise', _SUBMISSION_DATE, METHODE_LOC, DATE_OBS, CONTRIB, nmp_odk.DEMO_OISEAUX12_CORE.ID_STRUCTURE,
            INSEE_zero, ID_SITE, LONG_LIEU_DIT, LAT_LIEU_DIT, Z_LIEU_DIT, ZPRECISION, CD_DEPARTEMENT, DEPARTEMENT
    FROM nmp_odk.DEMO_OISEAUX12_REPEAT_LOC
            LEFT JOIN nmp_odk.site_clone
                ON nmp_odk.site_clone.ID_SITE=nmp_odk.DEMO_OISEAUX12_REPEAT_LOC.SITE_OBS,
        nmp_odk.DEMO_OISEAUX12_CORE
            LEFT JOIN nmp_baznat.AUTEUR 
                ON nmp_baznat.AUTEUR.ID_AUTEUR = nmp_odk.DEMO_OISEAUX12_CORE.CONTRIB
    WHERE METHODE_LOC = '2'
    AND nmp_odk.DEMO_OISEAUX12_REPEAT_LOC.SITE_OBS is not NULL
    AND nmp_odk.DEMO_OISEAUX12_CORE._URI = nmp_odk.DEMO_OISEAUX12_REPEAT_LOC._TOP_LEVEL_AURI
    ;
END;
|
delimiter ;

And my DELETE trigger:

delimiter |
CREATE TRIGGER nmp_baznat_test.trigger_delete_ODK AFTER INSERT
ON nmp_baznat_test.OBSERVATION
FOR EACH ROW
BEGIN
DELETE FROM nmp_odk.DEMO_OISEAUX12_REPEAT_OBS;
DELETE FROM nmp_odk.DEMO_OISEAUX12_CORE ;
DELETE FROM nmp_odk.DEMO_OISEAUX12_REPEAT_LOC ;
END; |
delimiter ;

Answer 1

Eventually, I found a way to solve my problem. The main issue was the lack of standard id (serial) in the source table (where new lines are inserted). I thus added a serial key to this table so that I can use an additional condition "AND nmp_odk.DEMO_OISEAUX12_CORE.id IN (SELECT max(id) FROM nmp_odk.DEMO_OISEAUX12_CORE)" so that the trigger will insert only the last inserted lines.