Programming DML Triggers
Almost any Transact-SQL statement that can be written as a batch can be used to create a DML trigger, except for the following statements.
The LOAD DATABASE and LOAD LOG statements are included in SQL Server 2008 for backward compatibility only, and may not be supported in the future.
In addition, the following Transact-SQL statements are not allowed inside the body of a DML trigger when used against the table or view that is the target of the triggering action:
ALTER PARTITION FUNCTION
ALTER TABLE when used to:
To convert the original text of the CREATE TRIGGER statement to an obfuscated format, use the WITH ENCRYPTION option. The output of the obfuscation is not directly visible in any of the system tables or views in SQL Server 2008: users without access to system tables, system views, or database files cannot retrieve the obfuscated text. However, the text is available to privileged users with direct access to database files. These users may be able to reverse engineer the obfuscation to retrieve the original text of the trigger definition.
When an ODBC application connects to 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 set to 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 remains in effect only for the execution of the trigger; when the trigger completes, the original setting is restored.
The UPDATE() function can be used to determine if an INSERT or UPDATE statement affected a specific column in the table. The function returns TRUE whenever the column is assigned a value.
Because a specific value in a column cannot be deleted using the DELETE statement, using an IF UPDATE() clause does not apply to the DELETE statement.
Alternatively, the COLUMNS_UPDATED function can be used to check which columns in a table were updated by an INSERT or UPDATE statement. This function uses an integer bitmask to specify the columns to test. For more information, see CREATE TRIGGER.
A. Using an IF UPDATE() clause to test data modifications
The following example creates an INSERT trigger my_trig on table my_table and tests whether column b was affected by any INSERT attempts.
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
B. Using the COLUMNS UPDATED function to test data modifications
The following example obtains 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
DML triggers can 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.
If an object referenced by a DML trigger is deleted or renamed, an error is returned when the trigger is executed. However, if an object referenced in a DML 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.
It is recommended that a DML trigger not return any results. This is 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 DML 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 before the trigger to eliminate the return of any result sets.
The ability to return result sets from triggers will be removed in a future version of SQL Server. Avoid returning result sets from triggers in new development work, and plan to modify applications that currently do this. To prevent triggers from returning result sets, set the disallow results from triggers Option to 1. The default setting of this option will be 1 in a future version of SQL Server.