SQL Triggers and when or when not to use them.

Question

When I was originally learning about SQL I was always told, only use triggers if you really need to and opt to use stored procedures instead if possible.

Now unfortunately at the time (a good few years ago) I wasn't as curious and caring about fundamentals as I am now so never did ask to the reason why.

What's the communities opinion in this? Is it just someone's personal preference, or should triggers be avoided (just like cursors) unless there is a good reason for them.

Answer 1

The Wikipedia article on database triggers presents a good overview of what triggers are and when to use them in different databases.

The following discussion is based on SQL Server only.

Using triggers is quite valid when their use is justified. For example, they have good value in auditing (keeping history of data) without requiring explicit procedural code with every CRUD command on every table.

Triggers give you control just before data is changed and just after the data is changed. This allows for:

• Auditing as mentioned before
• Validation and business security checking if so is desired. Because of this type of control, you can do tasks such as column formatting before and after inserts into database.

I was always told, only use triggers if you really need to and opt to use stored procedures instead if possible.

May be the some of the reasons for this are:

1. Some functions that triggers used to do in the old days could now be performed in other ways such as updating totals and automatic calculation on a column.
2. You don't see where the trigger is invoked by examining code alone without knowing they exist. You see their effect when you see the data changes and it is sometimes puzzling to figure out why the change occurred unless you know there is a trigger or more acting on the table(s).
3. If you use several database controls such as CHECK, RI, Triggers on several table, your transaction detailed flow becomes complex to understand and maintain. You will need to know exactly what happens when. Again, you will need good documentation for this.

Some differences between triggers and non-trigger stored procedures are (amongst others):

• A non-trigger stored procedure is like a program that has to be invoked explicitly either from code or from a scheduler or from a batch job, etc. to do its work, whereas a a trigger is a special type of stored procedure that fires as a response of an event rather than be directly executed by the user. The event may be a change of data in a data column for example.
• Triggers have types. DDL Triggers and DML Triggers (of types: INSTEAD OF, For, and AFTER)
• Non-Trigger Stored procedures can reference any type of object, however, to reference a view, you must use INSTEAD OF triggers.
• In SQLServer, you can have any number on non-trigger stored procedures but only 1 INSTEAD OF trigger per table.

Answer 2

Triggers are a requirement for any complex data integrity rules. These cannot be enforced anywhere except the database or you will have data integrity problems.

They are also the best place for auditing unless you don't want to capture all changes to the database (which is the problem of auditing from the application).

Triggers can cause performance issues if not written carefully and not enough developers are knowledgeable enough to write them well. This is part of where they get their bad rap.

Triggers are often slower than other means of maintaining data integrity, so if you can use a check constraint, use that instead of a trigger.

It is easy to write bad triggers that do stupid things like try to send emails. Do you really want to be unable to change records in the db if the email server goes down?

In SQL server, triggers operate on a batch of records. All too often developers think they only need to handle one record inserts, updates or deletes. That is not the only kind of data changes that happen to a database and all triggers should be tested under the conditions of 1 record change and many record changes. Forgetting to do the second test can lead to extremely poorly performing triggers or a loss of data integrity.

Answer 3

Use of database triggers

1. To drive column values automatically.
2. To enforce complex integrity constraints.
3. To enforce complex business rules.
4. To customize complex security authorizations.
5. To maintain replicate tables.
6. To audit data modification.

Answer 4

Another use case I've personally come across is for databases that are accessed by more than one program. If you want to implement functionality but not redesign all systems for it, a trigger makes a sensible solution.

For example, I recently worked on a database that had previously existed as solely an office system. When a webapp was written to interface with it, we wanted to implement a notification system (similar to stackexchange, for example), which would be fired by multiple events, such as processing a transaction, and so forth. We were able to implement a trigger so that updates in the office backend fired a trigger to create the notification for the frontend and tell the user that their transaction had been processed by the office.

Answer 5

Lets say you need to push data to a 3rd party system in near real time. Your table contains 950 gigabytes of data, so it's too big to just simply push the entire table to the 3rd party app.

Instead you accumulate changes in a queue. Some external program will then periodically push out small batches of queued data.

The system has over 2000 stored procedures. You also know that tons of sql exists in the source code. To guarantee the queue is populated correctly you'll have to search through all the stored procs and code and hope you don't miss anything.

Instead you can put a trigger on the table to keep the queue updated. Guaranteed not to miss anything. One central location. Performance penalty? Not really because the hit of populating the queue cannot be avoided whether it's by trigger, or external.

In this scenario I would say not using a trigger is a bad design choice. If later you want to use a new method of pushing data (say the queue isn't working out) and the interface changes you are protected if you use the trigger. Triggers are often the best choice. Don't listen to dogmatic anti-trigger fanboys.

Answer 6

Triggers can be used to enforce constraints on database which can't be enforced during database schema creation and any DML statements.

Answer 7

A trigger that sends email is not necessarily a 'stupid' idea. What is stupid is not anticipating the email outage in the design and handling it gracefully w/o data loss. The 'stupid' part of this is really bad to non-existent error handling by lazy developers who feel they are immune to making mistakes.

I would also offer the observation that a trigger can be kept simple by invoking a stored procedure / function that may be arbitrarily complicated and may well be reusable by multiple triggers or other stored procedures. That is why there are packages and libraries.

Bigotry is really crippling.