When to use stored procedures and triggers against the applicative layer

Question

I have a dilemma, and I hope that you will have some expert opinions.

I have a CARDS table with a STATUS column. If the status of the record changes from “download” to “publication”, I need to insert a link to the record in another table CARD_ASSIGNMENTS. In addition, an entry must be added to CARD_ASSIGNMENTS as many times as there are active entries in the SCANNER.

In other words, if there are two active scanners, I end up with two entries in CARD_ASSIGNMENTS, as shown 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 effective way to accomplish the above. I considered the following options:

• From PHP - make one UPDATE query and then INSERT queries.
• Create a stored procedure that takes care of updating the CARDS record and adding records to CARD_ASSIGNMENTS. Then just call this stored procedure from PHP.
• Create an ON UPDATE trigger for the CARDS table that will handle the INSERTS in the CARD_ASSIGNMENTS table.

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

Thanks,
Kate

Answer 1

Interest Ask.

I will tell you how to approach the problem.

So, you should start by defining three things:

• expected functionality
• feature access policy
• technical update policy

Here I will talk about these points.

So, the first thing you should define your functionality. By doing so, you will be able to find out whether always in all possible paradigms (sorry for the pedantic word that I can’t find more suitable) of your information system, it should always be that this card MUST exist in another table in according to the specifications you specified. This function reference 1-1 must be specified as TRUE or FALSE. This is really important. In other words, if there is at least one possibility that one day you do not want to copy this record to another table, this means that the trigger is the wrong decision or at least you should consider emergency mode (for an example, a variable inside , which allows it not to be performed in some conditions).

Then comes the second point, about access policy. You need to know if authorized access systems will do this using your application layer or if they can develop their own (SAAS style). If so, your php layer will be useless, and the stored procedure will be a great option, as each technical and business layer will go through it yes or yes.

The last thing you need to know is that you are probably going to update your php layer one day. In most cases, the answer is yes. If so, you may need to modify the part containing this sql logic that you are talking about. Then, having everything in the stored procedure and keeping it hardcoded in php, you will save time and improve stability.

---

The left brain in the right brain, I will tell you my personal opinion. I really enjoy walking with stored procedures, but not using triggers. If the environment allows this, I would go to the base batch, naming a set of specific stored procedures, concentrating activities outside the online area.

The benefits are as follows:

• no less or less risk of interrupting the online process, as you reduce the number of operations
• another schedule to get around database loading
• more secure policy, since only one grant is required to execute the stored procedure, while using the same sql with php will require attachments / updates
• Better quality of journaling: you may have a journal on a task
• better to respond to emergencies: when a malfunction (if it is well thought out), you can restart it and that it.

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

Hooray!

Answer 2

I would use triggers. Some developers say that if you have too many triggers and stored procedures, the database lives its own life, which means that you never know what will happen with the insert, update, etc. But, in my opinion, triggers can help you maintain the integrity of the database, so even if someone inserts data directly from some administration tool, the integrity is still maintained because all the necessary commands are executed. If you choose stored procedures, you still have to know that you need to call this procedure to insert any new data.