Programming in MS SQL Server 2012

Limitation of Trigger

01. CREATE TRIGGER must be the first statement in the batch and can apply to only one table.
02. A trigger is created only in the current database; however, a trigger can reference objects outside the current database.
03. If the trigger schema name is specified to qualify the trigger, qualify the table name in the same way.
04. The same trigger action can be defined for more than one user action (for example, INSERT and UPDATE) in the same CREATE TRIGGER statement.
05. INSTEAD OF DELETE/UPDATE triggers cannot be defined on a table that has a foreign key with a cascade on DELETE/UPDATE action defined.
06. Any SET statement can be specified inside a trigger. The SET option selected remains in effect during the execution of the trigger and then reverts to its former setting. When a trigger fires, results are returned to the calling application, just like with stored procedures. To prevent having results returned to an application because of a trigger firing, do not include either SELECT statements that return results or statements that perform variable assignment in a trigger. A trigger that includes either SELECT statements that return results to the user or statements that perform variable assignment requires special handling; these returned results would have to be written into every application in which modifications to the trigger table are allowed. If variable assignment must occur in a trigger, use a SET NOCOUNT statement at the start of the trigger to prevent the return of any result sets.
07. Although a TRUNCATE TABLE statement is in effect a DELETE statement, it does not activate a trigger because the operation does not log individual row deletions. However, only those users with permissions to execute a TRUNCATE TABLE statement need be concerned about inadvertently circumventing a DELETE trigger this way.
08. The WRITETEXT statement, whether logged or unlogged, does not activate a trigger.
The following Transact-SQL statements are not allowed in a DML trigger:

ALTER DATABASE	CREATE DATABASE	DROP DATABASE
LOAD DATABASE	LOAD LOG	RECONFIGURE
RESTORE DATABASE	RESTORE LOG	-

More Reference URL: http://technet.microsoft.com/en-us/library/ms189799%28v=SQL.105%29.aspx

* * * * *

Your Comment To AUTHOR