Udostępnij za pośrednictwem

Implementing DML Triggers

  • Artykuł

Before you create a DML 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 DML triggers defaults to the table owner, who cannot transfer it to other users.
  • DML triggers are database objects, and their names must follow the rules for identifiers.
  • You can create a DML trigger only in the current database, although a DML trigger can reference objects outside of the current database.
  • A DML trigger cannot be created on a temporary or system table, although DML triggers can reference temporary tables. System tables should not be referenced; use the Information Schema Views instead.
  • 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.

Important

The ability to return result sets from triggers will be removed in a future version of SQL Server. Triggers that return result sets may cause unexpected behavior in applications that are not designed to work with them. 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 in SQL Server 2005, 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.

When you create a DML 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 DML 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

Triggers are created in the schema of the table or view on which they are defined. For example, if trigger Trigger1 is created on table HumanResources.Employee, the trigger's schema-qualified name is HumanResources.Trigger1.

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 Ownership Chains.

To create a trigger