As of MySQL 5.0 there is no SHOW CREATE TRIGGER which is pretty annoying. I don’t like using “show triggers like “, so I figured I’d write a wrapper around mysqldump. For the record, this was a huge pain the ass. I did this on Fedora, GNU sed version 4.1.5. You need to have your password stored in ~./my.cnf for this to work, I believe. I haven’t tried it without it.
mysqldump -dnt database | sed "/\/\*\!4.*\*\/;*/d" | sed "s_/\*\!5[0-9]*__g" | sed "s_DEFINER=.*\*/__g" | sed "s/\*\/;*//g" | sed "/SET/d" | sed "s/^[ \t]*//" | sed "s/\ \ */ /g" | sed "/--/d"
You will get a dump of ALL the triggers in your database, using this. However, since that’s what I need on this particular example, it’s what I’m sharing. I’m sure I could have cleaned up the code a little and used fewer calls to sed, but frankly I don’t mind, since it works fine.
I hope this helps someone.