Jasinski Technical Wiki

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

PoweredBy

Trigger Quick Reference - SQL Server

RSS
Modified on Mon, Dec 21, 2009, 9:12 AM by Administrator Categorized as Quick Reference, SQL Server

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

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)

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2018, Patrick Jasinski.