Trigger exceptions in VS .NET

Drawer PDF 417 in VS .NET Trigger exceptions

You can find the full list of commands and constructs that are not allowed within a trigger in the SQL Server 2005 Books Online article CREATE TRIGGER (Transact-SQL).

SQL Server does not support triggers against system objects such as system tables and dynamic management views. Also, triggers will fire only in response to logged operations. Minimally logged operations such as TRUNCATE and WRITETEXT do not cause a trigger to fire.

You can use triggers to enforce referential integrity. However, you should not use triggers in place of declarative referential integrity (DRI) via a FOREIGN KEY constraint. DRI is enforced when the modification is made, before the change is part of the table, and is much more efficient than executing trigger code. However, you cannot define FOREIGN KEY constraints across databases. To enforce referential integrity across databases, you must use triggers.

Triggers have access to two special tables that are dynamically generated: INSERTED and DELETED. INSERTED and DELETED tables are visible only within a trigger and cannot be accessed by any other construct such as a stored procedure or function. The structure of the INSERTED and DELETED tables exactly matches the column definition of the table on which the trigger was created. Therefore, you can reference columns by using the same name as the table for which the trigger was defined. When you execute an INSERT operation, the INSERTED table contains each row that was inserted into the table, whereas the DELETED table does not contain any rows. When you execute a DELETE statement, the DELETED table contains each row that

was deleted from the table, whereas the INSERTED table does not contain any rows. When you execute an UPDATE statement, the INSERTED table contains the after image of each row you updated, and the DELETED table contains the before image of each row that you updated. The before image is simply a copy of the row as it existed before you executed the UPDATE statement. The after image reflects the data in the row after the UPDATE statement has changed appropriate values. 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 ] | EXTERNAL NAME <method specifier [ ; ] > } <dml_trigger_option> ::= [ ENCRYPTION ] [ EXECUTE AS Clause ]

Every trigger must have a name that conforms to the rules for object identifiers. You use the ON clause to specify the table or view that the trigger will be created against. If the table or view is dropped, any triggers that were created against the table are also dropped. Using the WITH clause, you can do the following:

Specify whether the code in the trigger will be encrypted when it is created. Specify an execution context.

The FOR clause specifies whether the trigger is an AFTER or INSTEAD OF trigger as well as the event(s) that cause the trigger to fire. You can specify more than one event for a given trigger if you choose. Most people can ignore the WITH APPEND clause, which applies only to 65 compatibility mode, because most organizations should have upgraded their SQL Server 6.5 databases by now. The NOT FOR REPLICATION clause is covered in 19, Managing Replication. Following the AS clause, you specify the code that you want to execute when the trigger is fired.