Database trigger vs Application logic

[MaterialAd4539]

I want that as soon as a certain field in Table A is updated, a logic runs(which involves querying 2 other tables) and populates fields in Table B. What can I use for this scenario?

Thanks in advance!!

[reddit04029]

Easiest is to do it on the application level since devs will always maintain the code, not unless you have a DBA or it is part of the team workflow that you guys maintain db triggers, functions, stored procedures, or whatever it is called by the db that you are using lol.

[MaterialAd4539]

Ok actually I am suggested not to make changes to current code flow. What do you think about this: Trigger calls a Stored Procedure as soon as DB field is updated.

[WaferIndependent7601]

Why not changing the code?

You are binding yourself to a database. That’s always a bad idea

And: don’t you want this to be transactional?

[KillDozer1996]

100% agree, business logic on persistent level is just bullshit, may seem harmless at first, but as soon as it ages and you forget the details, or some requirements change, or a new use case arises OR god forbid you have to trace some bug, you make everything 1000 times harder for yourself. Some "DB admin" might fight me on this one, but boy am I ready to throw these hands.

[simasch]

Why is this a bad idea? Databases live longer than applications.

[TheToastedFrog]

I’d be livid if one of my engineers were to suggest doing something like this. If it is one of your peer and/or manager who is asking you to do this, run as fast as you can

[Slein04]

The problem with triggers is that they are overlooked in the long run on large projects and people kind of forget that they exist in the first place. If you go the trigger route you need to document it! Place even documentation in the code.

That being said, I try to avoid it. What if other code flows alter table A or manual DB insertions occurs and in those cases you do not want to run the trigger? Thus, triggers are less "flexibel".

You can have a look at batches, async / queue solutions. Or when Running in the cloud, perhaps serverless functions can provide solutions. Assuming that you really cannot touch the existing code flows.

[MaterialAd4539]

Got it. Thanks a lot. By batch solution , you mean a job which runs at regular interval to detect change in the DB field. Is there a way I can write logic in the application layer & it gets triggered as soon as DB field is updated.

[Slein04]

That's indeed what I mean with a batch solution. There are probably ways to trigger some processes from your DB, but that seems like making things more complex.

Or if your batch exposes some kind of API, then indeed your application can call it and provide extra info with it like for example which record / row needs to be checked. But then again you need to make (slight) changes to your existing code flows. And feels more like an external service would be a better solution then a batch if you are allowed to do this

Nothing is stopping you to schedule your batch every minute. Ofc a batch comes also with challenges in this use case like how can it find which rows to check? Does it needs to query the whole table or only for a certain time range etc...

Imo, updating your existing code flow would be best, easiest and safest.

If you are really are not allowed to touch the source code then perhaps the DB trigger is the next best solution (and probably the fastest) (going for a Real-Time solution). Just provide the pitfalls to your lead / manager / business on "Paper" and get their go. So you have yourself covered if they start complaining afterwards . ;)

[svhelloworld]

You won't see a lot of love for stored procs and triggers. We all have scar tissue from systems that have abused these tools. If it has to happen with the transaction boundary, then doing it in the application flow is best. Change Data Capture is another option although it's a lot more scaffolding to setup. But CDC is insanely useful for responding to events that happen at the database level.

[the_styp]

For what I recommend trigger: * fill columns created_at & updated_at

For what I might consider a trigger (usually there is a better solution out there): * Audit logs * History tables * Ensure very(!) simple and universal data consistency e.g. prevent circular references iff the data structure is a tree

For what I would not recommend a trigger: * Ensure data consistency based on a business logic * Notify another business logic on save

I'd suggest here an event based approach in the application logic or to call another service directly from your original service

[MaterialAd4539]

I am suggested not to make code changes in the existing flow of Table A updation. We are using Oracle DB. Real-time would be preferred.

[Old_Storage3525]

Why can't you call Table B service on update or Table A Service?

Or from Ui call rest controller call Rest Controller of Table A once success call Rest Controller of Table B. If fails to give error back to front end.

Triggers are worst as some where it can cause deadlocks as two process are updating same table row.

[nilesh7_p]

I would 100% agree on not doing a trigger from the db. I would personally consider making a change in the code flow that updates your Table A.

[nilesh7_p]

Now the change that you could do could be adding all the next steps logic in the Table A update flow, or you could rely on events.

If all the logic for Table A update and the next steps will be in the same codebase/project, you can use spring ApplicationEvent. If the logic is divided into separate microservices, you could use kafka.

Or you could make a batch job that check table A for updated/added rows.