Add a comment (description) to a trigger

Question

If I create a trigger with CREATE TRIGGER 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 1

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:

SELECT
  SUBSTRING(b.body, b.start, (b.eind - b.start)) as comment 
FROM (
  SELECT
    a.body 
    ,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

Answer 2

As a workaround, you may write a commented text inside a trigger's body, e.g. -

CREATE TRIGGER trigger1
AFTER INSERT
ON table1
FOR EACH ROW
BEGIN
  -- 'Wonderful trigger with insert inside';
  INSERT INTO table2 VALUES(NEW.id);
END