Trigger Quick Reference - SQL Server

Syntax

Creating a Trigger

CREATE TRIGGER trigger_name
ON table
{ FOR | AFTER | INSTEAD OF }
{ [DELETE] [,] [INSERT] [,] [UPDATE] }
AS
sql_statements

Disabling a Trigger

DISABLE TRIGGER trigger_name
ON table

Functions

FunctionUsage
UPDATE (column)Used in an INSERT or UPDATE trigger. Returns a boolean indicating whether the specified column is being updated.
COLUMNS_UPDATED() {bitwise_operator} updated_bitmaskUsed in an INSERT or UPDATE trigger. Returns a varbinary bit-coded flag that indicates which columns were inserted or updated. See SQL Server Books Online for further details.

Referencing Old or New Values

T-SQL provides two logical tables structurally similar to the table on which the trigger is defined, and for use only within a trigger. The deleted logical table holds the "old" column values (i.e., values before the triggering SQL statement), while the inserted logical table holds the "new" column values (i.e,. values after the triggering SQL statement). For example, to retrieve all values in the deleted table, use: SELECT * FROM deleted.

Example

create trigger trgPeople on dbo.People 
for update as
update dbo.People
set UpdatedOn = getutcdate()
where id in (select id from inserted)