Page History: Trigger Quick Reference - SQL Server

Compare Page Revisions

1 0 Current 1 0

« Older Revision - Back to Page History - Current Revision

Page Revision: Mon, Dec 21, 2009, 9:12 AM

Syntax¶

Creating a Trigger¶

CREATE TRIGGER trigger_name
ON table
FOR { [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)