My requirement is to move EMR data for a patient to a set of history tables when the patient has been discharged. I proposed to use an after trigger on the table housing the discharge flag, checking new\old values on the flag to ensure I only take action when the flag is initially set to discharged. The Sr. app developer writing the PHP side says that doing this in PHP is at least 4 times faster than the trigger. To me this doesn't make any sense but my background is in SQL Server, not MySQL so I am looking to the community for help.
Additional info:
Table housing the discharge flag is heavily used even after the patient has been discharged. There are currently 7 narrow tables (total of about 70 columns) that would have data inserted by trigger.
Best Answer
I wonder where "at least 4 times faster" came from? There are too many variables at play to even come up with numbers like that, and in any event, the performance difference (however slight) should be in the other direction.
The presence of a trigger on a table does come with a performance penalty, but if your trigger is intelligently-written, the word "negligible" should apply. A well-behaved trigger does only what it needs to do and then gets out of the way, and in the case of triggers that test multiple conditions, the tests are nested so that as few logical comparisons are done as possible for each run of the trigger.
Some developers seem to resist triggers on the principle that they're "black magic" -- "Hey, something changed in the database, and the application didn't change it. That's bad." Granted, the presence of triggers is something developers and DBAs alike have to keep in mind when troubleshooting, and that's especially true if they are uncommon in your environment. If the structure of one of the tables modified by the triggers were to change -- say, a renamed column -- then updates to the discharge flag suddenly start throwing errors about columns that weren't even referenced in the update query, someone who doesn't have the possibility of "there might be triggers involved" at least in the back of their minds may be completely flummoxed. I've been on that side of it, too, and I was the one who originally wrote -- then forgot about-- the trigger in question. That's one of those lessons you learn the hard way, hopefully just once.
The issue you bring up is, I would say, as much as matter of principle as performance: as one of the answers to this question points out, the database should be in charge of looking after the database... not the application. The application shouldn't be in charge of referential integrity, so why should it be in charge of operations which -- like this one, from what I can tell -- should be atomic?
If, by definition, when the discharge flag is set, the EMR data should thenceforth be in the history tables, then I would argue on principle that the setting of the flag should be what puts the data there. That requires either a trigger... or a call to a stored stored procedure that both sets the discharge flag and migrates the records before returning.
I'm imagining that no matter how you end up migrating the records, the actual queries executed to do the migrating would be the same... so in truth it's hard to imagine the difference in performance being substantial, since the execution time for the queries that move the records is not going to be different, whether the queries are run from a trigger, run from a stored procedure, or run separately from the application... so, almost comically, the most significant factor I'd expect to find would likely be small: the round trip time between the system running PHP and the system running MySQL, multiplied by the number of separate queries that need to run, will likely be the determining factor in just how much faster a trigger (or stored procedure)-based implementation would be... and on a LAN with sub-millisecond RTT... not much.