4 ms·

Triggers?? Where you add some application logic to the database, 10 years go by, and no one has any idea how the triggers work? Or even how to test them? I've n

Triggers?? Where you add some application logic to the database, 10 years go by, and no one has any idea how the triggers work? Or even how to test them? I've never seen triggers used successfully in any production application (maybe they work at first, but give them time, and a few code changes).

segmondy 9y ago
You test triggers the same way you test any code. You setup your preconditions, you execute any action, you assert your post condition. You can use something like http://pgtap.org/documentation.html http://pgtap.org/documentation.html to write your database tests. Triggers have a place in app development, and sometimes are the best things to use.
hunterjrj 9y ago
What you're describing is a fault in an organization's maturity with respect to documentation and NOT a fault of a database feature.
wenc 9y ago
Triggers are pretty much like stored procedures. They have their place. One of the major use cases of serverless functions is as triggers.
ZenoArrow 9y ago
> "I've never seen triggers used successfully in any production application" Triggers have one or two good use cases, and plenty of bad use cases. I would suggest the strongest use case is for data validation. Databases don't have very sophisticated type systems, and custom data types can cause headaches. Database triggers allow you to ensure that the data stored in fields matches a set of criteria. To give a basic example, if you had a customer table with an email address field, you could validate that the email address had an @ symbol using a code in an insert and/or update trigger. Of course, you should have similar checks within the software that sits on top of a database, but by putting the validation at a low level by using database triggers you can be more confident that the data integrity will be protected. As a second use case, if the triggers are being used to maintain data for audit or reporting purposes, that can be fine. However, aside from the audit/reporting use case, I would recommend avoiding using triggers which span multiple tables (unless there are some exceptional circumstances where it's the best option). Things get messy when you have a chain of tables, each with their own triggers that can update other tables within that chain. If you spot lots of code like that, run to the hills!
- colanderman 9y ago
  Third case (and most important IMO): schema migrations. When doing major shuffling of a schema, you can use triggers to "redirect" accesses to the old tables or columns into new ones. This way, the schema updates can be applied without bringing down the application. (Such triggers of course are removed once the application is updated, along with any old schema bits.)
- majidazimi 9y ago
  We are using it for building aggregate tables from raw table. We insert data into raw table. And triggers propagate running Min/Max/Avg for hourly/daily/monthly tables. After couple of months we truncate our raw tables but keep aggregate tables. Duplicates are easily removed based on insert. And we get instantaneous result on our aggregate tables (No hourly batch job)

segmondy 9y ago

You test triggers the same way you test any code. You setup your preconditions, you execute any action, you assert your post condition. You can use something like http://pgtap.org/documentation.html http://pgtap.org/documentation.html to write your database tests. Triggers have a place in app development, and sometimes are the best things to use.

hunterjrj 9y ago

What you're describing is a fault in an organization's maturity with respect to documentation and NOT a fault of a database feature.

wenc 9y ago

Triggers are pretty much like stored procedures. They have their place. One of the major use cases of serverless functions is as triggers.

ZenoArrow 9y ago

> "I've never seen triggers used successfully in any production application" Triggers have one or two good use cases, and plenty of bad use cases. I would suggest the strongest use case is for data validation. Databases don't have very sophisticated type systems, and custom data types can cause headaches. Database triggers allow you to ensure that the data stored in fields matches a set of criteria. To give a basic example, if you had a customer table with an email address field, you could validate that the email address had an @ symbol using a code in an insert and/or update trigger. Of course, you should have similar checks within the software that sits on top of a database, but by putting the validation at a low level by using database triggers you can be more confident that the data integrity will be protected. As a second use case, if the triggers are being used to maintain data for audit or reporting purposes, that can be fine. However, aside from the audit/reporting use case, I would recommend avoiding using triggers which span multiple tables (unless there are some exceptional circumstances where it's the best option). Things get messy when you have a chain of tables, each with their own triggers that can update other tables within that chain. If you spot lots of code like that, run to the hills!

colanderman 9y ago
Third case (and most important IMO): schema migrations. When doing major shuffling of a schema, you can use triggers to "redirect" accesses to the old tables or columns into new ones. This way, the schema updates can be applied without bringing down the application. (Such triggers of course are removed once the application is updated, along with any old schema bits.)
majidazimi 9y ago
We are using it for building aggregate tables from raw table. We insert data into raw table. And triggers propagate running Min/Max/Avg for hourly/daily/monthly tables. After couple of months we truncate our raw tables but keep aggregate tables. Duplicates are easily removed based on insert. And we get instantaneous result on our aggregate tables (No hourly batch job)