MySQL Bugs: #59971: while making a trigger, i get errorr 1422 which says explicit or implicit commit

Bug #59971	while making a trigger, i get errorr 1422 which says explicit or implicit commit
Submitted:	6 Feb 2011 4:48	Modified:	6 Feb 2011 9:17
Reporter:	Malvinder Dhawan	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Compiling	Severity:	S2 (Serious)
Version:	5.0.45	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I am making a trigger which has no syntax error

I am also using delimiter

this is the error I get when I compile the whole code
error number 1422
Explicit or implicit commit cannot be done in a function or procedure or trigger

How to repeat:
please find a solution for it

error 1422 explicit or implicit commit

Attachment: trigger.sql (application/octet-stream, text), 740 bytes.

You can not use TRUNCATE in trigger. Read http://dev.mysql.com/doc/refman/5.0/en/truncate-table.html:

"As of MySQL 5.0.8, truncate operations cause an implicit commit."

and http://dev.mysql.com/doc/refman/5.0/en/stored-program-restrictions.html:

"For stored functions (but not stored procedures), the following additional statements or operations are not permitted:

      Statements that perform explicit or implicit commit or rollback."

Same restriction applies to triggers, as you can see from the error message.

So there is a detail to document here, I think.

"For stored functions (but not stored procedures), the following
additional statements or operations are not permitted: ...."

>>

"For stored functions (but not stored procedures) and triggers, the following
additional statements or operations are not permitted: ...."

and EVENTS btw??

That same page says at the beginning:

"The restrictions for stored functions also apply to triggers."

As for EVENTs, 5.1 manual has a special subsection, http://dev.mysql.com/doc/refman/5.1/en/stored-program-restrictions.html#stored-routines-ev...

This is 100% NOT a bug in anything related to MySQL 5.0.