I've created a trigger (never done it before).
My goal is:
WHEN one or more rows are INSERTED into the table 'userlite'
IF there are rows with 'IdLite' as in the new row inserted in the table 'litedetails'
THEN add a row to the table 'informations' for each row counted.
The new rows data fields will be:
IdUser -> from new row inserted into the table 'userlite'
IdLite -> it's the same on new row inserted into the table 'userlite' and into rows selected from the table 'litedetails'
IdEvent -> from rows selected
I used the code below to create the trigger
DELIMITER $$
CREATE TRIGGER after_newuserlite
AFTER INSERT ON userlite
FOR EACH ROW
BEGIN
IF (
(
SELECT COUNT(*)
FROM litedetails
WHERE IdLite = NEW.IdLite
) > 0
) THEN
INSERT INTO informations (IdUser, IdLite, IdEvent)
SELECT NEW.IdUser AS IdUser, IdLite, IdEvent
FROM litedetails
WHERE IdLite = NEW.IdLite;
END IF;
END;
$$
I've tested it and all seems to work but I'm worried for my inexperience, so my questions are:
1) Is there anything that can cause my trigger to fail?
2) what happens if the trigger fails?
3) If the trigger fails the query who started the trigger will mantain its effects?
