Trigger question for DBAs

I'm not a DBA, but I've been asked to play one from time to time. I like the completeness of triggers (as long as security best practices are followed instead of everyone using "sa"). I like the comfort of knowing, for example, that the code will execute after an insert regardless of whether the insert was caused by the typical process in the UI or someone running T-SQL directly against the database or any other way. This is especially important if the trigger is used for auditing.

I use INSTEAD OF triggers quite a lot when I want to change the functionality of a system. For example, on my company's website, we have a bug tracker system, which expected to find it's own tables etc. Instead of writing functionality to synchronise user accounts between the main site and the bug tracker site, we use views which match the schema of the bug tracker tables, and then use INSTEAD OF triggers on those views to perform the relevant operations in our native tables. I find that sort of thing invaluable.

Also, as Jack says, the 'protecting me from me' bit can be important if you're in a pressured environment and you need quick, guaranteed results. As Håkan (sorry I have no idea how to get the special a mate! - aha - thanks Kev) says, you need to be mindful of performance, so it pays to test, test and test again!

As a rule, I'm definitely in the 'No' category.

Hate it when business logic, or workflow is implemented as triggers. If you need to do something to some data, as part of a process, then put it with the rest of your code in the SP. Code should be somehwat self-documenting, but splitting the workflow out between an SP and a trigger on the underlying tables just breaks that notion.

Having said that, I do use triggers, but as most have said here already, mainly for auditing purposes.

Do I allow them? Yes. But do I encourage them? Heck no. I realize they have their place in a well designed database system, but I've noticed that more often than not they are either used incorrectly or as a crutch to try to prop up some bad code or bad structure. So, I look at them with a VERY jaundiced eye when they're coming my way.

By the way, the re-launch of the SQL Server Standard had an article on triggers in it. It's worth checking out.

This is a classic "It Depends" question.

I typically only use them for auditing, but I also have used them to enforce some RI (FK could be from one of 2 tables). I've also used them in an EAV design to populate the attributes with either null or default values when an entity was created and when a new attribute was created.

Like Tom, I like that the trigger fires when something happens outside the normal application bounds. I call this "protecting me from me".

They definitely add some overhead and poorly written ones are total performance killers.