[ranguna]

> If I have to invent weirdo gymnastics in my database to enforce something, it's probably best to do in code.

What if you had to do weird gynmastcis in the code instead ?

Setting up a trigger that checks if the row being inserted has a foreign key that points to a row of another table that has a column with a value that you were expecting.

Easily done with a trigger that requires no round trips, but many see it as an overhead because you now need to maintain a function with DB migrations.

If you do it in the code, you need to make sure you do a second call to your DB to do this validation everyone you insert in a specific table, something that can be missed when witting code.

[gwbas1c]

I would argue that's a good use of the trigger!

The complication is error reporting. Will your application interpret the error that comes from the database correctly? Will your UI (or API) return a useful error?

[ranguna]

Yep agreed, so sometimes it's better to write business logic on the DB side because other wise you'd have to do code gymnastics and multiple round trips in your application code.

As for error handling, you can raise an error with a specific format in your plsql and have your application interpret it like any other DB error, the the application does whatever it wants with it, including sending it to the FE if needed.

Ex :

FE clicks a button to append a payment to an order.

BE creates the payment entity assigned to a wallet of a specific user and currency. BE attempts to assign the new payment to the existing order (the order was created for a different currency).

DB runs a trigger on payment creation that checks that the associated wallet's currency is the same as the order's currency, if it fails, the DB raises "payment_wallet_currency_mismatch"

BE catches the error and matches the error code, throwing back an error to the FE saying that the currency in the payment is invalid for the order.

With this example, a single call was performed to the DB and all the necessary checks were performed on DB side with no extra round-trips.

One downside is version controlling all this, it's a bit of a mess doing code review for DB function changes because you have to write the whole function every time in a new migration file just to change and if statement, for example.