Programming Triggers

Almost any Transact-SQL code that can be written as a batch can be used to create a trigger, except for the following:

Encrypting Trigger Definitions

If you are creating a trigger and you want to make sure that the trigger definition cannot be viewed by other users, you can use the WITH ENCRYPTION clause. The trigger definition is then stored in an unreadable form.

Once encrypted, the definition of the trigger cannot be decrypted and cannot be viewed by anyone, including the owner of the trigger or the system administrator.

SET Statement

When an ODBC application connects to Microsoft® SQL Server™, the server automatically sets these options for the session:

• SET QUOTED_IDENTIFIER ON
• SET TEXTSIZE 2147483647
• SET ANSI_DEFAULTS ON
• SET CURSOR_CLOSE_ON_COMMIT OFF
• SET IMPLICIT_TRANSACTIONS OFF

These settings increase the portability of ODBC applications. Because DB-Library-based applications generally do not set these options, triggers should be tested with the SET options listed above turned both on and off. This ensures that the triggers work correctly regardless of the options a particular connection may have set when it invokes the trigger. A trigger that requires a particular setting for one of these options should issue a SET statement at the start of the trigger. This SET statement will remain in effect only for the execution of the trigger; when the trigger completes, the original setting is restored.

Testing for Changes to Specific Columns

The IF UPDATE (column_name) clause in the definition of a trigger can be used to determine if an INSERT or UPDATE statement affected a specific column in the table. The clause evaluates to TRUE whenever the column is assigned a value.

Note Because a specific value in a column cannot be deleted using the DELETE statement, the IF UPDATE clause does not apply to the DELETE statement.

Alternatively, the IF COLUMNS_UPDATED() clause can be used to check which columns in a table were updated by an INSERT or UPDATE statement. This clause uses an integer bitmask to specify the columns to test. For more information, see CREATE TRIGGER.

Examples

The following example creates an INSERT trigger my_trig on table my_table and tests whether column b was affected by any INSERT statements.

CREATE TABLE my_table

(a int NULL, b int NULL)

GO

CREATE TRIGGER my_trig

ON my_table

FOR INSERT

AS

IF UPDATE(b)

    PRINT 'Column b Modified'

GO

You obtain similar results using the COLUMNS_UPDATED() clause:

CREATE TRIGGER my_trig2

ON my_table

FOR INSERT

AS

IF ( COLUMNS_UPDATED() & 2 = 2 )

    PRINT 'Column b Modified'

GO

Deferred Name Resolution

SQL Server allows triggers to refer to tables that do not exist at trigger creation time. This is called deferred name resolution. For more information about deferred name resolution, see Deferred Name Resolution and Compilation.

Note If an object referenced by a trigger is deleted or renamed, then an error is returned when the trigger is executed. However, if an object referenced in a trigger is replaced with an object of the same name, the trigger executes without having to be re-created. For example, if trigger trig1 references table test1, and test1 is deleted and a different table called test1 is created, trig1 now references the new table.

Returning Results

It is recommended that a trigger not return any results because special handling for these returned results must be written into every application in which modifications to the trigger table are allowed. To prevent any results from being returned from a trigger, do not include either SELECT statements or variable assignments in the definition of the trigger. If variable assignment must occur in a trigger, use a SET NOCOUNT statement at the beginning of the trigger to eliminate the return of any result sets.

See Also