Creating a Trigger
Before you create a trigger, consider that:
- The CREATE TRIGGER statement must be the first statement in the batch. All other statements that follow in that batch are interpreted as part of the definition of the CREATE TRIGGER statement.
- Permission to create triggers defaults to the table owner, who cannot transfer it to other users.
- Triggers are database objects, and their names must follow the rules for identifiers.
- You can create a trigger only in the current database, although a trigger can reference objects outside of the current database.
- A trigger cannot be created on a temporary or system table, although triggers can reference temporary tables. System tables should not be referenced; use the Information Schema Views instead. For more information, see Information Schema Views.
- INSTEAD OF DELETE and INSTEAD OF UPDATE triggers cannot be defined on a table that has a foreign key defined with a DELETE or UPDATE action.
- Although a TRUNCATE TABLE statement is like a DELETE statement without a WHERE clause (it deletes all rows), it does not cause DELETE triggers to fire because the TRUNCATE TABLE statement is not logged.
- The WRITETEXT statement does not cause the INSERT or UPDATE triggers to fire.
When you create a trigger, specify:
- The name.
- The table upon which the trigger is defined.
- When the trigger is to fire.
- The data modification statements that activate the trigger. Valid options are INSERT, UPDATE, or DELETE. More than one data modification statement can activate the same trigger. For example, a trigger can be activated by an INSERT and an UPDATE statement.
- The programming statements that perform the trigger action.
Multiple Triggers
A table can have multiple AFTER triggers of a given type provided they have different names; each trigger can perform numerous functions. However, each trigger can apply to only one table, although a single trigger can apply to any subset of three user actions (UPDATE, INSERT, and DELETE).
A table can have only one INSTEAD OF trigger of a given type.
Trigger Permissions and Ownership
CREATE TRIGGER permissions default to the table owner on which the trigger is defined, the sysadmin fixed server role, and members of the db_owner and db_ddladmin fixed database roles, and are not transferable.
If an INSTEAD OF trigger is created on a view, the ownership chain is broken if the view owner does not also own the base tables referenced by the view and trigger. For a base table not owned by the view owner, the table owner must separately grant the necessary permissions to anybody reading or updating the view. If the same user owns both the view and the underlying base tables, they have to grant other users permissions only on the view, not individual base tables. For more information, see Using Ownership Chains.
To create a trigger
Transact-SQL