Creating a Trigger

Before creating a trigger consider that:

• The CREATE TRIGGER statement must be the first statement in the batch.
• 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 view, or temporary or system table, although triggers can reference views or temporary tables. System tables should not be referenced; use the Information Schema Views instead. For more information, see Information Schema Views.
• 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 creating a trigger, specify:

• The name.
• The table upon which the trigger is defined.
• 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 either INSERT or UPDATE statements.
• The programming statements that perform operations in the database.

Multiple Triggers

A table can have multiple 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 all three user actions (UPDATE, INSERT, and DELETE).

To create a trigger