Search code examples
mysqltriggersprocedures

mysql: use a procedure inside a trigger

I need to call a procedure that modifies root password inside a trigger. This is the procedure

delimiter //
create procedure foobar ()
begin SET PASSWORD FOR 'root'@'localhost' = 'foobar';
end//
delimiter ;

and this is the trigger

delimiter //
create trigger rootpass
after delete on map
for each row
begin
call foobar;
end //
delimiter ;

It seems the syntax is correct but when I launch a delete I get this error

ERROR 1445 (HY000): Not allowed to set autocommit from a stored function or trigger

So I think I'm trying to do a forbidden thing, is there any way this could work in mysql? I know the whole thing sounds weird/horrible but this is the way for my application to understand that something has been deleted.

Solution

  • When executing a statement would cause an implicit commit of the current transaction, that statement is not permitted within a trigger or stored function.

    This includes a statement in a stored procedure that is invoked by a trigger or function -- there is no layer of abstraction here that makes the invalid action any more valid.