Trigger to fire on any insert/update in any table
Posted by: Lajos Arpad
Date: September 26, 2013 07:05AM
I am working on a module, which generates constraints for FDs, CFDs and ARs and I would like to enforce them.
If any of the constraints are not respected by an insert or an update, then I would like to raise an error.
To achieve my goal I am thinking to make the following steps:
1. To find the FDs, CFDs and ARs and to store them.
2. A stored function is generated by the application which checks the columns of the new record whether they break any rule. I name this stored function "isBreakingConstraints". Basically it should get the name of the table where insertion or update occurred and should return true if the insertion/modification breaks any rule and false otherwise.
3. I would like to create a trigger which fires on any insert/update and runs the stored function. If the function returns a trueish value, then I would like to raise a MySQL error.
I have several questions to ask regarding this issue. The most important one is:
Can I create a trigger which fires on any insert into any table? Can I create a trigger which fires on any update on any table? Is this possible? If so, how? If not, will this be possible in the near future?
If I am successful in doing this, then I am puzzled about how can I get the name of the table where the insert/update event was triggered.
I could generate a trigger on each table, but that would not be nice, as new tables can be created anytime and I would need a module which repeatedly checks whether new tables were created. Also, I could call the stored function from my application, but this would give only the illusion of constraints, as it would not guarantee that there is no other application, or if there is one, then it also called the stored function, not to mention the case when users run insert/update commands directly from MySQL client.