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?
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.
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.