From Beginning SQL Server 2005 for Developers: From Novice to Professional, Berkeley, Apress, January 2006.

Part 1  |  Part 2  |  Part 3

Although you have become quite proficient in using SQL Server 2005, you really ought to know about one last aspect of it. Triggers are that one last step, and this chapter is the missing link in the foundation of your knowledge and skill set.

There will be times when a modification to data somewhere within your database will require an automatic action on data elsewhere, either in your database, another database, or elsewhere within SQL Server; a trigger is the object that will do this for you. When a modification to your data occurs, SQL Server will fire a trigger, which is a specialized stored procedure that will run, performing the actions that you desire. Triggers are similar to constraints but more powerful, and they require more system overhead, which can lead to a reduction in performance. Triggers are most commonly used to perform business rules validation, carry out cascading data modifications (changes on one table causing changes to be made on other tables), keep track of changes for each record (audit trail), or do any other processing that you require when data on a specific table is modified.

The aim of this chapter is as follows:

• Describe what a trigger is.
• Detail potential problems surrounding triggers.
• Show the CREATE TRIGGER T-SQL syntax.
• Discuss when to use a constraint and when to use a trigger.
• Show the system tables and functions specific to triggers.
• Demonstrate the creation of a trigger through a template and straight T-SQL commands.
• Talk about image data types and the problems that surround updating these columns and firing a trigger.

First of all, let’s see just what constitutes a trigger.

What Is a Trigger?

A trigger is a specialized stored procedure that can execute either on a data modification, known as a Data Modification Language (DML) trigger, or can execute on a data model action, such as CREATE TABLE, known as a Data Definition Language (DDL) trigger. DML triggers are pieces of code attached to a specific table that are set to automatically run in response to an INSERT, DELETE, or UPDATE command. However, a DDL trigger is attached either to an action that occurs within a database or within a server. The first part of the chapter will look at DML triggers, followed by an investigation of DDL triggers.

Note: Unlike stored procedures, you cannot manually make a trigger run, you cannot use parameters with triggers, and you cannot use return values with triggers.

The DML Trigger

Triggers have many uses. Perhaps the most common for a DML trigger is to enforce a business rule; for example, when a customer places an order, check that they have sufficient funds or that you have enough stock; if any of these checks fail, you can complete further actions or return error messages and roll back the update.

DML triggers can be used as a form of extra validation, for example, to perform quite complex checks on data that a constraint could not achieve. Keep in mind that using constraints instead of triggers gives you better performance, but triggers are the better choice when dealing with complex data validation. Another use for a DML trigger is to make changes in another table based on what is about to happen within the original triggered table. For example, when you add an order, you would create a DML trigger that would reduce the number of that item in stock. Finally, DML triggers can be used to create an automated audit trail that generates a change history for each record.

We can create separate triggers for any table action except SELECT, or triggers that will fire on any combination of table actions. Obviously, as no table modifications occur on a SELECT statement, it is impossible to create such a trigger. There are three main types of triggers:

• INSERT trigger
• DELETE trigger
• UPDATE trigger

You can also have a combination of the three types of triggers.

Triggers can update tables within other databases if desired, and it is also possible for triggers to span servers as well, so don’t think the scope of triggers is limited to the current database.

It is possible for a trigger to fire a data modification, which in turn will execute another trigger, which is known as a nested trigger. For example, imagine you have Table A, which has a trigger on it to fire a modification within Table B, which in turn has a trigger on it that fires a modification within Table C. If a modification is made to Table A, then Table A’s trigger will fire, modifying the data in Table B, which will fire the trigger in Table B, thus modifying data in Table C. This nesting of triggers can go up to 32 triggers deep before you reach the limit set within SQL Server; however, if you start getting close to that sort of level, you either have a very complex system, or perhaps you have been overly zealous with your creation of triggers!

It is possible to switch off any nesting of triggers so that when one trigger fires, no other trigger can fire; however, this is not usually the norm. Be aware that your performance will suffer greatly when you start using nested triggers; use them only when necessary.

Note: There is one statement that will stop a DELETE trigger from firing. If you issue a TRUNCATE TABLE T-SQL command, it is as if the table has been wiped without any logging. This also means that a DELETE trigger will not fire, as it is not a deletion per se that is happening.

As with stored procedures, do take care when building triggers: you don’t want to create a potentially endless loop in which a trigger causes an update, which fires a trigger already fired earlier within the loop, thereby repeating the process.

CREATE TRIGGER Syntax for DML triggers

The creation of a trigger through T-SQL code can be quite complex if you use the full trigger syntax. However, the reduced version that I cover here is much more manageable and easier to demonstrate. When building a trigger, it can be created for a single action or for multiple actions. To expand on this, a trigger can be for insertion of a record only, or it could cover inserting and updating the record.

Note: Although this chapter will demonstrate DML triggers on tables, a trigger can also be placed on a view as well, so that when data is modified through a view, it too can fire a trigger if required.

Here is the syntax for creating a basic trigger:

CREATE TRIGGER trigger_name
ON {table|view}
[WITH ENCRYPTION]
{
{{FOR {AFTER|INSTEAD OF} {[INSERT] [,] [UPDATE] [,] [DELETE]}
AS
[{IF [UPDATE (column)
[{AND|OR} UPDATE (column)] ] ]
COLUMNS_UPDATE()]
sql_statements}}

Let’s explore the options in this syntax more closely:

• CREATE TRIGGER trigger_name: First of all, as ever, you need to inform SQL Server what you are attempting to do, and in this instance, you wish to create a trigger. The name for the trigger must also follow the SQL Server standards for naming objects within a database. In this chapter, you name the triggers starting with tg to indicate the object is a trigger, followed by the type of trigger (ins for insert, del for delete, and upd for update), and then the name of the root table the trigger will be associated with.
• ON {table|view}: It is then necessary to give the name of the single table or view that the trigger relates to, which is named after the ON keyword. Each trigger is attached to one table only.
• [WITH ENCRYPTION]: As with views and stored procedures, you can encrypt the trigger using the WITH ENCRYPTION options so that the code cannot be viewed by prying eyes.
• {FOR|AFTER|INSTEAD OF}:
• The FOR|AFTER trigger will run the code within the trigger after the underlying data is modified. Therefore, if you have any constraints on the table for cascading changes, then the table and these cascades will complete before the trigger fires. You either specify FOR or AFTER.
• INSTEAD OF: The most complex of the three options to understand as a trigger defined with this option will run the T-SQL within the trigger rather than allowing the data modification to run. This includes any cascading. To clarify, if you have an INSTEAD OF trigger that will execute on a data INSERT, then the insertion will not take place.
• {[INSERT] [,] [UPDATE] [,] [DELETE]}: This section of the syntax determines on what action(s) the trigger will execute. This can be an INSERT, an UPDATE, or a DELETE T-SQL command. As mentioned earlier, the trigger can fire on one, two, or three of these commands, depending on what you wish the trigger to do. Therefore, at this point, you need to mention which combination of commands, separated by a comma, you wish to work with.
• AS: The keyword AS defines that the trigger code has commenced, just as the AS keyword defined the start of a stored procedure. After all, a trigger is just a specialized stored procedure.
• [{IF UPDATE (column) [{AND|OR} UPDATE (column)] ]: This option can be used within a trigger that is not available within a stored procedure, and that is the test to check whether a specific column has been modified or not. This happens through the use of the UPDATE() keyword. By placing the name of the column to test in between the parentheses, a logical TRUE or FALSE will be returned depending on whether the column has been updated or not. The deletion of a record will not set the UPDATE test to TRUE or FALSE, as you are removing an item and not updating it. An INSERT or an UPDATE record manipulation will set the UPDATE test to the necessary value.
• COLUMNS_UPDATE(): This has functionality similar to UPDATE(), but instead of testing a specific named column, it tests multiple columns in one test.
• sql_statements: At this point you code the trigger just like any other stored procedure.

The main thought that you must keep in mind when building a trigger is that a trigger fires after each record is flagged to be modified, but before the modification is actually placed into the table. Therefore, if you have a statement that updates many rows, the trigger will fire after each record is flagged, not when all the records have been dealt with.

Note: Keep in mind, the FOR trigger executes before the underlying data is modified; therefore, a trigger can issue a ROLLBACK for that particular action if so desired.

Now that you know how to create a trigger, we’ll look at which situations they best apply to, as opposed to constraints.

Why Not Use a Constraint?

There is nothing stopping you from using a constraint to enforce a business rule, and in fact, constraints should be used to enforce data integrity. Constraints also give you better performance than triggers. However, they are limited in what they can achieve and what information is available to them to complete their job.

Triggers are more commonly used for validation of business rules, or for more complex data validation, which may or may not then go on to complete further updates of data elsewhere within SQL Server.

A constraint is only able to validate data that is within the table the constraint is being built for or a specified value entered at design time. This is in contrast to a trigger, which can span databases, or even servers, and check against any data set at design time or built from data collected from other actions against any table. This can happen if the necessary access rights are given to all objects involved.

However, constraints are the objects to use to ensure that data forming a key is correct, or when referential integrity needs to be enforced through a foreign key constraint.

At times a fine line will exist between building a constraint and a trigger, when the trigger is meant to perform a very simple validation task. In this case, if the decision deals with any form of data integrity, then use a constraint, which will give you better performance than using a trigger. If the object to be built is for business rules and may require complex validation, needs to handle multiple databases or servers, or requires advanced error handling, then build a trigger. For example, a trigger must be used if you need a change on one table to result in an action (update, delete, etc.) on a table that is located in another database. You might have this situation if you keep an audit trail (change history) database separate from your production database. It is doubtful that you would want to use a trigger if you are doing something simple like verifying that a date field only contains values within a certain range.

--

Robin Dewson has been hooked on programming ever since he bought his first computer, a Sinclair ZX80, in 1980. He has been working with SQL Server since version 6.5 and Visual Basic since version 5. Robin is a consultant mainly in the city of London, where he has been for nearly eight years. He also has been developing a rugby-related website as well as maintaining his own site at www.fat-belly.com.