Opinions on triggers

  • I've been a database developer for quite some time now, and I've always been encouraged by senior developers to shy away from triggers in general, but no one has had a really good answer why. (I've also been told to stay away from cursors and not drink the water in Mexico, but I've heard valid reasons for those).

    I'm looking for a general discussion on triggers. I realize it's an extremely broad topic, but I'm hoping to really put together my own opinion, so the more responses and viewpoints, the better.

    So what are the pros and cons of using triggers? Are they really helpful? What kind of performance traps might I see? What are the benefits of using them? Is there anything I should be aware of when using triggers? Any "ah-ha!" or "gotcha!" advice you can give?

    I look forward to the discussion.

  • In my opinion, the commonly heard arguments against triggers are mostly from a practical angle:

    1. Triggers are bad because you can easily mess up - change a row somewhere and affect something else without being aware it happens.

    True, if you do not know what triggers exist in your database. But in my opinion not necessarily a bad thing. Assuming the database was designed and built correctly, then if you make a change that fires the trigger, that other data should change.

    But there is validity to the point. If a row was accidentally removed (or lost after repairing inconsistency), or if data has otherwise gone bad, you sometimes need to be able to correct that data without affecting other data, and triggers can get in the way of that. Requiring more work to disable them, or even causing damage to spread further if you are not aware that they exist.

    2. Triggers are bad because they are hard to get right - a lot of triggers are incorrectly written to handle only single-row operations, or to not handle MERGE correctly.

    All very true, but I think that this is better combatted by educating people and correcting bad code where you see it then by trying to forbid the instrument.

    3. Triggers are bad because they have a negative impact on performance

    Absolutely right. You can write the trigger to be as fast as possible, but it'll still be slower than having no trigger. If you can avoid them, then do. But if you need them to get your application to work correctly, then do not hesitate to use them. The alternative is either a fast but incorrect application, or a workaround that avoids the trigger but not the performance hit.

    4. Triggers are bad because you have not enough control over execution order.

    True. I once had the misfortune of inheriting a database that had ten triggers on some of its tables, and you can only use sp_settrigorder to determine the first and the last.

    But there is a simple fix. Don't do it. When I work with trigger, I normally limit them to at most one per operation per trigger (so one for inserts, one for updates, and one for deletes), so instead of adding an extra trigger I would add extra logic to the existing trigger. The only exception is that I have in some occassions used generated triggers to do standard auditing, and I of course leave those triggers untouched.

    5. Triggers are often abused to achieve things better done in other ways.

    True, but the same can be said about almost every other technique. You can name almost any feature (CTE, CLR, APPLY, user-defined functions, views, synonyms, service broker, etc etc etc), and you will find people who go overboard on using that specific feature because they once had success with it.

    Do not use a trigger to replace a constraint. If a business rule can be implemented with a constraint, then that is always the better option. It is often even better to use a combination of constraints and computed columns instead of a trigger. But when this is not possible and a trigger is the only option, go for it.

    (Also, do NOT replace triggers with a CHECK constraint that uses a user-defined function. SQL Server does not know what tables are accessed in that function, so it will only evaluate the constraint when the row itself changes, not when any of the other data changes).

    Against all these pragmatical arguments against (ab)using triggers, I put one argument in favor of them. It is the responsibility of the database to ensure that all committed data is valid as defined by the business rules. That is why we implement primary keys, unique constraints, foreign key constraints, check constraints, and NOT NULL constraints. But that responsibility of the database does not stop when those standard tools fall short. All business rules should be enforced by the database, so those that cannot be implemented using the standard tools should be implemented with the one tool that gives us ultimate flexibility to implement every imaginable rule: triggers.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • I can only echo what Hugo has already said. Triggers are a tool that has its uses, but, like all tools, need to be used correctly. You don't hand a person a chainsaw without educating them how to use it properly and you shouldn't do that with triggers either. Even cursors have their place.

  • I too agree with what Hugo has said.

    There are some cases where a trigger is the wrong way to do something. Wherever possible, data integrity should be enforced by constraints and the type system, so using triggesr to ensure (for example) that a particular column-set contains no duplicates, or that a particular column contains no nulls should normally be avoided in favor respectively of a UNIQUE constraint or a NOT NULL column declaration. And I used to insist on code changes when anyone had used an INSTEAD OF trigger to do what should have been done with the ON clause of a foreign key constraint.

    But the most important thing to remember is Hugo's final (unnumbered) point: "It is the responsibility of the database to ensure that all committed data is valid as defined by the business rules." If ensuring that means that you need triggers (which it sometimes will) then include those triggers in the database - never rely on applications to maintain data integrity, epecially when the data is used by multiple applications written by different development teams.

    Tom

  • A couple places I willfully employ triggers:

    1. maintaining audit columns like last modified date and user

    2. copying modified or deleted rows to an audit table or shadow-history table

    I see it much the same as Hugo, Jack and Tom, the database must ensure that only data that adheres to the business rules is committed. Stored procedures could theoretically do this work however in direct-table-access scenarios, e.g. production support or applications that issue ad hoc or prepared SQL statements directly to the database, triggers are a way to more closely preserve the audit trail. The trick is to keep these operations as lightweight as possible to the trigger adds only negligible overhead and the logic is simple so you have a good chance of getting it right.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Wow. That is a lot of food for thought and exactly what I was looking for.

    It appears that there's somewhat of a consensus on how triggers could/should be used. It looks like I was given some misleading information in the past and will start to change my thinking.

    Thank you for offering your thoughts on this.

  • TomThomson (3/23/2016)


    ...never rely on applications to maintain data integrity, epecially when the data is used by multiple applications written by different development teams.

    I really like this quote and am going to start using it.

    Thanks!

  • There's nothing wrong with triggers... used appropriately. Same thing goes for just about every other object within SQL Server (except multi-statement table-valued user-defined functions, those things are pure EVIL and should never be implemented).

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Grant Fritchey (3/24/2016)


    There's nothing wrong with triggers... used appropriately. Same thing goes for just about every other object within SQL Server (except multi-statement table-valued user-defined functions, those things are pure EVIL and should never be implemented).

    No, Grant. Even those have their use cases. (Admittedly most of those use cases are to appear as terrifying examples in my presentation "T-SQL User-Defined Functions, or: Bad Performance Made Easy", but there are some real actual use cases for this technique as well.)


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • So then what is an appropriate use of a trigger?

  • I agree with Hugo, but I'll add one more thing against triggers. They're hidden.

    We see them rarely, they don't easily appear in SSMS, and it's easy to forget they're in place. If you use them, and there are good reasons to use them for things that much take place inside of a table modification trigger, then make sure they are well documented and everyone from developers to sysadmins are aware they are in place.

  • another reason to avoid them: Linq To SQL and Entity Framework side affects.

    we just had an issue with transactions rolling back without any SQL error;

    the issue was a linq to sql featuring change tracking(the default behaviour) would issue a command to update a row.

    the trigger would populate the UpdatedDate.

    Linq To SQL checked the value after the call, and the UpdatedDate was not null like it expected after the transaction, and rolled an insert back, thinking that some other process had changed the data before it did.(technically true)

    so adding to what Steve said, that hidden event, and adding what seems like an innocuous trigger affected one column that was null anyway, caused a dataloss without an error.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • CferMN (4/8/2016)


    So then what is an appropriate use of a trigger?

    Some examples off the top of my head:

    * Auditing (depending on the exact audit regulations - in some cases, triggers are not secure enough)

    * Checking business rules that are too complex to be checked in a constraint

    * Recomputing denormalized computable columns (** see note 1 below)

    * Rarely, kicking off an external process (** see note 2 below)

    Note 1: When possible, prefer not to store computed columns. But there are exceptions. For example, I know of at least one off-the-shelf ETL program that doesn't store the current stock of an item in the warehouse; instead it aggregates all stock movements recorded since the program was installed. That, in my opinion, is not a good use of resources; the current stock level should be recorded and a trigger on the table that records stock movememt should update the current stock.

    Note 2: Starting an external process from a trigger should be very rare, and very well-documented. It should also NEVER EVER be done directly. First, it often slows down the transaction more than any DBA will accept. And second, the external process is not part of the transaction, so when the modification that fires the trigger rolls back, the process still runs.

    If you really, really, really need an external process to start when something happens, and you really, really, really are sure that the database is the place where this should be orchestrated, then set up a service broker queue. Let the trigger put a message on the queue, and an activated stored procedure can then actually kick off the process. This has two benefits: First, the queue message will be rolled back if the trigger action rolls back so the process does not start. And second, the queue is asynchronous so your users do not have to wait for a slow external process to respond.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Lowell (4/8/2016)


    another reason to avoid them: Linq To SQL and Entity Framework side affects.

    we just had an issue with transactions rolling back without any SQL error;

    the issue was a linq to sql featuring change tracking(the default behaviour) would issue a command to update a row.

    the trigger would populate the UpdatedDate.

    Linq To SQL checked the value after the call, and the UpdatedDate was not null like it expected after the transaction, and rolled an insert back, thinking that some other process had changed the data before it did.(technically true)

    so adding to what Steve said, that hidden event, and adding what seems like an innocuous trigger affected one column that was null anyway, caused a dataloss without an error.

    In your case, I am inclined to blame Linq rather than the trigger. Who told Linq to check that UpdatedDate was NULL after the transaction? Who told Linq to roll back the insert if it is not? And nost importantly, who told Linq to do that silently?

    Even when there had been an actual concurrency problem and the rollback would have actually been warranted, then it still should have reported the error.

    I assume that the application developer messed op the try ... catch blocks in the application code, but I have to admit that I do not know Linq well enough so perhaps this is indeed an issue where Linq behaves incorrectly.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • I would say to use triggers to maintain audit information and to support low-level data integrity that cannot be done via the more standard methods. Some others have been listed and I don't have a comment as I've never run into that situation.

    The audit use is self-explanatory. Other than auditing, I have found uses for triggers rare. But here are some examples of what I mean. I had a design that had offices parent to jobsites parent to subjobsites. Any one of those levels could be set to inactive. If a jobsite was inactivated, a trigger inactivated all the children. If an office where inactivated, all its jobsites were inactivated, with that trigger inactivating the sub jobsites.

    In another case a few SKUs had required equipment. There was a bit field indicating if this was the case. That field was maintained by trigger from the table that stored the SKU and equipment required. While this could have been solved by query at the time, the performance hit was significant. I don't remember all the details, but it was the most complicated trigger I ever developed as it had to take a number of things into account.

    Both of these show a relation to the data that I term "low level". The first prevented children being active when the parent was not. The second showed the existence of a certain set of data in another table that was related to it, but not in a direct fashion.

    //Edited for grammar.

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply