MySQL trigger error: Explicit or implicit commit is not allowed in stored function or trigger.

I haven’t seen this documented in MySQL docs, so I’ll share this little hidden nuissance. When compiling a trigger, MySQL throws the following error: Explicit or implicit commit is not allowed in stored function or trigger.

What does it mean?
The code inside the trigger is doing a commit. Looking at the code for the trigger alone is not enough, you must check all procedure/function calls, because the code inside any calls could really be (part of) the problem.

Problem?
The problem is usually we look in our code and find that there are no “explicit” commits. The problem is that there is a implicit commit happening somewhere and it is hard to pinpoint where if you didn’t know this one hidden fact:

Depending on version and storage engine, TRUNCATE can cause the table to be dropped and recreated. This provides a much more efficient way of deleting all rows from a table, but it does perform an implicit COMMIT. You might want to use DELETE instead of TRUNCATE.

Moral
DELETE FROM tablename rather than TRUNCATE TABLE tablename.

4 thoughts on “MySQL trigger error: Explicit or implicit commit is not allowed in stored function or trigger.

  1. DROP TRIGGER IF EXISTS demo.cacth_insert//
    CREATE TRIGGER demo.catch_insert BEFORE INSERT ON demo.users;
    FOR EACH ROW
    BEGIN
    INSERT INTO demo.trigger(‘username’,’name’) VALUES (‘username’,’name’);
    END//

  2. hello, i am creating a trigger and got the error as u talking about above, but i cant find out anything relative to ‘commit’, would u help me:)
    code goes like this:

    delimiter //
    create trigger insert_only_one
    after insert on sc
    for each row
    BEGIN
    set @sum=(select count(*) from sc where sno=new.sno);
    IF @sum > 1 then
    ROLLBACK;
    END IF;
    END

Leave a Comment