The content here is not new. I have written about same before many years ago (I think in our old Blog system that is now offline for 4+ years). But I feel like to restate my point of view after listening to the latest OurSQL Episode. I was a little excited if Sheeri and Gerry would express similar concerns in their podcast, but they did not.
This is my concern: You may build an application on top of a database schema using TRIGGERS. And it may work perfectly as long as the database is only accessed from the application. However sooner or later it will likely happen that some maintenance or other database manipulations is required that was not foreseen when the application was written, and thus the application cannot handle it and you will need to connect with another client. It may be a problem because once you start doing manipulations from outside the application you may fire TRIGGERS that you don’t want to fire in the context (or TRIGGERS you forgot about or overlooked because you did not code the application yourself). And this could easily result in irreversible harm to data.
I remember that at least 3 different ways to implement support for this have been proposed over time – mostly in the MySQL bugs system as ‘feature requests’. Actually it took only very short time after TRIGGERS were introduced in MySQL before request for various ways to bypass TRIGGERS occured. And none of it has yet been considered in MySQL development. The list (possibly incomplete) is:
1) A SESSION variable turning off TRIGGERS for the session (“SET @@session.triggers = OFF”)
2) Include or Exclude users in the TRIGGER definition so that a TRIGGER is fired or not fired depending on what user manipulates the table ON which the TRIGGER is DEFINED. (“CREATE TRIGGER .. INCLUDE|EXCLUDE [email protected] [,[email protected]]…”)
3) a ‘NOTRIGGER’ keyword to be used in INSERT, UPDATE or DELETE statements.
Frankly I don’t know if other RDBMS’s have such options or even if it would be compliant with SQL standards and various auditing requirements or not. But I would find it highly useful.
As long as some option like this is not there, I am a little scared of using TRIGGERS – or not trigger-happy, so to say. And if I should use them I would at least consider to wrap all statements between BEGIN and END in a condition testing for CURRENT_USER being the application user the database schema was designed for and not any other user. However if you want to use this ‘hack’ and if you also manipulate data from inside Stored Procedures be aware of how the SQL_SECURITY setting for the procedure affects CURRENT_USER returned from inside a Stored Procedure – refer docs on this.