何时使用存储过程和触发器与应用层

Question

I have a dilemma, which I hope you will have some expert opinions on.

I have a table called CARDS with a column STATUS. If a record's status changes from 'download' to 'publish', I have to insert the record reference into another table called CARD_ASSIGNMENTS. Additionally, the record needs to be added into CARD_ASSIGNMENTS as many times as there are active records in SCANNERS.

In other words, if there are two active scanners, I will end up with two records in CARD_ASSIGNMENTS as below:

ID   CARD_ID   SCANNER_ID   STATUS_ID
1    1         1            4
2    1         2            4

My dilemma is that I'm not quite sure what would be the most efficient way to execute the above. I've considered the following options:

1. From PHP - Do one UPDATE query and then the INSERT queries.
2. Create a stored procedure, which will take care of updating the CARDS record and adding records into the CARD_ASSIGNMENTS. Then, just call that stored procedure from PHP.
3. Create an ON UPDATE trigger for the CARDS table which will take care of processing INSERTS into the CARD_ASSIGNMENTS table.

PS. A simplified version of my database is available on MySQL Fiddle

Thanks,
Kate

Answer 1

Interesting question.

I'm going to give you clues about how to approach the problem.

So, you have to start by defining precisely three things:

1. the expected functionality
2. the access policy to the functionality
3. the technical upgrade policy

Here I'll detail these points.

So, the first point is that you have to define your functionality. By doing so, you will be able to tell whether adding a card implies always, in all the possible paradigms (sorry for the pedantic word I can't find a more proper one) of your information system, that this card MUST exist in the other table according to the specifications you provided. This 1-1 functional link must be said TRUE or FALSE. This is really important. Said with other words, if there's at least one possibility that one day you don't want to copy that record to the other table, it means the trigger is a wrong solution, or at least it should be thought with an emergency mode (for example a variable inside that allows it to not get executed in some conditions) setup on.

Then comes the second point, about the access policy. You have to know whether the allowed accessing systems will do so by using your application layer or if they could develop their own (SAAS style). If so, your php layer will be useless and the stored procedure is an excellent option, since every single technical and business layer will go trough it yes or yes.

The last thing to know is whether you're possibly going to upgrade your php layer one day. In most of the cases the answer is yes. If so, you might have to modify the part containing this sql logic you're talking about. Then, having everything into a stored procedure vs storing it hardcoded into the php will definitely save you time, and improve stability.

---

Left brain right brain, I'm going to tell you my personal opinion afterall. I really love going with stored procedures but not using any triggers. If the environment allows it, I would go for an underlying batch, calling a set of defined stored procedures, concentrating the activity outside of the online scope.

The advantages are the following:

• none or less risks of interruption of the online workflow since you reduce the number of operations
• different schedule to alliviate the database load
• more secure policy since executing the stored procedure requires only one grant, while using the same sql with php would require insert/update grants
• better logging quality: you can have a log per job
• better emergency response: when a job fails (if well thought) you can restart it, and that's it.

Long post, but that was interesting and I really wanted to share these ideas.

Cheers!