How do I diagnose steps in a trigger on MySQL

Question

I have two tables:

• source table
• result table

I have an after update trigger on my source table which updates some records in result table. the problem is, my trigger is not updating result table and I would like to diagnose my trigger execution.

I tried putting select statements to see variable values but selects are not allowed in a trigger. I would like to use something similar to PRINT in Microsoft SQL Management Studio that would output some values in GUI but this command doesn't seem to exist on MySQL or Toad tool that I'm using.

How am I suppose to diagnose my trigger then? How do you do it?

Answer 1

Use a log table.

CREATE TABLE log (t datetime, comment varchar(255));

In your trigger you can insert the log.

INSERT INTO log 
SELECT now(), concat('debug comment ', @your_variable);