Hi folks,
Here's my situation: I have a MySQL database in which I'd like to use triggers to automatically manage the updating of date creation and date modified fields in a few of my tables. Later, I'd like to expand them into logging data changes, but that's neither here nor there at the moment.
The triggers work fine and update the fields as intended. The problem is that the application account is now basically the only account that can affect these tables, actions from other MySQL user accounts (such as mine) fail because the definer is the application account.
I can't seem to find a way to have the trigger fire regardless of who executes a command on the tables and it's quite frustrating. Is there a way to either open up a trigger to fire regardless of user or allow multiple users to fire the trigger?
We're currently running MySQL 5.0.18 - changing this is very unlikely as the folks here in charge of infrastructure are rather resistant to fixing what (in their minds at least) isn't broken.