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.
Thanks, this worked!
This was exactly what I needed to solve my problem. Thanks for putting it out there.
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//