When Are Database Triggers Bad

Are database triggers necessary?

The main problems with triggers are

They are completely Global - they apply no matter what the context of the table activity;
They are stealthy; it's easy to forget they are there until they hurt you with unintended (and very mysterious) consequences.

This just means they need to be carefully used for the proper circumstances; which in my experience is limited to relational integrity issues (sometimes with finer granularity than you can get declaratively); and usually not for business or transactional purposes. YMMV.

When are database triggers bad?

I think they're OK when they are used to populate a separate, one-off set of tables for things like logging, aggregation etc. for security or creating metadata for example.

When you start altering your "live" data or "looping back" into your biz info tables, that's when they become evil and unwieldy. They are also utterly unnecessary for this. There is nothing that a trigger does that a stored proc cannot do.

I feel like they are SQL's evil equivalent to GOTOs in programming languages. Legal, but to be avoided unless absolutely necessary, and they are NEVER absolutely necessary.

If database triggers are evil, is it also evil to have side effects when setting a property in java or C#?

Going against the grain here...

Properties should NOT trigger side effects. That's what Method's are for.

By having properties cause side effects you end up in a situation were code is essentially hidden. People rarely expect properties to kick off some process or cause something else to flip. If this has to be documented, then its not obvious and subject to being ignored.

However, we do expect something to happen when we call a Method.

Taking @astander's example, he says that the act of changing "Price" should cause a different property "Cost" to change. However, what if we later add a new property called "Discount"? The code around the Price and Amount properties would have to change. Which isn't very discoverable.

However, if Cost calculated itself.. then everything is better off.

Performance considerations for Triggers vs Constraints

Constraints hands down!

With constraints you specify relational principles, i.e. facts about your data. You will never need to change your constraints, unless some fact changes (i.e. new requirements).
With triggers you specify how to handle data (in inserts, updates etc.). This is a "non-relational" way of doing things.

To explain myself better with an analogy: the proper way to write a SQL query is to specify "what you want" instead of "how to get it" – let the RDBMS figure out the best way to do it for you. The same applies here: if you use triggers you have to keep in mind various things like the order of execution, cascading, etc... Let SQL do that for you with constraints if possible.

That's not to say that triggers don't have uses. They do: sometimes you can't use a constraint to specify some fact about your data. It is extremely rare though. If it happens to you a lot, then there's probably some issue with the schema.

What could cause triggers to be hit for one user in DB, but not for another?

This turned out to be a nondescript permissions error for that particular user account. When the account was removed from the database and added back in - the triggers, logging, and all related events started working appropriately.

What is the best practice for using public fields?

I only ever expose public fields when they're (static) constants - and even then I'd usually use a property.

By "constant" I mean any readonly, immutable value, not just one which may be expressed as a "const" in C#.

Even readonly instance variables (like Result and Message) should be encapsulated in a property in my view.

See this article for more details.