I know that triggers can be used to validate stored data to keep database consistent. However, why not perform validation of data on the application side before storing them into the database?
For example, we store clients, and we want to perform some validation that cannot be easily done on DDL level.
https://severalnines.com/blog/postgresql-triggers-and-stored-function-basics
Another example is audit.
Update
How triggers and database transaction work together.
For example, if I would like to perform validation of data being inserted. It is done inside of a transaction. What happens earlier: transaction is committed or trigger is executed?
Best Answer
It depends on what kind of application system you are building:
if you are creating an application-centric system which contains just one main application, with a dedicated database specifically for this application, and ideally one team responsible for evolving application and database side-by-side, you can keep all validation logic and also audit logic inside the application.
The main benefit of this is that you do not have to distribute the business logic between application and db, so maintaining and evolving the system becomes often easier. As a bonus, you do not tie the application too much to a specific type of DBMS or DBMS vendor. This approach is obviously required if your application wants to be able to use a lightweight DB system which does not provide triggers.
If, however, you create a system where many different applications share a common database, and it cannot envisioned beforehand which applications will write to it in the future, or which teams will develop applications for filling data into the db in the future, then it is better your database will be responsible for guaranteeing as much data consistency as it can. And that is where triggers get really helpful. In larger systems, referential constraints are often not sufficient for this, but a trigger which calls a stored procedure can implement almost any kind of validation you need.
Another reason for using triggers can be performance: in complex data models, it is not uncommon to encounter complex consistency rules which require to use a lot of additional data which is not part of the current working set available at the client application. Transfering all those data over the network first for making validation possible at the client side can have a notable performance impact.
See also this older SE post: Application Logic Vs DB Triggers for database cleaning
So decide for yourself what kind of system you are building, then you can make a founded decision if triggers are the right tool for your case or not.