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¶

Function	Usage
`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_bitmask`	Used 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)