Not allowed to return result set from mysql trigger

Question

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;

Answer 1

You cannot execute SELECT statements from a trigger. If you want to set variables, use SELECT INTO , for example. -

 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 must be different from field names.