DROP TRIGGER normaliser;

DELIMITER |

CREATE TRIGGER normaliser AFTER INSERT ON logs 
FOR EACH ROW BEGIN

    insert into messages ( message ) 
           select distinct logs.message from logs
                  left join messages on logs.message = messages.message
                  where messages.id is NULL;

    insert into programs ( name, version )
           select distinct progname, progrev from logs 
                  left join programs on progname = name and
                  ( progrev = version or (progrev is NULL and version is NULL) )
                  where programs.id is NULL;

    insert into validation ( message_id, program_id )
           ( select distinct messages.id, programs.id from messages,programs
             where NEW.message = messages.message and 
                   NEW.progname = name and
                   ( NEW.progrev = version or ( NEW.progrev is NULL and version is NULL)) );
END |

DELIMITER ;
