bluish bluish - 11 months ago 50
MySQL Question

Add a comment (description) to a trigger

If I create a trigger with

syntax in MySQL, how can I attach a comment to it, which describes it? I mean in the declaration or after it's the same to me.

I can't figure it out. With tables you add
COMMENT = 'Wonderful table with users inside';
at the end of the declaration. But how do you add comments to a trigger?

Answer Source

You cannot attach a comment to a trigger.

You can however put comments in the trigger body.

If you type the in body comments like /** comment **/
You can extract these comments with the following query:

  SUBSTRING(b.body, b.start, (b.eind - b.start)) as comment 
    ,locate('/**',a.body) as start
    ,locate('**/',a.body) as eind
  FROM (
    SELECT t.ACTION_STATEMENT as body FROM information_schema.triggers t 
    WHERE t.TRIGGER_NAME like %aname% 
  ) a
) b