1

There are many questions and responses on database triggers, however I have not found any that touch on network performance specifically, in the matter of benefits (or otherwise) of triggers.

I have this case:

Table A has rows that are data heavy (1-2 KB of JSON data). When one of the fields (status) is updated, the entire record has to be moved to another table (archive) and deleted from Table A. More than one row can be updated at a time.

Without getting into the merits of the design of the archive table, I have implemented this in a trigger ('after UPDATE' on Table A, copy record and the delete) - my logic being that there will not be any need for the data to be sent to the application (it is an app, with the dB on the cloud) and back again.

I believe this will improve end user experience, reduce network traffic, and de-risk issues with poor networks.

Are triggers still an 'avoid - these are bad'?

4
  • 1
    Moving data between tables doesn't require sending it to the client and back. A query like insert into A select * from B requires minimal network traffic but may copy GB of data. Commented Dec 6, 2022 at 23:02
  • You find nothing about "network performance" with triggers, because you are mixing up stored procedures with triggers. Of course, these two things are connected, since triggers require stored procedures, but but reducing network performance isn't a cause of using a trigger, it is cause of using a stored procedure.
    – Doc Brown
    Commented Dec 7, 2022 at 6:37
  • ... so in your use case above, first thing you need to clarify is if the JSON record needs to be moved immediately, inside the very same transaction, to the new table when the status field changes, and if you expect different applications to change the status field. If yes, using a trigger would be the logical choice, regardless if there is just one number to move or 2KB of data.
    – Doc Brown
    Commented Dec 7, 2022 at 6:41
  • ... so I recommend you google terms like "stored procedures vs client code", that gives a lot of results.
    – Doc Brown
    Commented Dec 7, 2022 at 6:47

3 Answers 3

3

The problem with triggers is that they are so often abused, and usually wrongly-written even when used for a good cause.

The main legitimate use for triggers is to enforce the basic integrity of the data, but which can't be expressed with standard SQL constraints. I'm talking about fundamental constraints that could never be violated even temporarily, like that a journal entry must always balance to zero.

Another justifiable practical use is as an interception point for an old application whose workings can no longer be safely and correctly altered. Like opioids for the terminally ill, these triggers hurry death, but may be administered to ease what time remains.

Bolt-on audit/history tables are a simple and common pattern which is arguably amongst the more legitimate uses of triggers. It is not always a "fundamental" constraint that changes be audited, but it's often quite up there.

The only thing to bear in mind is that developers and administrators often don't realise that triggers exist.

If the triggers are not fundamental - if they cover only usual operation but might be disapplied in the case of high-level intervention or bulk update - then developers and administrators won't realise they exist until their ignorance has already caused damage or dysfunction.

I've seen even the creators of (completely inappropriate) triggers get tangled in their own web this way.

Ignorance is especially likely to reign if triggers only cover some tables, or if the functionality of triggers (all taken as a whole) is arbitrary, varying, or complicated.

Whenever triggers should not fire 100% of the time, or if they do anything but the simplest and most predictable work, then stored procedures should be used in preference to triggers, and the front-end application reworked to use these stored procedures.

3

In terms of network performance, using a trigger to move data from one table to another and then delete it from the original table can potentially reduce network traffic in the short term since the data is transferred and deleted within the database itself instead of being sent over the network to the application and then back to the database. However, it's essential to consider the long-term effects of using a trigger in this way.

For example, if your trigger runs on a large table with a high volume of updates, it could potentially cause performance issues for your database as it tries to keep up with the updates and run the trigger for each one. This could lead to slower query performance and cause your database to crash or become unresponsive. In this case, the trigger might increase network traffic as users try to access the database but cannot because it is busy running the trigger.

Overall, it's best to carefully consider the potential drawbacks of using triggers and weigh them against the benefits before deciding whether or not to use them in your database. In some cases, triggers can be helpful tools, but they should be used cautiously.

2

Indeed, the benefit of the trigger is the processing on the db server: you avoid unnecessary trafic to the client, and additional parsing and execution overhead for commands comming back, not to speak of transactional performance (reduced locking time, etc).

The downside is that you often are locked in proprietary trigger definition, and more problematic, in a data oriented architecture with loss of encapsulation/reponsibility as part of the processing is done on client and some is taken over by the sever and after a while it becomes unclear in the maintenance cycle, who does what. Ownership of entities is diluted between db and individual applications.

The alternative approach is to have services in control of entities. Trigger activity is then moved to the corresponding service. Since the service is in general operated in the same datacenter, with high )internal) bandwith, the network effects of not using triggers would be almost unnoticed by the user. For very very high volumes, you’d then have to decide whether to optimize the service with the help of a trigger, or to scale-up, partition, distribute the database (which is not a problem with a service approach but might be difficult to achieve with triggers that assume that all data is local to the db server)

1
  • 1
    @GregBurghardt sorry: typo
    – Christophe
    Commented Dec 6, 2022 at 22:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.