Page 85 - DCAP508_DATABASE_ADMINISTRATION

Unit 5: SQL Server Databases

A trigger cannot be created on a temporary or system table, although triggers can reference Notes
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.
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.

Creating Triggers

To create a trigger, you can use any of the following tools given below:

1. Transact-SQL
2. Enterprise Manager
3. SQL-DMO

LOVELY PROFESSIONAL UNIVERSITY 79