SQL Server 2008 : Programming Objects - Implementing Triggers

A trigger is a T-SQL routine stored on the server that is executed automatically in response to an event within SQL Server. You can use triggers to evaluate data before or after a manipulation, to maintain data integrity, to control server operations and access, to audit a server, and to implement business logic. Unlike procedures and functions, you cannot execute triggers directly: they are attached to an object.

You can create three types of triggers: Data Manipulation Language (DML), Data Definition Language (DDL), and Logon triggers. DML triggers run in response to data manipulation events, such as inserting, updating, and deleting data. DDL triggers fire in response to server events, such as creating, altering, and dropping an object. Logon triggers fire in response to the LOGON event that is raised when a user session is being established. Whereas DDL and Logon trigger a response to events in a server, DML triggers only exist inside a table or a view. Triggers support recursive and nesting. Recursive occurs when a trigger calls itself inside its routine, and nesting takes place when a trigger executes a statement that fires another trigger.

Creating DML Triggers

A DML trigger fires as a response to the execution of INSERT, UPDATE, and DELETE statements. When you create a DML trigger, you attach it to a table or a view, and you define which event will elicit a response. Triggers can fire in two different ways: AFTER and INSTEAD OF.

You use an AFTER trigger when you want a trigger to fire only after SQL Server completes all actions successfully. For example, if you insert a row in a table, the trigger associated with the insert event in this table will fire only after the row passes all the checks, such as primary key, rules, and constraints. If your insert fails, SQL Server will not fire the trigger. You can create any number of AFTER triggers on a table, although you cannot use an AFTER trigger on views.

You use an INSTEAD OF trigger when you want to perform a different operation from the statement that fires that trigger. An INSTEAD OF fires before SQL Server starts the execution of the action, providing you the possibility to change the operations in your database. For example, imagine that a user cannot update a client’s data without your approval. So, you decide to create a table that will receive the proposed updates. To accomplish this task, you will then create an INSTEAD OF trigger on the UPDATE event of the client’s table, telling SQL Server that it will perform an INSERT on the other table. You can create only one INSTEAD OF trigger for each INSERT, UPDATE, or DELETE statement for a view or a table. Also, you cannot create INSTEAD OF DELETE and INSTEAD OF UPDATE triggers on a table that has a cascading foreign key defined.

Head of the Class...: Triggers or Declarative Referential Integrity? You can employ two ways to enforce referential integrity between tables: DML triggers and Declarative Referential Integrity (DRI). Although you can use both of them, specific situations govern the use of each. When a FOREIGN KEY constraint is used, DRI is enforced when you modify data. This enforcement occurs before this change becomes part of the table, and it is much more efficient than executing trigger code. However, when you must enforce referential integrity across databases, you cannot define a FOREIGN KEY. So, in this case, you must use triggers to achieve integrity.

The routine within a DML trigger can be composed by any T-SQL statements, except CREATE DATABASE, ALTER DATABASE, DROP DATABASE, LOAD DATABASE, LOAD LOG, RECONFIGURE, RESTORE DATABASE, and RESTORE LOG statements. In addition, the following T-SQL commands are not allowed inside the body of your DML trigger when it is used against the table or view that is the target of the triggering action:

• CREATE INDEX

• CREATE SPATIAL INDEX

• CREATE XML INDEX

• ALTER INDEX

• DROP INDEX

• DBCC DBREINDEX

• ALTER PARTITION FUNCTION

• DROP TABLE

• ALTER TABLE (when managing columns, switching partitions; or managing keys)

Although DML triggers support many operations, you cannot create triggers against system tables or dynamic management views. Moreover, the TRUNCATE TABLE statement does not fire a trigger because this operation does not log individual row deletions. Nor does the WRITETEXT statement, whether logged or unlogged, activate a trigger.

The general Transact-SQL syntax for creating a DML trigger is as follows:

CREATE TRIGGER [schema_name. ] trigger_name
ON {table | view }
[ WITH <dml_trigger_option> [,...n ] ]
{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [, ] [ UPDATE ] [, ] [ DELETE ] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS { sql_statement [ ; ] [,...n ] }
<dml_trigger_option> ::=
    [ ENCRYPTION ]
    [ EXECUTE AS Clause ]

Like other SQL Server objects, triggers must have a unique name that conforms to the rules of object identifiers. In the CREATE TRIGGER statement, you use the ON clause to specify the table or view to which the trigger will be attached. If you drop a table or a view, any triggers that are attached to that object will be dropped as well.

As in functions and procedures, the WITH clause of a trigger has the ENCRYPTION and EXECUTE AS options. You specify the ENCRYPTION option when you want to encrypt the trigger definition. The EXECUTE AS option specifies the security context under which the trigger is executed.

The AFTER and the INSTEAD OF clauses specify the fire mode of the trigger, as well as the event (or events) that will execute the trigger. You can define more than one event for a trigger. The FOR clause present in the syntax came from SQL Server 7.0 and older versions. It works in the same way as the AFTER clause, but you should use it only for compatibility. Another compatibility clause is the WITH APPEND, which is used to specify that an additional trigger of an existing type should be added. This works only in SQL Server 6.5 and below. You can use the NOT FOR REPLICATION clause if you want to indicate that your trigger should not be executed when a replication agent modifies the table to which the trigger is attached.

After the AS clause, you write the T-SQL routine that the trigger will perform when fired by the events specified earlier, on the AFTER/INSTEAD OF clause.

Configuring & Implementing...: Using Inserted and Deleted Tables DML trigger statements can use two special tables that are dynamically created and managed by SQL Server: the deleted table and the inserted table. These temporary tables reside in memory, and they have the same column definition of the table on which the trigger was created. You can use them to test the effects of certain data modifications and to set conditions for DML trigger actions. The deleted table stores copies of the affected rows when you run a DELETE or an UPDATE statement. During the execution of one of these statements, rows are deleted from the trigger table and transferred to the deleted table. The deleted table and the trigger table ordinarily have no rows in common. The inserted table stores copies of the affected rows when you execute an INSERT or an UPDATE statement. During one of these transactions, new rows are added to both the inserted table and the trigger table. The rows in the inserted table are copies of the new rows in the trigger table. An update transaction is similar to a delete operation followed by an insert operation; the old rows are copied to the deleted table first; and then the new rows are copied to the trigger table and to the inserted table. These dynamic tables can be used in many ways. For example, you can enforce business rules, extend referential integrity between tables, or manipulate this data in the way you want.

Example: Creating and Consuming a DML Trigger

Let’s use the Product table from the AdventureWorks2008 database. This table has two important columns responsible for the stock of a product: SafetyStockLevelReorderPoint. Your business requirements determine that the value of the ReorderPoint column will be 80 percent of the SafetyStockLevel column value. and

As a database developer, you decide to create a DML trigger to enforce this business logic. With this object, you intend to automatically fill the ReorderPoint value every time that a user inserts or updates a row. Figure 1 shows how you can create a trigger that updates the ReorderPoint value after every insert or update operations in the table Product. When you create a DML trigger, it is stored in the active database, inside the folder Triggers, set under the table to which the trigger is attached.

Figure 1. The Creation Syntax of the DML Trigger

As you can see, the new trigger has the name t_CalcReorderPoint. It will be attached to the table Product, and it will fire on insert and update events. Once created, you can fire this trigger using INSERT or UPDATE against the table Product. Figure 2 shows an example of the column values before and after an update.

Figure 2. The DML Trigger in Action

Creating DDL Triggers

Since version 2005, SQL Server has provided the ability to create triggers for DDL operations. These operations primarily correspond to T-SQL CREATE, ALTER, and DROP statements and certain system stored procedures that perform DDL-like operations. For example, if you use the CREATE LOGIN statement or the sp_addlogin stored procedure, they will both fire a DDL trigger that you created on a CREATE_LOGIN event. You can use FOR/AFTER clause in DDL triggers. However, INSTEAD OF cannot be used.

You can use this type of trigger to monitor and control actions performed on the server, and also audit these operations. DDL triggers can be used for administrative tasks such as auditing and regulating database operations.

New & Noteworthy...:SQL Server Audit In SQL Server 2005, DDL triggers brought a new level of monitoring and regulating database and server operations. These triggers, along with other features, allow you to audit your server. SQL Server 2008 has a new feature called AUDIT. This feature realizes automatic auditing on the server or a database, and involves tracking and logging events that occur on the system. SQL Server Audit provides the tools and processes to enable, store, and view audits on your server and database objects. Although the audit feature is a great one, it is only available on SQL Server 2008 Enterprise Edition, and it consumes resources. So, you must verify the business requirements and decide which feature you will use to monitor your server.

The general Transact-SQL syntax for creating a DDL trigger is as follows:

CREATE TRIGGER trigger_name
ON { ALL SERVER | DATABASE }
[ WITH <ddl_trigger_option> [,...n ] ]
{ FOR | AFTER } { event_type |event_group } [,...n ]
AS { sql_statement [ ; ] [,...n] }
<ddl_trigger_option> ::=
    [ ENCRYPTION ]
    [ EXECUTE AS Clause ]

Like DML triggers, DDL triggers must have a unique name that conforms to the rules of object identifiers. You use the ON clause to specify the scope of the trigger. If you use the DATABASE option, the scope of your DDL trigger will be the current database. If you use the ALL SERVER option, the scope of your DDL triggers to the current server.

The WITH clause of a trigger has the ENCRYPTION and EXECUTE AS options. You specify the ENCRYPTION option when you want to encrypt the trigger definition. The EXECUTE AS option specifies the security context under which the trigger is executed.

The AFTER clause specifies the event type and group that will fire the trigger. An event type is the name of the event that fires your DDL trigger. An event group is the name of a predefined grouping of T-SQL language events. You combine these two parts to define the firing event. For example, if you create a trigger that will fire when a user creates a table, the event type will be CREATE and the event group will be TABLE.

Exam Warning

DDL triggers fire only after SQL Server processes the event type and group defined in the CREATE TRIGGER statement. So, remember that if the execution of this event fails, the trigger will not fire.

After the AS clause, you write the T-SQL routine that the trigger will perform when fired by the events specified before, on the AFTER clause.

Example: Creating and Consuming a DDL Trigger

To protect the accidental dropping of tables in the AdventureWorks2008 database, you decide to create a DDL trigger that will warn the user about table protection, and will roll back the operation. Figure 3 shows the code of the DDL trigger and a DROP statement that tries to drop a table.

Figure 3. The DDL Trigger Syntax and the Execution of a DROP Statement

As the figure shows, the new trigger has the name tddl_ProtectTable, its scope will be the active database (AdventureWorks2008), and it will fire on DROP_TABLE events. You can also see that a DROP TABLE statement fails because of the created trigger.

When you create a DDL trigger, it is stored according to the scope defined in the trigger. If the scope is DATABASE, the trigger will be stored inside the folder Triggers, set under the Programmability folder inside the active database. If the scope is ALL SERVER, the trigger will be stored inside the folder Triggers, set under the Server Objects folder inside the server.

Creating Logon Triggers

Logon triggers fire in response to the LOGON event that is raised when a user session is being established with your server—after the authentication phase finishes, but before the user session is actually established. Therefore, all messages that you define inside the trigger to users, such as error messages, will be redirected to the SQL Server error log. Logon triggers do not fire if authentication fails. You can use logon triggers to audit and control server sessions, such as to track login activity or limit the number of sessions for a specific login.

The general T-SQL syntax for creating a logon trigger is as follows:

CREATE TRIGGER trigger_name
ON ALL SERVER
[ WITH <logon_trigger_option> [,...n] ]
{ FOR | AFTER } LOGON
AS { sql_statement [ ; ] [,...n ] [ ; ] }
<logon_trigger_option> ::=
     [ ENCRYPTION ]
     [ EXECUTE AS Clause ]

Example: Creating and Consuming a Logon Trigger

In this example, you will protect your server from a logon of a specific login in your server. To accomplish that, you decide to create a logon trigger that will block the logon process after its authentication, executing a rollback in the operation. Figure 4 shows the code of the logon trigger that denies the logon process.

Figure 4. The Logon Trigger Syntax

The figure shows that the new trigger has the name tl_NoAccess, its scope will be the whole server, and it will fire on LOGON events. You can also see that a ROLLBACK will be executed if the login is Herleson. All logon triggers are stored inside the folder Triggers, set under the Server Objects folder inside the server. You can fire this trigger trying to establish a new connection with the server. Figure 5 shows the error generated by SQL Server when you try to connect using the blocked login.

Figure 5. The Error Message

Generated by SQL Server for the Specific Login

Head of the Class...: Logon Trigger Blocks All Login Attempts Since Logon Triggers can block the access to an instance, sometimes you can face an unexpected trouble: you develop a Logon Trigger that should restrict the access for some logins. However, because of a mistake in the code, it blocks all logon attempts, including sysadmin logins. So, how can you regain the access to your SQL Server instance and disable this trigger? The solution relies on the DAC feature. The Dedicated Administrator Connection (DAC) allows you to access a running instance of SQL Server Database Engine to troubleshoot problems on the server. Even when the server is unresponsive to other client connections or it’s blocking the access, you can use DAC to connect and execute restoring operations. The DAC is available through the sqlcmd utility and SQL Server Management Studio. So, in situations that a Logon Trigger works in an unexpected way, use DAC to connect to the instance and disable it. Then, connect to the instance normally and see what the problem of this trigger is.

Using Recursive and Nested Triggers

A trigger fires in response to an operation (DML, DDL, or Logon), and it can perform additional operations. Because of that, it can cause itself to fire, or it can fire additional triggers in a chain reaction.

A trigger that fires itself is called a recursive trigger. You can create two types of recursive triggers: direct and indirect recursion. A direct recursion occurs when your application updates table A. This operation fires a trigger that executes an update in table A. Because table A was updated, the trigger will fire again and again. An indirect recursion occurs when your application updates table A. This fires a trigger that updates table B. But table B has a trigger that updates table A. Then the trigger in table B will fire and update table A.

A trigger that fires additional triggers in a chain reaction is called a nested trigger. A nested trigger happens when your trigger changes a table on which there is another trigger. Then the second trigger is activated and calls a third trigger, and so on. If any of your triggers in the chain set off an infinite loop, the nesting level is exceeded and the trigger is canceled. A trigger can be nested to a maximum of 32 levels. Also, an indirect recursion is a subset of a nested trigger.

You can enable or disable direct recursive triggers using the RECURSIVE_TRIGGERS setting of a database. To enable or disable nested triggers and indirect recursive triggers, you can use the nested triggers option of sp_configure procedure.

Managing Triggers

As a database developer, you need to learn some basics about managing triggers, such as alter and drop them. Basically, there are two ways to manage these triggers: using the SQL Server Management Studio (SSMS) or using Transact-SQL statements.

In SQL Server Management Studio, you can alter the code of a DML trigger by right-clicking it and choosing Modify to open a new query tab. This tab shows the actual definition of the trigger and allows you to make changes at its code. After you have finished the changes, commit them by clicking on the Execute button. DDL and Logon triggers do not have the Modify option. Also, to drop a trigger, right-click the desired object and choose the Delete option. You can see the objects that depend on a function and the objects that a function depends on by right-clicking it and choosing the View Dependencies option.

You can also manage procedures using Transact-SQL statements. You can alter and remove a trigger using the ALTER TRIGGER and the DROP TRIGGERALTER TRIGGER alters an existing trigger that you previously created. Its syntax and options are the same as the CREATE TRIGGER statement. The DROP TRIGGER removes one or more triggers from the current database. statements. The

You can also use system stored procedures and catalog views to provide information about triggers and their definitions. The following example shows you how to retrieve the definition and the information of the trigger t_CalcReorderPoint using sp_help and sp_helptext :

--View the definition
sp_helptext uspNewProductCategory
--View the information
sp_help  uspNewProductCategory

You can also enable or disable a trigger. Disabling a trigger is useful in some scenarios, such as when you are executing a bulk operation. The following code shows the syntax of enabling or disabling a trigger.

DISABLE | ENABLE TRIGGER { [ schema_name. ] trigger_name [,...n ] | ALL }
ON { object_name| DATABASE | ALL SERVER } [ ; ]

Another important management task with triggers is to define their execution order, in scenarios that multiple triggers will fire with the same event. For example, you create four triggers against a table that will be executed at every update operation. When you set an order, you define which trigger will be fired first and which will be fired last. The other two triggers that are fired between the first and last triggers will be executed in undefined order. You can set the order only for the AFTER trigger. To specify the order of a trigger, you use the sp_settriggerorder procedure. The general syntax is as follows:

sp_settriggerorder [ @triggername = ] '[ triggerschema. ] triggername '
      , [ @order = ] 'value'
      , [ @stmttype = ] 'statement_type'
      [, [ @namespace = ] { 'DATABASE' | 'SERVER' | NULL } ]

After specifying the trigger’s name, you define if this trigger will be the first or the last in the @order argument. Then, you state which event fires the trigger, at the @stmttype argument. When you specify a DDL trigger, you must inform the scope of this trigger, DATABASE or SERVER in the @namespace argument. If your trigger is a logon trigger, SERVER must be specified. DML triggers do not need this argument defined. The following two examples show how you can define the order of a DML and a DDL trigger:

--Seting the order of a DML trigger
sp_settriggerorder @triggername='dbo.t_CalcReorderPoint',
@order='Last', @stmttype='UPDATE';
--Seting the order of a DDL trigger
sp_settriggerorder @triggername='tddl_ProtectTable',
@order='First', @stmttype='DROP_TABLE', @namespace='DATABASE';

1. Creating Triggers

You will create a trigger that will block inserts and deletes operations in the table ProductCategory. You then will set its order.

1. Launch SQL Server Management Studio (SSMS), connect to the instance, open a new query window, and change the context to the AdventureWorks2008 database.

2. Execute the following statement to view the rows of the ProductCategory table.

   SELECT * FROM [Production].[ProductCategory]

3. Create the trigger t_ProtectTable by executing the following code:

   CREATE TRIGGER [dbo].[t_ProtectTable]
   ON [Production].[ProductCategory]
   AFTER INSERT, DELETE
   AS
      PRINT 'This table only accepts update operations'
      ROLLBACK

4. Try to delete a product category using the DELETE statement, as follows:

   DELETE FROM [Production].[ProductCategory]

5. Execute the following statement to see that no row of the ProductCategory table was deleted.

   SELECT * FROM [Production].[ProductCategory]

6. Set the order of this trigger using the sp_settriggerorder procedure, as follows:

   sp_settriggerorder @triggername='dbo.t_ProtectTable',
   @order='First', @stmttype='INSERT';
   sp_settriggerorder @triggername='dbo.t_ProtectTable',
   @order='First', @stmttype='DELETE';