SHOW CREATE TRIGGER - Nope
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.
If you found this post helpful, please consider sharing to your network. I'm also available to help you be successful with your distributed systems! Please reach out if you're interested in working with me, and I'll be happy to schedule a free one-hour consultation.