Trigger :Do not execute INSERT INTO when SELECT returns no result

Question

I use a trigger in Mysql that inserts newly inserted rows into multiple tables. The trigger is composed of several SELECTs. I noticed that the trigger works only when each SELECT returns a result. I understood that MySQL does not like to INSERT nothing... However, often, the last recorded row does not contain the data to return a result for every single SELECT of the trigger. In this case, the trigger fails to run.
For instance, when I modidy the trigger with only the SELECTs that return something, then all the INSERTs work fine.
I would like to find a way to tell the trigger to NOT execute the INSERT INTO of the SELECT that returns no result. How could I do?

delimiter |
CREATE TRIGGER trigger_name AFTER INSERT
ON table1
FOR EACH ROW
BEGIN
INSERT INTO table3 (field3a, field3b)
    SELECT field1A, field1B
    FROM table1
    WHERE table1.id = table2.id
    AND table1.id IN (SELECT max(id) FROM table1);
INSERT INTO table4 (field4a, field4b)
    SELECT field2A, field2B   -- imagine this SELECT returns no row
    FROM table2
    WHERE table2.id = table1.id
    AND table1.id IN (SELECT max(id) FROM table1);
END;
|
delimiter ;

Thank you!

score 1 · Answer 1 · answered Aug 02 '16 at 15:11

This is as easy as pie. The answer is right there, before your eyes, in the INSERT statement. You don't need to fuss with triggers at all.

Enhance the INSERT statement to include an EXISTS condition. If a certain query, SELECT FOO FROM BAR WHERE AWK, is the trigger condition--execute the INSERT if it returns something, but defray the INSERT if it doesn't--then just expand your INSERT statement from this:

INSERT INTO TABLE( . . . )
    SELECT desiredValues
    FROM necessaryPlaces
    WHERE applicableConditions

to this:

INSERT INTO TABLE( . . . )
    SELECT desiredValues
    FROM necessaryPlaces
    WHERE applicableConditions
    AND EXISTS     (SELECT *
        FROM BAR
        WHERE AWK
        )

If no rows are returned, the EXISTS clause evaluates to FALSE, and the "trigger" doesn't occur.

Notice that I dropped the SELECT FOO and replaced it with SELECT *; since all you're interested in is whether any rows were retrieved, there's no reason to do the extra work of chopping up the returned rows to yield just this column and that one: let the query optimizer determine what to do, and how to do it, by asserting just the very general SELECT *.

Thanks for your help! However, I'm note sure to get what should be the EXISTS query in my case. The trigger condition would be every single INSERT ON table1. How could I turn it into a SELECT query? I don't get it, sorry. — RemiC, Aug 03 '16 at 09:21

Trigger :Do not execute INSERT INTO when SELECT returns no result

1 Answers1