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.
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.