Not allowed to return a result set from a trigger

Question

I have problem:

I created procedure :

CREATE PROCEDURE `tran_sp` ()
BEGIN
  SELECT 'procedure runned!';
END
$ 

It's works !

mysql> CALL tran_sp()$
+-------------------+
| procedure runned! |
+-------------------+
| procedure runned! |
+-------------------+
1 row in set (0.00 sec)

Then i created trigger :

mysql> CREATE TRIGGER my_trigger1
    ->     AFTER INSERT ON users
    ->     FOR EACH ROW
    -> BEGIN
    ->          CALL tran_sp();
    -> END$


mysql> INSERT INTO users VALUES(166,156)$

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

Help me please.

Answer 1

The exception is I think clear enough.

You can perform additional operations inside a trigger (call a SP, perform insert / update / delete operations, ...) but all of those aren't allowed to return any result.

This means, a SP with a simple select-statement inside isn't allowed. If instead you would use this select statement within a loop for instance in order to perform updates or similar, this would be allowed, as you wouldn't return anything.

The reason is, that an insert/update/delete statement can't return anything, it can't return the result set of your stored procedure and therefor you shouldn't try to return one inside the trigger.