Triggers: Threat or Menace?

Triggers are generally over-used in SQL Server. They are only rarely necessary, can cause performance issues, and are tricky to maintain If you use them, it is best to keep them simple, and have only one operation per trigger. Joe Celko describes a feature of SQL that 'gets complicated fast'.

The original SQL-86 standard did not have TRIGGERs. In fact it did not have much of anything! My favorite omission to the standard was that you could grant privileges to a user, but you could not revoke them. Talk to security officers about this and see what they’d have to say about that!

We were trying to get a skeleton standard into the public domain so as to make sure that SQL did not suffer the fate of BASIC. This early popular programming language quickly mutated into a host of dialects, and never really converged back to a single ANSI/ISO Standard BASIC, so it was important to get something out there.

Trigger is a schema-level construct that has a body of procedural code. It is attached to one and only one table (the ‘subject table’ in SQL standard lingo). This body of code is executed when a ‘schema event’ occurs. These events are insertion, deletion, and updating; things that change the content of the subject table. Queries do not change the contents of the database, so they cannot fire a TRIGGER without an event.

086-small

… ‘My advice is to avoid using TRIGGERs’

Triggers could not have been added to the language initially. We did not have the SQL/PSM (Persistent Stored Modules, the official procedural language in the standards). Vendors, however, each had their own local proprietary 4GLs. Oracle uses PL/SQL, DB2 uses SQL/PSM and PL/SQL, Informix had Informix 4GL and, of course, SQL Server has T-SQL.

When we were setting up the SQL-92 standard, we deferred some things and TRIGGERs was one of them. We really did not think the TRIGGERs would come to the forefront so soon and we wound up having to add this in the SQL:1999 standard, based on what vendors had been doing for the past several years.

A stored procedure is relatively easy to invoke. In the PSM, you use “CALL <procedure name> (<parameter list>)”, pretty much like any other programming language. But TRIGGERs get complicated fast. When is the TRIGGER executed, relative to the event? Is it before or after the event (BEFORE and AFTER are SQL keywords)? If I have more than one TRIGGER on the same table, in what order they executed? The model used in SQL Server is fairly simple, but the full ANSI/ISO Standard is elaborate.

Triggers have to follow one of the basic principles of RDBMS, namely that when the transaction is finished, all of the constraints are true. Oh; for the record, all constraints are always TRUE on an empty table. But this principle does not prevent an intermediate step in the execution of TRIGGERs from violating constraints.

Finally, is the procedural code applied to the whole table or row by row? The ANSI/ISO Standard lets you pick how the code is executed. If you want more control, you can also restrict the columns in an UPDATE FOR <column list>” within the TRIGGER. SQL Server, however, has only table level TRIGGERs. This model often confuses new SQL programmers, whose mindset is still back in procedural code and a record-at-a-time processing from their old languages.

I am not going to get into the details of this, but this procedural code does not even have to be in SQL. That can be a total mess. Since an external procedure is not in the scope of the database session, you cannot get any kind of error message or feedback.

But even if you do not leave SQL, one TRIGGER can change its subject table, which in turn will cascade to a second table via DRI actions and/or a second TRIGGER. External procedures can pretty much do it anything in the application. Those external events can shut down the entire application, move it to another computer, etc. in short, all bets are off.

It is quite possible to build an elaborate, tangled web of TRIGGERs. It is also probably an incredibly bad idea! The SQL engine will have to store all of the information it needs to do rollbacks, so performance is never going to be good. Then you have to understand all of the possible execution paths that can occur depending on which subject table you touch.

You can do all of the usual things with TRIGGERs that you do with other schema objects and constraints.

DISABLE TRIGGER
ENABLE TRIGGER

Triggers can be enabled and disabled, like named constraints, but this has the same problems of other constraints. If, by doing so, you bring the system to an invalid configuration, then you can destroy the integrity of your schema and bring it to a state that does not match the constraints you wanted to enforce.

CREATE TRIGGER
ALTER TRIGGER
DROP TRIGGER

You can create, alter and drop TRIGGERs. However, remember the order of execution problem. If you drop a TRIGGER then re-create it, you might change the order of execution. I am not sure if the ALTER statement has the same problem to be honest.

DRI Actions

While we were looking at TRIGGERs, we got proposals for DRI (Declarative Referential Integrity) actions. You should be very familiar with this feature, by now. Here is some BNF:

The FOREIGN KEY references can be named and can be made up of one or more columns in the referencing table at have to match to the UNIQUE or PRIMARY KEY columns in the referenced table.

The only two database events that DRI actions track are in deletion and update. If you think about it for a minute, having an “ON INSERT” just would not work. When there is a change in the referenced table, then a referencing table event must change the referenced table. That means the that you would be able to place an order on your website without being required to put anything into the (order details) basket beforehand.

  1. NO ACTION. This is the default if you did not have an ON clause. You get an error message and a rollback from the system. In the example I’ve just mentioned of orders and order detail, this transaction would roll back if you try to put an item into the details of an order that does not exist. Use it in the way that we do with the “ELSE NULL END” in a case expression; these empty markers are good programming technique.
  2. CASCADE. If this option is used with an ON DELETE clause, then all of the matching rows in the referencing table are also deleted. In the example of orders and order details, if you try to put an item into the details of an order that does not longer exist, then this transaction would roll back. This is how you enforce strong and weak entity relationships.
  3. SET NULL. The corresponding rows in the referencing table have the matching columns set to NULL. This obviously requires that those columns be NULL-able. Then the question is, “does it make sense?” It depends on the context.
  4. SET DEFAULT. The corresponding rows of referenced table have the matching columns set to their default values. This obviously requires those those columns have a default. As an aside, the data types in both the referenced and referencing tables should match exactly; the compiler has to go through a lot of trouble to do casting.

The full ANSI/ISO version of this has more elaborate options for doing matching between the two tables. But I found that in practice, a PARTIAL match is rarely used.

Because the TRIGGER is procedural code, there is no real way to optimize it. However, DRI actions have a fixed structure and we can build it into the SQL engine. This also gives information to the optimizer; if I know there is a reference, then I know something exists in both the referenced and referencing tables. I do not have to do any extra work to validate this.

Avoid Using Triggers

My advice is to avoid using TRIGGERs. Here is a quick list of reasons why:

  1. The dialects and options vary so much from SQL to SQL that your TRIGGERs will not port or even readable to new programmers in your shop.
  2. Triggers are easy to mess up. When you look at DRI actions, there is no large elaborate body of procedural code in a proprietary dialect to understand. If data is corrupted in some way, then disabling TRIGGERs can be difficult or dangerous.
  3. If your TRIGGER is incorrectly written to handle only single-row operations, or MERGE statements correctly, then finding the problem is very difficult in T-SQL.
  4. Triggers have a negative impact on performance. If you do not need a TRIGGER, then do not write one, obviously. Are you doing something with a TRIGGER that should have been done in another tier of your application? In particular, you will see people put things it should have been in an input procedure into a TRIGGER; they are basically doing the editing and data scrubbing that should have been done before the data got to the database. In T-SQL, you have to fire the TRIGGER on the whole table. When it failed you have to do a rollback or lock the database while you apply those corrections. This can be a huge performance hit.
  5. The order of execution of multiple TRIGGERs is not obvious. You need to get to schema information data, and most SQLs default to creation order. In T-SQL you can use sp_SetTrigOrder to determine the first and the last TRIGGERs to fire. There is cute trick if you have only three TRIGGERs in which you set the first and last, then let the third TRIGGER be forced to the second position.
  6. 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. A CHECK () constraint is checked at the row level at insertion time, not the table level over and over (in full ANSI/ISO SQL, we do have schema level constraints, the CREATE ASSERTION statement, but that is another topic).

INSTEAD OF Triggers

In SQL Server, all of the triggers occur after the event. Other products have triggers that occurred before the event. In addition to the after triggers. However there something else you need to be aware of called an INSTEAD OF trigger.

But let us go back a bit, to college database classes and one of the big debates. The view is a virtual table and one of our goals was to ensure that they are treated as much like base tables as possible. This also means that views should be updateable.

Originally, we defined an updateable view in the ANSI/ISO standards as being on one and only one base table, with keys that allow us to resolve any reference to a row, to one and only one unique row in the base table. This is very minimal, and everybody could implement it. If a vendor wanted to give you more, this was fine with us.

Achieving a general VIEW updateability is known to be an NP complete problem at best and impossible in many general cases. In plain English, it means the problem of setting up rules that everybody would agree on for views is impossible even in theory.

Let us make that a little easier to see. Let us create some skeleton Personnel tables like this:

And, yes, this is really a terrible design, but bear with me. I can now create two views like this:

Obviously I can assemble the original Personnel table with a UNION or UNION ALL the two views.

In fact, if I have any two of the three tables/views involved, I can reconstruct the third. All I need is to use an EXCEPT operator on Personnel. This design error is called “attribute splitting” and it refers to taking the values of an attribute and making them into multiple columns or even tables, like we have done here.

But what if I started off not with views, but with improperly designed base tables? Then I could make “Personnel” into a view with the union. But there are problems if I try to update base tables through the union to view. It gets even worse because they could create a view using an IN() predicate that is logically equivalent to a union.

There are also restrictions on the nature of the columns in an updateable view. Deterministic and non-deterministic columns, certain data types and so forth present problems. You just need to read the books online for SQL Server and learn what to avoid. It may get better later, so look again a new release comes out.

The other problem with INSTEAD OF triggers is complexity. The view is a virtual table and has no physical existence. That means you have to be able to get down to the base tables and change them in some way; those changes will then be reflected when the view is invoked again. Well, at least we hope so. As you can imagine, there is a lot of overhead with this.

Programming Tips

When I work with TRIGGERs, which is rare, 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.

Do not use TRIGGERs for auditing. One of the principles of security and audit work is that such processes are to be kept external to the thing being audited. This is done for the same reason that you do not create backups on the same hard disk as the data. What happens when the database is destroyed?

This is why you need an external third-party audit package. It will keep you out of jail and maintain its independent audit trails. It also makes the third-party vendor liable for any problems that you encounter

  • 8985 views

  • Rate
    [Total: 20    Average: 3.9/5]
  • Jeff Moden

    From the teaser on this article…

    “Triggers are generally over-used in SQL Server.”
    Where in the world did you come up with THAT generalization? Please cite a reference.
    Heh… and as for 3rd party auditing, I’ve found that many of them do it with triggers and they don’t know how to write good ones either. When they are able to actually pull off the task of auditing without triggers, I find that they waste a huge number of resources especially when it comes to wider tables where they’ll save the whole row to disk instead of just the 1 or 2 columns that have been updated. Worse yet, some store both the “Before’n’After” row, which immediately doubles the footprint of the table on disk and causes it to take 3 times the space on just a single update per row.
    So, what to do if the database that contains your auditing is destroyed? The answer is simple. You’d do the same as with any database… you’d restore it from backup.

    • Andrew Clarke

      As editor, I’m to blame for the teaser, not Joe, Jeff. The sentiment I expressed comes from years of experience auditing, assessing and checking relational databases in government and large enterprise settings, but I think Joe and I agree about this. I’m surprised that you don’t agree: maybe you’ve been lucky. As for auditing changes in data, maybe it is time for a Simple-Talk article on the range of available techniques, for all budgets. Are you up for writing this?

      • Jeff Moden

        Sorry, Andrew. Not sure why the system didn’t alert me to your response 2 months ago. Sure… as long as you don’t mind the fact that the code won’t be portable. 😉 I’ll put a couple of ideas together before I write anything up. What’s the best way to submit those ideas to you?

    • Joe Celko

      Many of the features you see in SQL Server were inherited from Sybase. In particular, when we were adding triggers to the ANSI/ISO standard SQL, we found most common use was to ensure referential integrity. In particular, that weak entities would be deleted or cascaded when their strong entity was deleted or changed. We did not have DRI actions back then, so triggers with the only way to go.

      One of the committee members did a quick survey of actual code on mainframes at the time and found that a little over 70% of the triggers were used for cascading. That is why we built them into the language.

      We also did not have the concept of a tiered architecture back in the dark ages. This is why we have CONVERT() and MONEY data types. They exist to keep COBOL programmers happy when they could not find their missing PICTURE clauses. In modern programming such things be done in a presentation layer and not in the database.

      Maybe we should not feel too bad. How many of you have a pair of blue jeans with a watch pocket, and have never owned pocket watch? You think somebody would make it big enough to hold a cell phone today, wouldn’t you?

  • samot1

    regarding the DISABLE TRIGGER statement:

    For our databases I prefer to write an IF CONTEXT_INFO = 0x1000 RETURN as one of the first lines into each trigger. When I have to do something that has to skip the trigger, I use SET CONTEXT_INFO 0x1000 to “disable” the trigger this way temporary, but ONLY for my own, current session, so I don’t have to wait for a maintenance window.

    Instead of 0x1000 you could of course any other value too. And you should not forget to SET CONTEXT_INFO 0x00 at the end of your INSERT / UPDATE / DELETE (or close the session)

    Caution: if you have an INSTEAD OF TRIGGER (imho the worst type of triggers), you may not RETURN when CONTEXT_INFO = 0x1000 but have to perform the INSERT / UPDATE / DELETE manually…

  • William Sisson

    1. There are certain
    kinds of constraints that can only be expressed with triggers. In
    particular constraints that reference more than one row in the same
    table. For example, ensuring that there can be no overlapping date
    ranges and that all date ranges are contiguous.

    2. Check constraints
    that query other tables (or the current table) in a function will be
    considerably less efficient than the equivalent trigger based
    solution, because the query in the function has to be executed again
    and again rather than as a set operation joining the inserted or
    deleted tables.

    3. Just because the
    trigger implementation in another well known SQL-DBMS is
    problematical (the constraints described in point 1 being
    particularly tricky to implement) doesn’t mean that you shouldn’t use
    them in SQL Server.

    4. “I come to bury
    Caesar not to praise him”.

    If assertions were implemented then
    the need for trigger based constraints would disappear. It would be
    interesting to have an article explaining why no vendor has
    implemented assertions, which have been in the SQL standard since
    1992.

    • Joe Celko

      >> 1. .. For example, ensuring that there can be no overlapping date ranges and that all date ranges are contiguous. <<

      Kuznetsov’s History Table is a SQL idiom which builds a temporal chain
      from the current row to the previous row. This is easier to show with
      code:

      CREATE TABLE Tasks
      (task_id INTEGER NOT NULL,
      task_score CHAR(1) NOT NULL,
      previous_end_date DATE, — null means first task
      current_start_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
      CONSTRAINT previous_end_date_and_current_start_in_sequence
      CHECK (prev_end_date <= current_start_date),
      current_end_date DATE, — null means unfinished current task
      CONSTRAINT current_start_and_end_dates_in_sequence
      CHECK (current_start_date <= current_end_date),
      CONSTRAINT end_dates_in_sequence
      CHECK (previous_end_date current_end_date)
      PRIMARY KEY (task_id, current_start_date),
      UNIQUE (task_id, previous_end_date), — null first task
      UNIQUE (task_id, current_end_date), — one null current task
      FOREIGN KEY (task_id, previous_end_date) — self-reference
      REFERENCES Tasks (task_id, current_end_date));

      Well, that looks complicated! Let’s look at it column by column. Task_id explains itself. The previous_end_date will not have a value for the first task in the chain, so it is NULL-able. The current_start_date and current_end_date are the same data elements, temporal sequence and PRIMARY KEY constraints we had in the simple
      history table schema.

      The two UNIQUE constraints will allow one NULL in their pairs of columns and prevent duplicates. Remember that UNIQUE is not like PRIMARY KEY, which implies UNIQUE NOT NULL.

      Finally, the FOREIGN KEY is the real trick. Obviously, the previous task has to end when the current task started for them to abut, so there is another constraint. This constraint is a self-reference that makes sure this is true. Modifying data in this type of table is easy, but requires some thought. Fortunately, Alex has written a Simple Talk article to explain in more detail how it is done.

      Disabling Constraints

      Just one little problem with that FOREIGN KEY constraint. It will not let you put the first task into the table. There is nothing for the constraint to reference. In Standard SQL, we can declare constraints to be DEFERABLE with some other options. The idea is that you can turn a constraint ON or OFF during a session so the database can be in state that would otherwise be illegal. But at the end of the session all constraints have to be TRUE. or UNKNOWN.

      In SQL Server, you can disable constraints and then turn them back on. It actually is restricted to disabling FOREIGN KEY constraint, and CHECK constraints. PRIMARY KEY, UNIQUE, and DEFAULT constraints are always enforced. The syntax for this is part of the ALTER TABLE statement. The syntax is simple:

      ALTER TABLE NOCHECK CONSTRAINT [ | ALL]; This is why you want to name the constraints; without user given names, you have to look up what the system gave you and they are always long and messy.. The ALL option will disable all of the constraints in the entire schema. Be careful with it.

      To re-enable, the syntax is similar and explains itself:

      ALTER TABLE CHECK CONSTRAINT [ | ALL]; When a disabled constraint is re-enabled, the database does not check to ensure any of the existing data meets the constraints. So for this table, The body of a procedure to get things started would look like this:

      BEGIN
      ALTER TABLE Tasks NOCHECK CONSTRAINT ALL;
      INSERT INTO Tasks (task_id, task_score, current_start_date,
      current_end_date, previous_end_date)
      VALUES (1, ‘A’, ‘2010-11-01’, ‘2010-11-03’, NULL);
      ALTER TABLE Tasks CHECK CONSTRAINT ALL;
      END;

      >> 2. Check constraints that query other tables (or the current table) in a function will be considerably less efficient than the equivalent trigger based solution, because the query in the function has to be executed again and again rather than as a set operation joining the inserted or deleted tables. <> 3. Just because the trigger implementation in another well known SQL-DBMS is
      problematical (the constraints described in point 1 being particularly tricky to implement) doesn’t mean that you shouldn’t use them in SQL Server. <> 4. It would be interesting to have an article explaining why no vendor has
      implemented assertions, which have been in the SQL standard since 1992. <<

      Yes, it would be a good article. I know the general assertion could be very expensive to execute. Think about writing one the checks to see that all of the tables in a huge schema are not empty.

      • William Sisson

        >> It would be interesting to have an article explaining why no vendor has
        implemented assertions, which have been in the SQL standard since 1992.

        There is an example of such an article here (on page 13):

        https://nocoug.files.wordpress.com/2014/08/nocoug_journal_201308.pdf

        • Erwin Smout

          An informed reader …

          Just a quick plug to point out that the NOV ’13 issue had a followup article on the same subject, by me, entitled “CREATE ASSERTION : neither impossible nor a dream”. If you’re interested in the subject and you weren’t already aware of that other article, maybe you might want to read that one too.

          • William Sisson

            Thank you, I should have mentioned your article too.

      • William Sisson

        Here is another single table, multi-row constraint for you to consider: a department cannot employ a manager without a clerk in the same department.

        My point is not that I want you to find a non-trigger solution for this constraint, but that implementing the trigger solution is fundamentally simpler and more generic than thinking up increasingly complicated workarounds to avoid the use of triggers.

        The triggers are declarative, in most cases they only require a single statement.

        I would in any case object to your solution for the date range constraint on three grounds:

        1. The use of a column that will never be visible to the end user and which is effectively redundant (previous_end_date).

        2. The use of nulls. That unique constraints allow nulls doesn’t to my mind make sense. If null represents an unknown value then we cannot reasonably say that it is equal to another unknown value.

        3. The special case to introduce the first row. The special case may seem trivial but when implementing changes the more special cases you have the more likely you are to make mistakes. Each individual special case may be trivial, but the cumulative effect is very problematic.

        The trigger based solution avoids all these problems.

        It is worth pointing out that the implementation of such triggers are considerably simpler in SQL Server compared to Oracle for two reasons.

        1. The absence of inserted and deleted tables. This means that Oracle set based triggers are inherently inefficient as they must query the whole table.

        2. Oracle’s use of multiversion concurrency control. This means that any data you read in the trigger will be in the state as of the beginning of the transaction and not the current state of the data. In order to implement a constraint in Oracle you have to read, store (and lock!) the data at the start of the transaction.

        Neither of these problems exist in SQL Server. I can understand in the light of these problems that some Oracle experts disapprove of triggers, but most of their objections don’t apply to SQL Server.

        I don’t really accept the argument that we should avoid triggers because some people don’t know how to implement them properly. You might equally argue that we should stop using hammers because some people keep hitting their thumbs.

    • Erwin Smout

      Why no vendor has implemented assertions :

      – it’s a little bit difficult
      – database users are not asking for it
      – certain SQL paradigms (thinking mainly of how SQL is itself still highly row-oriented not set-oriented) make the set of use cases where ASSERTIONs can really show their power rather limited.

  • Carl Perkins

    Wow. I disagree. Triggers and constraints are tools. They can be useful and solve many problems. Like any other tool they should be used where appropriate. To blatantly say they should be avoided is a misguided recommendation. I have used them for auditing and have used ‘instead of’ triggers on deletes to do things that are best done in the database. Ya know, this reminds me of two types of carpenters who show up on a work site; one has nothing but a hammer in his toolbox (every problem looks like a nail to him), and another carpenter who has a large number of tools in his toolbox and he is prepared to use each where it is needed – that is the carpenter you want to hire. There are even misguided developer-wanna-bees that think database update logic and constraints should be handle by the client application – very bad indeed. It is true that I have had to work on some triggers written by offshored pseudo developers who did not understand the ramifications. As Spock said ‘each according to his gift’. I would paraphrase that to ‘each according to their talent and exploration of the tool’. And I will not even comment about aGile and sCrum (little a and little s on purpose) – the destruction of software development.

  • Chris

    I completely agree. I think triggers are diabolical. When I was a new DBA years ago I basically thought : “Cool; event driven code in a database.” After years of troubleshooting shoddy databases I’m very wary of them. I think you are offering good advice: ‘Be careful’. If you do use them, know their weaknesses and design accordingly. I got my undies in a bundle when MS plugged the CLR into SQL Server and still view it in a similar unfavorable light. I do plenty of .NET programming (and like it) but I don’t see it exactly as chocolate and peanut butter when it comes to all solutions if you know what I mean. Like your 3rd grade teacher might have told you: “Just because you can do something doesn’t mean you should.” Think twice… maybe thrice… Go Joe!

  • William Sisson

    In defence of triggers I can recommend Toon Koppelaars Blog “Triggers Considered Harmful, Considered Harmful”

    http://harmfultriggers.blogspot.de/

    Those of us who work mainly with SQL Server rather than Oracle might want to compare the SQL Server and Oracle trigger implementations and consider which is more straightforward to work with and why.

  • James Meece

    I use triggers to create flat files, which are then used to link systems together. This has worked very well for my organization for nearly a decade.

  • Ron Clarke

    Looking forward to audit options discussion/article. We use generated triggers based on an audit configuration table (what table, which events, vertical and horizontal filters).

  • cbowman

    I mostly disagree. Triggers are an essential final line of defense to protect data. We’ve used and have depended on them for almost 20 years. Without them, ultimately anything can happen to your data.