CODEWITHSUNDEEP
mysqltriggersmysql-5.5
user387049
user387049

Reputation: 6877

MySQL Trigger returns multiple rows

I have a MySQL 5.5 Trigger which is failing due to multiple results being returned. The problem is, I'm attempting to return one result at a time by using a unique field 'id' field.

CREATE TRIGGER insert_com_app_title AFTER INSERT
ON com_apps FOR EACH ROW 
INSERT IGNORE INTO com_myisam_app_titles(id_company, title) VALUES((SELECT a.id_company, b.title FROM com_apps a JOIN epf_application b ON a.application_id = b.application_id WHERE NEW.id = a.id));

The (relevant) table structure is as follows:

**com_apps**
id, id_company, application_id

**com_myisam_app_titles**
id_company, title

**epf_application**
application_id, title

I'm assuming something is wrong with New.ID, however even if the value is wrong the id field of com_apps is a PRIMARY auto-incremented key, so even in that case I would return one incorrect result.

Upvotes: 0

Views: 930

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270573

You shouldn't nest select in a values statement. SQL offers much better syntax:

INSERT IGNORE INTO com_myisam_app_titles(id_company, title) 
    SELECT a.id_company, b.title
    FROM com_apps a JOIN
         epf_application b
         ON a.application_id = b.application_id
    WHERE NEW.id = a.id;

The problem is that the underlying query is returning more than one row and the values statement cannot handle that. The above fixes that problem.

If you only want one row, add a limit 1 to the query:

INSERT IGNORE INTO com_myisam_app_titles(id_company, title) 
    SELECT a.id_company, b.title
    FROM com_apps a JOIN
         epf_application b
         ON a.application_id = b.application_id
    WHERE NEW.id = a.id
    LIMIT 1;

Upvotes: 1

Related Questions