Transact-SQL User's Guide

Home

Transact-SQL User's Guide

Chapter 16 Triggers: Enforcing Referential Integrity

Rules associated with triggers

Apart from anticipating the effects of a multirow data modification, trigger rollbacks, and trigger nesting, consider the following factors when you are writing triggers.

Triggers and permissions

A trigger is defined on a particular table. Only the owner of the table has create trigger and drop trigger permissions for the table. These permissions cannot be transferred to others.

Adaptive Server will accept a trigger definition that attempts actions for which you do not have permission. The existence of such a trigger aborts any attempt to modify the trigger table because incorrect permissions will cause the trigger to fire and fail. The transaction will be canceled. You must rectify the permissions or drop the trigger.

For example, Jose owns salesdetail and creates a trigger on it. The trigger is supposed to update titles.total_sales when salesdetail.qty is updated. However, Mary is the owner of titles, and has not granted Jose permission on titles. When Jose tries to update salesdetail, Adaptive Server detects the trigger and Jose's lack of permissions on titles, and rolls back the update transaction. Jose must either get update permission on titles.total_sales from Mary or drop the trigger on salesdetail.

Trigger restrictions

Adaptive Server imposes the following limitations on triggers:

A table can have a maximum of three triggers: one update trigger, one insert trigger, and one delete trigger.
Each trigger can apply to only one table. However, a single trigger can apply to all three user actions: update, insert, and delete.
You cannot create a trigger on a view or on a temporary table, though triggers can reference views or temporary tables.
The writetext statement will not activate insert or update triggers.
Although a truncate table statement is, in effect, like a delete without a where clause, because it removes all rows, it cannot fire a trigger, because individual row deletions are not logged.
You cannot create a trigger or build an index or a view on a temporary object (@object)
You cannot create triggers on system tables. If you try to create a trigger on a system table, Adaptive Server returns an error message and cancels the trigger.
You cannot use triggers that select from a text column or an image column of the inserted or deleted table.
If Component Integration Services is enabled, triggers have limited usefulness on proxy tables because you cannot examine the rows being inserted, updated, or deleted (via the inserted and deleted tables). You can create a trigger on a proxy table, and it can be invoked. However, deleted or inserted data is not written to the transaction log for proxy tables because the insert is passed to the remote server. Hence, the inserted and deleted tables, which are actually views to the transaction log, contain no data for proxy tables.

Implicit and explicit null values

The if update(column_name) clause is true for an insert statement whenever the column is assigned a value in the select list or in the values clause. An explicit null or a default assigns a value to a column, and thus activates the trigger. An implicit null does not.

For example, suppose you create the following table:

create table junk 
(a int null, 
 b int not null)

and then write the following trigger:

create trigger junktrig 
on junk 
for insert 
as 
if update(a) and update(b) 
        print "FIRING"

    /*"if update" is true for both columns.
      The trigger is activated.*/ 
insert junk (a, b) values (1, 2)

    /*"if update" is true for both columns.
      The trigger is activated.*/
insert junk values (1, 2)

    /*Explicit NULL: 
      "if update" is true for both columns. 
      The trigger is activated.*/
insert junk values (NULL, 2)

    /* If default exists on column a,   
      "if update" is true for either column.   
      The trigger is activated.*/ 
insert junk (b) values (2)

    /* If no default exists on column a,   
      "if update" is not true for column a.   
      The trigger is not activated.*/ 
insert junk (b) values (2)

The same results would be produced using only the clause:

if update(a)

To create a trigger that disallows the insertion of implicit nulls, you can use:

if update(a) or update(b)

SQL statements in the trigger can then test to see if a or b is null.

Triggers and performance

In terms of performance, trigger overhead is usually very low. The time involved in running a trigger is spent mostly in referencing other tables, which may be either in memory or on the database device.

The deleted and inserted trigger test tables are always in active memory. The location of other tables referenced by the trigger determines the amount of time the operation takes.

For more information on how triggers affect performance, see the Performance and Tuning Guide.

set commands in triggers

You can use the set command inside a trigger. The set option you invoke remains in effect during execution of the trigger. Then, the trigger reverts to its former setting.

Renaming and triggers

If you change the name of an object referenced by a trigger, you must drop the trigger and re-create it so that its source text reflects the new name of the object being referenced. Use sp_depends to get a report of the objects referenced by a trigger. The safest course of action is to not rename any tables or views that are referenced by a trigger.

Trigger tips

Consider the following tips when creating triggers:

Suppose you have an insert or update trigger that calls a stored procedure, which in turn updates the base table. If the nested triggers configuration parameter is set to true, the trigger will enter an infinite loop. Before executing an insert or update trigger, set sp_configure "nested triggers" to false.
When you execute drop table, any triggers dependent on that table are also dropped. If you want to preserve any such triggers, change their names with sp_rename before dropping the table.
Use sp_depends to see a report on the tables and views referred to in a trigger.
Use sp_rename to rename a trigger.
A trigger fires only once per query (a single data modification such as an insert or update). If the query is repeated in a loop, the trigger fires as many times as the query is repeated.


	Expand Search