0

I recently came across a set of possibilities for creating rows in a table in my database. the scenario is that I am trying to populate a notifications table by different types of notifications data based on different tables.

Adding to the notifications table is done instantly after the adding of rows in other tables (like adding in invoices table).

Since the code adding to the other tables is on a higher level (php) the question is : should I add new rows to notification table with a php sql query or should I implement a trigger that would do that automatically ?

Kaki Master Of Time
  • 139
  • 5
  • Not enough context for me to give a good answer, but some advice I would give is to keep business logic out of the database, therefore, I would personally not use a trigger for something like this. – Brandon Jan 07 '19 at 14:41

1 Answers1

1

The benefit and problem with triggers are two sides of one coin. Once defined they will always be executed. This means future application code will always have notifications baked in and the programmer, no matter how new, will not forget to add it. Conversely some future requirement that does not need notification cannot avoid it. You know your application best. Which of these seems most problematic or beneficial in the context of your team and business?

Supposing the notification uses only values from the data row and no further information from the application, implementing as a trigger will save a network round-trip from application to DB server.

In my experience triggers are often forgotten about during debugging. People only look at the application code. Triggers cop the blame for much "magic" that happens out of sight.

A happy compromise is stored procedures. They can perform several actions from a single network message and all actions are in plain sight. The code is easy to put under source control, just like all other application logic. Should future requirements make notifications conditional that can be added to the SP and still have visibility.

Michael Green
  • 903
  • 5
  • 16