Navigation

	Home Page
	Index
	All Pages

Quick Search
Advanced Search »

Contributor Links

	Create a new Page
	Administration
	File Management
	Login/Logout
	Your Profile

Other Wiki Sections

Software

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)