not allowed to return a resultset from a trigger mysql

database, mysql, mysql-error, sql, triggers

Solution

You cannot execute SELECT statements from trigger. If you want to set variables, then use SELECT INTO statement, e.g. -

DECLARE supplierid_ INT(11);
DECLARE customerid_ INT(11);

SELECT
  supplierid, customerid
INTO
  supplierid_, customerid_
FROM
  iv_documents
WHERE
  id = OLD.note_documentid;

IF supplierid_ = OLD.note_companyid THEN
...

Also, rename variables, they have to differ from from field names.

Problem

``` delimiter $$ CREATE TRIGGER REDUCE_NOTE_COUNT AFTER DELETE ON iv_notes FOR EACH ROW BEGIN DECLARE supplierid int(11); DECLARE customerid int(11); SELECT supplierid ,customerid FROM iv_documents WHERE id=OLD.note_documentid; SET supplierid=supplierid; SET customerid=customerid; IF supplierid=OLD.note_companyid THEN update iv_documents set supplier_notes=supplier_notes-1 where id=OLD.note_documentid and supplier_notes>0; END IF; IF customerid=OLD.note_companyid THEN update iv_documents set customer_notes=customer_notes-1 where id=OLD.note_documentid and customer_notes>0 ; END IF; END$$ delimiter ; ```

Original source