not allowed to return a resultset from a trigger mysql

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 ;

Oidium

new user tip: please, people, ASK the question, it's not obvious what you're asking for. Include version you use, and possibly an error message :) – Centenary

Answer 1

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.

Dammar

Accept the answer if it is OK. – Dammar

My problem was also that I had DECLARE before each SELECT statement. Apparently for some magical reason of MySQL wisdom DECLARE has to be up on top and you can't declare variables in the middle of trigger. – Thirst

@SergiyKolodyazhnyy the requirement to have DECLARE statements first in a procedural block is not magical, see dev.mysql.com/doc/refman/8.0/en/declare.html ""DECLARE is permitted only inside a BEGIN ... END compound statement and must be at its start, before any other statements. Declarations must follow a certain order. Cursor declarations must appear before handler declarations. Variable and condition declarations must appear before cursor or handler declarations."" – Iritis

Answer 2

In a trigger and function, SELECT INTO is allowed to use but SELECT without INTO is not allowed to use because there are the errors below. *In a procedure and event, SELECT without INTO is allowed to use:

ERROR 1415 (0A000): Not allowed to return a result set from a trigger

ERROR 1415 (0A000): Not allowed to return a result set from a function

Swagsman