sqlmysqldatabasetriggersmysql-error

not allowed to return a resultset from a trigger mysql


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 ;

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.