Truncate table in mysql

Question

I want to make trigger and this is code

DELIMITER $$
CREATE TRIGGER tax_year_update AFTER UPDATE ON const_data
FOR EACH ROW 
BEGIN
    IF NEW.tax_year <> OLD.tax_year THEN
    TRUNCATE family_income;
    TRUNCATE student_income;
    END IF;
END$$
DELIMITER;

It causes this error

1422 - Explicit or implicit commit is not allowed in stored function or trigger.

Any suggestions why it doesn't work?

Answer 1

Truncate implicitly commits the transaction which is not allowed inside the trigger. Also TRUNCATE TABLE is DDL statament. You need to better use DELETE instead of TRUNCATE.

From the source:

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.

So you can try

DELETE FROM family_income;
DELETE FROM student_income;

instead of

TRUNCATE family_income;
TRUNCATE student_income;

Answer 2

While you truncating the table and if it records relates to other tables. It won't be truncating.

For that first you've to remove all the records from table in which you want to truncate.

Then go to options and set auto-increment with 1.

Enjoy :)