Triggers

A trigger is a special type of stored procedure that is automatically invoked whenever the data in the table is modified.

Triggers are invoked in response to INSERT, UPDATE,or DELETE statements.

A trigger can query other tables and can include complex Transact-SQL statements.

The trigger and the statement that fires it are treated as a single transaction that can be rolled back from within the trigger. If a severe error is detected, the entire  transaction automatically rolls back.

Triggers are useful in these ways:

• Triggers can cascade changes through related tables in the database.

For example, a delete trigger on the title_id
column of the titles table causes a corresponding deletion of matching rows in
other tables, using the title_id column as a unique key to locate rows in titleauthor, sales, and roysched.

• Triggers can disallow or roll back changes that violate referential integrity, thereby canceling the attempted data modification.

Such a trigger might go into effect when you change a foreign key and the new value does not match its primary key. For example,
you can create an insert trigger on titleauthor.title_id that rolls back an
insert if the new value does not match some value in titles.title_id. (However,
foreign key constraints are usually used for this purpose.)

• Triggers can enforce restrictions that are more complex than those defined with CHECK constraints.

Unlike CHECK constraints, triggers can  reference columns in other tables. For example, a trigger can use a SELECT from another table to roll back updates that attempt to increase a book’s price by more than one percent of its advance.

• Triggers can also find the difference between the state of a table before and after a data modification and take action(s) based
on that difference.

• Multiple triggers of the same type (INSERT,
UPDATE, or DELETE) on a table allow multiple different actions to take place in
response to the same modification statement.

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.
• 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).

CREATE TRIGGER (T-SQL)

Creates a trigger, which is a special kind of
stored procedure that executes automatically when a user attempts the specified
data-modification statement on the specified table. Microsoft® SQL Server™ allows the creation of multiple triggers for any given INSERT, UPDATE, or DELETE statement.

Syntax

CREATE TRIGGER trigger_name

ON table [WITH ENCRYPTION]
{
{FOR { [DELETE] [,] [INSERT] [,] [UPDATE] }
[WITH APPEND]
[NOT FOR REPLICATION]

AS 

sql_statement [...n] 

} 

AS  

{ IF UPDATE (column) 

[{AND | OR} UPDATE (column)]  

[...n] 

| IF (COLUMNS_UPDATED() {bitwise_operator}
updated_bitmask)  

{ comparison_operator} column_bitmask [...n] 

} 

sql_statement [ ...n] 

} 

}

Arguments

trigger_name

Is the name of the trigger. A trigger name must conform to the rules for identifiers and must be unique within the database.
Specifying the trigger owner name is optional.

table

Is the table on which the trigger is executed; sometimes called the trigger table. Specifying the owner name of the table is
optional. Views cannot be specified.

WITH ENCRYPTION

Encrypts the syscomments entries that contain the text of CREATE TRIGGER.

{ [DELETE] [,] [INSERT] [,] [UPDATE] } | { [INSERT]
[,] [UPDATE]}

Are keywords that specify which data modification statements, when attempted against this table, activate the trigger. At least one option must be specified. Any combination of these in any order is allowed
in the trigger definition. If more than one option is specified, separate the options with commas.

WITH APPEND

Specifies that an additional trigger of an existing type should be added. Use of this optional clause is needed only when the compatibility level is less than or equal to 65. If the compatibility level is greater than or equal to 70, the WITH APPEND optional clause is not needed to add an additional trigger of an existing type (this is the default behavior of CREATE TRIGGER with the compatibility level setting greater than or equal to 70.)

NOT FOR REPLICATION

Indicates that the trigger should not be executed when a replication process modifies the table involved in the trigger.

AS  

Are the actions the trigger is to take.

sql_statement

Is the trigger condition(s) and action(s). Trigger conditions specify additional criteria that determine whether the attempted
DELETE, INSERT, or UPDATE statements cause the trigger action(s) to be carried out.

The trigger actions specified in the Transact-SQL statements go into effect when the user action (DELETE, INSERT, or UPDATE) is
attempted.

Triggers can include any number and kind of
Transact-SQL statements except SELECT. A trigger is designed to check or change
data based on a data modification statement; it should not return data to the
user. The Transact-SQL statements in a trigger often include control-of-flow
language.

A few special tables are used in CREATE TRIGGER statements: 

• deleted and inserted are logical (conceptual) tables. They are structurally similar to the table on which the trigger is
defined (that is, the table on which the user action is attempted) and hold the
old values or new values of the rows that may be changed by the user action.
For example, to retrieve all values in the deleted table, use:

SELECT *

FROM deleted

• In a DELETE, INSERT, or UPDATE trigger, SQL
Server does not allow text, ntext or image column references in the inserted
and deleted tables if the compatibility level is equal to 70. The text, ntext,
and image values in the inserted and deleted tables cannot be accessed. To
retrieve the new value in either an INSERT or UPDATE trigger, join the inserted
table with the original update table. When the compatibility level is 65 or
lower, null values are returned for inserted or deleted text, ntext, or image
columns that allow null values; zero-length strings are returned if the columns
are not nullable.

n

Is a placeholder indicating that multiple
Transact-SQL statements can be included in the trigger. For the IF UPDATE
(column) statement, multiple columns can be included by repeating the UPDATE
(column) clause.

IF UPDATE (column)

Tests for an INSERT or UPDATE action to a specified
column and is not used with DELETE operations. More than one column can be
specified. Because the table name is specified in the ON clause, do not include
the table name before the column name in an IF UPDATE clause. To test for an
INSERT or UPDATE action for more than one column, specify a separate
UPDATE(column) clause following the first one.

Note The IF UPDATE (column) clause functions
identically to an IF, IF...ELSE or WHILE statement and can use the BEGIN...END
block.

UPDATE(column) can be used anywhere inside the body
of the trigger.

column

Is the name of the column to test for either an
INSERT or UPDATE action. This column can be of any data type supported by SQL
Server.

IF (COLUMNS_UPDATED())

Tests, in an INSERT or UPDATE trigger only, whether
the mentioned column or columns were inserted or updated. COLUMNS_UPDATED
returns a varbinary bit pattern that indicates which columns in the table were
inserted or updated.

COLUMNS_UPDATED can be used anywhere inside the
body of the trigger.

bitwise_operator

Is the bitwise operator to use in the comparison.

updated_bitmask

Is the integer bitmask of those columns actually
updated or inserted. For example, table t1 contains columns C1, C2, C3, C4, and
C5. To check whether columns C2, C3, and C4 are all updated (with table t1
having an UPDATE trigger), specify a value of 14. To check whether only column
C2 is updated, specify a value of 2.

comparison_operator

Is the comparison operator. Use the equals sign (=)
to check whether all columns specified in updated_bitmask are actually updated.
Use the greater than symbol (>) to check whether any or some of the columns
specified in updated_bitmask are updated.

column_bitmask

Is the integer bitmask of those columns to check
whether they are updated or inserted.

Remarks

Triggers are often used for enforcing business
rules and data integrity. SQL Server provides declarative referential integrity
(DRI) through the table creation statements (ALTER TABLE and CREATE TABLE);
however, DRI does not provide cross-database referential integrity. To enforce
referential integrity (rules about the relationships between the primary and
foreign keys of tables), use primary and foreign key constraints (the PRIMARY
KEY and FOREIGN KEY keywords of ALTER TABLE and CREATE TABLE). If constraints
exist on the trigger table, they are checked prior to trigger execution. If
either PRIMARY KEY or FOREIGN KEY constraints are violated, the trigger is not
executed (fired).

Note Whether SQL Server interprets an empty string
as a single space or as a true empty string is controlled by the setting of
sp_dbcmptlevel. If the compatibility level is less than or equal to 65, SQL
Server interprets empty strings as single spaces. If the compatibility level is
equal to 70, SQL Server interprets empty strings as empty strings.

Trigger Limitations

CREATE TRIGGER must be the first statement in the
batch and can apply to only one table.

A trigger is created only in the current database;
however, a trigger can reference objects outside the current database.

If the trigger owner name is specified (to qualify
the trigger), qualify the table name in the same way.

The same trigger action can be defined for more
than one user action (for example, INSERT and UPDATE) in the same CREATE
TRIGGER statement.

Any SET statement can be specified inside a trigger.
The SET option chosen remains in effect during the execution of the trigger and
then reverts to its former setting.

When a trigger fires, results are returned to the
calling application, just as with stored procedures. To eliminate having
results returned to an application due to a trigger firing, do not include
either SELECT statements that return results, or statements that perform
variable assignment in a trigger. A trigger that includes either SELECT
statements that return results to the user or statements that perform variable
assignment requires special handling; these returned results would have to be
written into every application in which modifications to the trigger table are
allowed. If variable assignment must occur in a trigger, use a SET NOCOUNT
statement at the beginning of the trigger to eliminate the return of any result
sets.

A trigger cannot be created on a view.

A TRUNCATE TABLE statement is not caught by a
DELETE trigger. Although a TRUNCATE TABLE statement is, in effect, a DELETE
without a WHERE clause (it removes all rows), it is not logged and thus cannot
execute a trigger. Because permission for the TRUNCATE TABLE statement defaults
to the table owner and is not transferable, only the table owner should be
concerned about inadvertently circumventing a DELETE trigger with a TRUNCATE
TABLE statement.

The WRITETEXT statement, whether logged or
unlogged, does not activate a trigger.

These Transact-SQL statements are not allowed in a
trigger:

ALTER DATABASE ALTER PROCEDURE ALTER TABLE

ALTER TRIGGER ALTER VIEW CREATE DATABASE

CREATE DEFAULT CREATE INDEX CREATE PROCEDURE

CREATE RULE CREATE SCHEMA CREATE TABLE

CREATE TRIGGER CREATE VIEW DENY

DISK INIT DISK RESIZE DROP DATABASE

DROP DEFAULT DROP INDEX DROP PROCEDURE

DROP RULE DROP TABLE DROP TRIGGER

DROP VIEW GRANT LOAD DATABASE

LOAD LOG RESTORE DATABASE RESTORE LOG

REVOKE RECONFIGURE

TRUNCATE TABLE UPDATE STATISTICS

Note Because SQL Server does not support
user-defined triggers on system tables, it is recommended that no user-defined
triggers be created on system tables.

Multiple Triggers

SQL Server allows multiple triggers to be created
for each data modification event (DELETE, INSERT, or UPDATE). For example, if
CREATE TRIGGER FOR UPDATE is executed for a table that already has an UPDATE
trigger, then an additional update trigger is created. In earlier versions,
only one trigger for each data modification event (INSERT, UPDATE, DELETE) was
allowed for each table.

Note The default behavior for CREATE TRIGGER (with
the compatibility level of 70) is to add additional triggers to existing
triggers, if the trigger names differ. If trigger names are the same, SQL
Server returns an error message. However, if the compatibility level is equal
to or less than 65, any new triggers created with the CREATE TRIGGER statement
replace any existing triggers of the same type, even if the trigger names are
different.

Recursive Triggers

SQL Server also allows recursive invocation of
triggers when the recursive triggers setting is enabled in sp_dboption.

Recursive triggers allow two types of recursion to
occur:

• Indirect recursion

• Direct recursion

With indirect recursion, an application updates
table T1, which fires trigger TR1, updating table T2. In this scenario, trigger
T2 then fires and updates table T1.

With direct recursion, the application updates
table T1, which fires trigger TR1, updating table T1. Because table T1 has been
updated, trigger TR1 fires again, and so on.

This example uses both indirect and direct trigger
recursion. Assume that two update triggers, TR1 and TR2, are defined on table
T1. Trigger TR1 updates table T1 recursively. An UPDATE statement executes each
TR1 and TR2 one time. In addition, the execution of TR1 triggers the execution
of TR1 (recursively) and TR2. The inserted and deleted tables for a given
trigger contain rows corresponding only to the UPDATE statement that invoked
the trigger.

Note The above behavior occurs only if the
recursive triggers setting of sp_dboption is enabled. There is no defined order
in which multiple triggers defined for a given event are executed. Each trigger
should be self-contained.

If any of the triggers do a ROLLBACK TRANSACTION,
regardless of the nesting level, no further triggers are executed.

Nested Triggers

Triggers can be nested up to 32 levels. If a
trigger changes a table on which there is another trigger, the second trigger
is activated and can then call a third trigger, and so on. If any trigger in
the chain sets off an infinite loop, the nesting level is exceeded and the
trigger is canceled. To disable nested triggers, set the nested triggers option
of sp_configure to 0 (off). The default configuration allows nested triggers.
If nested triggers is off, recursive triggers is also disabled, regardless of the
recursive triggers setting of sp_dboption.

Deferred Name Resolution

SQL Server allows Transact-SQL stored procedures,
triggers, and batches to refer to tables that do not exist at compile time.
This ability is called deferred name resolution. However, if the Transact-SQL
stored procedure, trigger, or batch refers to a table defined in the stored
procedure or trigger, a warning is issued at creation time only if the
compatibility level setting (set by executing sp_dbcmptlevel) is equal to 65. A
warning is issued at compile time if a batch is used. An error message is
returned at execution time if the table referenced does not exist. For more
information, see Deferred Name Resolution and Compilation.

Permission

CREATE TRIGGER permission defaults to the table
owner on which the trigger is defined, or to members of the db_owner and
db_ddladmin fixed database roles, and is not transferable.

Examples

A. Use a trigger with a reminder message

This example trigger prints a message to the client
when anyone tries to add or change data in the titles table.

USE pubs

IF EXISTS (SELECT name FROM sysobjects

WHERE name = 'reminder' AND type = 'TR')

DROP TRIGGER reminder

GO

CREATE TRIGGER reminder

ON titles

FOR INSERT, UPDATE

AS RAISERROR (50009, 16, 10)

GO

B. Use a trigger with a reminder e-mail message

This example sends an e-mail message to a specified
person (MaryM) when the titles table changes.

USE pubs

IF EXISTS (SELECT name FROM sysobjects

WHERE name = 'reminder' AND type = 'TR')

DROP TRIGGER reminder

GO

CREATE TRIGGER reminder

ON titles

FOR INSERT, UPDATE, DELETE

AS

EXEC master..xp_sendmail 'MaryM',

'Don''t forget to print a report for the
distributors.'

GO

C. Use a trigger business rule between the employee
and jobs tables

Because CHECK constraints can reference only the
columns on which the column- or table-level constraint has been defined, any
cross-table constraints (in this case, business rules) must be defined as
triggers.

This example creates a trigger that, when an
employee job level is inserted or updated, checks that the specified employee
job level (job_lvls), on which salaries are based, is within the range defined
for the job. To get the appropriate range, the jobs table must be referenced.

USE pubs

IF EXISTS (SELECT name FROM sysobjects

WHERE name = 'reminder' AND type = 'TR')

DROP TRIGGER reminder

GO

CREATE TRIGGER employee_insupd

ON employee

FOR INSERT, UPDATE

AS

/* Get the range of level for this job type from
the jobs table. */

DECLARE @min_lvl tinyint,

@max_lvl tinyint,

@emp_lvl tinyint,

@job_id smallint

SELECT @min_lvl = min_lvl,

@max_lvl = max_lvl,

@emp_lvl = i.job_lvl,

@job_id = i.job_id

FROM employee e INNER JOIN inserted i ON e.emp_id =
i.emp_id

JOIN jobs j ON j.job_id = i.job_id

IF (@job_id = 1) and (@emp_lvl <> 10)

BEGIN

RAISERROR ('Job id 1 expects the default level of
10.', 16, 1)

ROLLBACK TRANSACTION

END

ELSE

IF NOT (@emp_lvl BETWEEN @min_lvl AND @max_lvl)

BEGIN

RAISERROR ('The level for job_id:%d should be
between %d and %d.',

16, 1, @job_id, @min_lvl, @max_lvl)

ROLLBACK TRANSACTION

END

D. Use deferred name resolution

This example creates two triggers to illustrate
deferred name resolution.

USE pubs

IF EXISTS (SELECT name FROM sysobjects

WHERE name = 'trig1' AND type = 'TR')

DROP TRIGGER trig1

GO

-- Creating a trigger on a nonexistent table.

CREATE TRIGGER trig1

on authors

FOR INSERT, UPDATE, DELETE

AS

SELECT a.au_lname, a.au_fname, x.info

FROM authors a INNER JOIN does_not_exist x

ON a.au_id = x.au_id

GO

-- Here is the statement to actually see the text
of the trigger.

SELECT o.id, c.text

FROM sysobjects o INNER JOIN syscomments c

ON o.id = c.id

WHERE o.type = 'TR' and o.name = 'trig1'

-- Creating a trigger on an existing table, but
with a nonexistent

-- column.

USE pubs

IF EXISTS (SELECT name FROM sysobjects

WHERE name = 'trig2' AND type = 'TR')

DROP TRIGGER trig2

GO

CREATE TRIGGER trig2

ON authors

FOR INSERT, UPDATE

AS

DECLARE @fax varchar(12)

SELECT @fax = phone

FROM authors

GO

-- Here is the statement to actually see the text
of the trigger.

SELECT o.id, c.text

FROM sysobjects o INNER JOIN syscomments c

ON o.id = c.id

WHERE o.type = 'TR' and o.name = 'trig2'

E. Use COLUMNS_UPDATED

This example creates two tables: an employeeData
table and an auditEmployeeData table. The employeeData table, which holds
sensitive employee payroll information, can be modified by members of the human
resources department. If the employee’s social security number (SSN), yearly
salary or bank account number is changed, an audit record is generated and
inserted into the auditEmployeeData audit table.

By using the COLUMNS_UPDATED() function, it is
possible to test quickly for any changes to these columns that contain
sensitive employee information.

USE pubs

IF EXISTS(SELECT TABLE_NAME FROM
INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME = 'employeeData')

DROP TABLE employeeData

IF EXISTS(SELECT TABLE_NAME FROM
INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME = 'auditEmployeeData')

DROP TABLE auditEmployeeData

GO

CREATE TABLE employeeData (

emp_id int NOT NULL,

emp_bankAccountNumber char (10) NOT NULL,

emp_salary int NOT NULL,

emp_SSN char (11) NOT NULL,

emp_lname nchar (32) NOT NULL,

emp_fname nchar (32) NOT NULL,

emp_manager int NOT NULL

)

GO

CREATE TABLE auditEmployeeData (

audit_log_id uniqueidentifier DEFAULT NEWID(),

audit_log_type char (3) NOT NULL,

audit_emp_id int NOT NULL,

audit_emp_bankAccountNumber char (10) NULL,

audit_emp_salary int NULL,

audit_emp_SSN char (11) NULL,

audit_user sysname DEFAULT SUSER_SNAME(),

audit_changed datetime DEFAULT GETDATE()

)

GO

CREATE TRIGGER updEmployeeData

ON employeeData

FOR update AS

-- Check whether columns 2, 3 or 4 has been
updated. If any or all of

-- columns 2, 3 or 4 have been changed, create an
audit record.

-- The bitmask is:
power(2,(2-1))+power(2,(3-1))+power(2,(4-1)) = 14

-- To check if all columns 2, 3, and 4 are updated,
use = 14 in place of

-- >0 (below).

IF (COLUMNS_UPDATED() & 14) > 0

-- Use IF (COLUMNS_UPDATED() & 14) = 14 to see
if all of columns 2, 3,

-- and 4 are updated.

BEGIN

-- Audit OLD record.

INSERT INTO auditEmployeeData

(audit_log_type,

audit_emp_id,

audit_emp_bankAccountNumber,

audit_emp_salary,

audit_emp_SSN)

SELECT 'OLD',

del.emp_id,

del.emp_bankAccountNumber,

del.emp_salary,

del.emp_SSN

FROM deleted del

-- Audit NEW record.

INSERT INTO auditEmployeeData

(audit_log_type,

audit_emp_id,

audit_emp_bankAccountNumber,

audit_emp_salary,

audit_emp_SSN)

SELECT 'NEW',

ins.emp_id,

ins.emp_bankAccountNumber,

ins.emp_salary,

ins.emp_SSN

FROM inserted ins

END

GO

--Inserting a new employee does not cause the
UPDATE trigger to fire.

INSERT INTO employeeData

VALUES ( 101, 'USA-987-01', 23000, 'R-M53550M',
N'Mendel', N'Roland', 32)

GO

-- Updating the employee record for employee number
101 to change the

-- salary to 51000 causes the UPDATE trigger to
fire and an audit trail

-- to be produced.

UPDATE employeeData

SET emp_salary = 51000

WHERE emp_id = 101

GO

SELECT * FROM auditEmployeeData

GO

--Updating the employee record for employee number
101 to change both the bank account number and social security number (SSN)
causes the UPDATE trigger to fire and an audit trail to be produced.

UPDATE employeeData

SET emp_bankAccountNumber = '133146A0', emp_SSN =
'R-M53550M'

WHERE emp_id = 101

GO

SELECT * FROM auditEmployeeData

GO

SET Statement

When an ODBC application connects to Microsoft® SQL
Server™, the server automatically sets these options for the session:

• SET QUOTED_IDENTIFIER ON

• SET TEXTSIZE 2147483647

• SET ANSI_DEFAULTS ON

• SET CURSOR_CLOSE_ON_COMMIT OFF

• SET IMPLICIT_TRANSACTIONS OFF

These settings increase the portability of ODBC
applications. Because DB-Library-based applications generally do not set these
options, triggers should be tested with the SET options listed above turned
both on and off. This ensures that the triggers work correctly regardless of
the options a particular connection may have set when it invokes the trigger. A
trigger that requires a particular setting for one of these options should
issue a SET statement at the start of the trigger. This SET statement will
remain in effect only for the execution of the trigger; when the trigger
completes, the original setting is restored.

Examples

The following example creates an INSERT trigger
my_trig on table my_table and tests whether column b was affected by any INSERT
statements.

CREATE TABLE my_table

(a int NULL, b int NULL)

GO

CREATE TRIGGER my_trig

ON my_table

FOR INSERT

AS

IF UPDATE(b)

PRINT 'Column b Modified'

GO

You obtain similar results using the
COLUMNS_UPDATED() clause:

CREATE TRIGGER my_trig2

ON my_table

FOR INSERT

AS

IF ( COLUMNS_UPDATED() & 2 = 2 )

PRINT 'Column b Modified'

GO

Returning Results

It is recommended that a trigger not return any
results because special handling for these returned results must be written
into every application in which modifications to the trigger table are allowed.
To prevent any results from being returned from a trigger, do not include
either SELECT statements or variable assignments in the definition of the
trigger. If variable assignment must occur in a trigger, use a SET NOCOUNT
statement at the beginning of the trigger to eliminate the return of any result
sets.

Triggers and ROLLBACK TRANSACTION

When triggers that include ROLLBACK TRANSACTION
statements are executed from a batch, they cancel the entire batch. In the
following example, if the INSERT statement fires a trigger that includes a
ROLLBACK TRANSACTION, the DELETE statement is not executed because the batch is
canceled:

If triggers that include ROLLBACK TRANSACTION
statements are fired from within a user-defined transaction, the ROLLBACK
TRANSACTION rolls back the entire transaction. In this example, if the INSERT
statement fires a trigger that includes a ROLLBACK TRANSACTION, the UPDATE
statement is also rolled back:

Multirow Considerations

An important consideration to keep in mind when
writing the code for a trigger is that the statement that causes the trigger to
fire can be a single statement that affects multiple rows of data, rather than
a single row. This is common for UPDATE and DELETE triggers because these
statements often affect multiple rows. It is less common for INSERT triggers
because the basic INSERT statement adds only a single row. However, since an
INSERT trigger can be fired by an INSERT INTO (table_name) SELECT statement, hundreds
of rows can be affected by an INSERT trigger.

Multirow considerations are particularly important
when the function of a trigger is to automatically recalculate summary values
from one table and store the results in another for ongoing tallies.

Examples

The triggers in the following examples are designed
to store a running total of a column in another table. The first version of the
trigger works well for a single row insert, when a row of data is loaded into
the sales table. The trigger is fired by an INSERT statement and the new row is
loaded into the inserted table for the duration of the trigger execution. The
UPDATE statement reads the qty column value for the row and adds it to the
existing value in the ytd_sales column in the titles table. The WHERE clause
ensures that the updated row in the sales table matches the title_id of the row
in the inserted table.

-- Trigger valid for single row INSERTS.

CREATE TRIGGER intrig

ON sales

FOR INSERT AS

UPDATE titles

SET ytd_sales = ytd_sales + qty

FROM inserted

WHERE titles.title_id = inserted.title_id

In the case of a multirow INSERT, this trigger
might not operate correctly; the expression to the right of an assignment
expression in an UPDATE statement (ytd_sales + qty) can be only a single value,
not a list of values. So the effect of the trigger is to obtain a value from
any single row in the inserted table and add it to the existing ytd_sales value
in the titles table for a given title_id value. This might not have the desired
effect if a single title_id value occured more than once in the inserted table.

To update the titles table properly, the trigger
has to accommodate the possibility of multiple rows in the inserted table. This
can be done with the SUM function that calculates the total qty for a group of
rows in the inserted table for each title_id. The SUM function is placed in a
correlated subquery (the SELECT statement in parentheses), which returns a
single value for each title_id in the inserted table that matches or is
correlated with a title_id in the titles table.

-- Trigger valid for multirow and single row
inserts.

CREATE TRIGGER intrig

ON sales

FOR INSERT AS

UPDATE titles

SET ytd_sales = ytd_sales +

(SELECT SUM(qty) -- Correlated subquery.

FROM inserted

WHERE titles.title_id = inserted.title_id)

WHERE titles.title_id IN

(SELECT title_id FROM inserted)

This trigger also works correctly in a single row
insert; the sum of the qty value column is the sum of a single row. However,
with this trigger the correlated subquery and the IN operator used in the WHERE
clause require additional processing from Microsoft® SQL Server™, which is
unnecessary for a single row insert. Therefore, you can change the trigger to
use the method optimal for the number of rows. For example, the @@ROWCOUNT
function can be used in the logic of the trigger to distinguish between a single
and a multirow insert.

-- Trigger valid for multirow and single row
inserts

-- and optimal for single row inserts.

CREATE TRIGGER intrig

ON sales

FOR INSERT AS

IF @@ROWCOUNT = 1

BEGIN

UPDATE titles

SET ytd_sales = ytd_sales + qty

FROM inserted

WHERE titles.title_id = inserted.title_id

END

ELSE

BEGIN

UPDATE titles

SET ytd_sales = ytd_sales +

(SELECT SUM(qty)

FROM inserted

WHERE titles.title_id = inserted.title_id)

WHERE titles.title_id IN

(SELECT title_id FROM inserted)

END

Implicit and Explicit Null Values

Inserting an explicit null value into a column or
using the DEFAULT keyword to assign a value to a column activates the trigger
as expected. Similarly, when no value is specified in the INSERT statement for
a column, the trigger is still activated when:

• An implicit null value is inserted into a column
because no DEFAULT definition exists.

• A default value is inserted into a column because
a DEFAULT definition does exist.

Triggers and Performance

Trigger performance overhead is usually low. The
time involved in running a trigger is spent mostly in referencing other tables,
which can be either in memory or on the database device. The deleted and
inserted tables are always in memory. The location of other tables referenced
by the trigger determines the amount of time the operation takes.

ALTER TRIGGER trigger_name

ON table

[WITH ENCRYPTION]

{

{FOR { [DELETE] [,] [UPDATE] [,][INSERT] }

[NOT FOR REPLICATION]

AS

sql_statement [...n]

}

AS

{ IF UPDATE (column)

[{AND | OR} UPDATE (column)]

[...n]

| IF (COLUMNS_UPDATED() {bitwise_operator}
updated_bitmask)

{ comparison_operator} column_bitmask [...n]

}

sql_statement [...n]

}

}

Viewing a Trigger

Microsoft® SQL Server™ allows you to determine the
types of triggers on a table, the name of the trigger, its owner, and the date
it was created.

You can also:

• Gain information about the definition of a
trigger if it was not encrypted when created or modified. You may need to see
the definition of the trigger to see its Transact-SQL statements or to
understand how it affects the table upon which it is defined.

• List the objects used by the specified trigger.
This information can be used to identify the objects that affect the trigger if
they are changed or deleted in the database.

sp_helptrigger

Returns the type or types of triggers defined on
the specified table for the current database.

sp_helptrigger [@tabname =] 'table'

[,[@triggertype =] 'type']

sp_help

Reports information about a database object (any
object listed in the sysobjects table), a user-defined data type, or a data
type supplied by Microsoft® SQL Server™.

Syntax

sp_help [[@objname =] name]

sp_helptext

Prints the text of a rule, a default, or an
unencrypted stored procedure, trigger, or view.

Syntax

sp_helptext [@objname =] 'name'

sp_depends

Displays information about database object dependencies
(for example, the views and procedures that depend on a table or view, and the
tables and views that are depended on by the view or procedure). References to
objects outside the current database are not reported.

Syntax

sp_depends [@objname =] 'object'

Deleting a Trigger

Delete a trigger when you no longer need it. When a
trigger is deleted, the table and the data upon which it is based are not
affected. Deleting a table automatically deletes any triggers on the table.
Permissions to delete a trigger default to the owner of the table upon which
the trigger is defined.

DROP TRIGGER (T-SQL)

Removes one or more triggers from the current
database.

Syntax

DROP TRIGGER {trigger} [,...n]

<