SQL Server 2005: TRIGGER

Creates a DML, DDL, or logon trigger. A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server. DML triggers execute when a user tries to modify data through a data manipulation language (DML) event. DML events are INSERT, UPDATE, or DELETE statements on a table or view. These triggers fire when any valid event is fired, regardless of whether or not any table rows are affected.


DDL triggers execute in response to a variety of data definition language (DDL) events. These events primarily correspond to Transact-SQL CREATE, ALTER, and DROP statements, and certain system stored procedures that perform DDL-like operations. Logon triggers fire in response to the LOGON event that is raised when a user sessions is being established. Triggers can be created directly from Transact-SQL statements or from methods of assemblies that are created in the Microsoft .NET Framework common language runtime (CLR) and uploaded to an instance of SQL Server. SQL Server allows for creating multiple triggers for any specific statement.



Syntax

Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger)
CREATE TRIGGER [ schema_name . ]trigger_name 
ON { table | view } 
[ WITH <dml_trigger_option> [ ,...n ] ]
{ FOR | AFTER | INSTEAD OF } 
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } 
[ WITH APPEND ] 
[ NOT FOR REPLICATION ] 
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME <method specifier [ ; ] > }

<dml_trigger_option> ::=
    [ ENCRYPTION ]
    [ EXECUTE AS Clause ]

<method_specifier> ::=    assembly_name.class_name.method_nameTrigger on a CREATE, ALTER, DROP, GRANT, DENY, REVOKE, or UPDATE STATISTICS statement (DDL Trigger)
CREATE TRIGGER trigger_name 
ON { ALL SERVER | DATABASE } 
[ WITH <ddl_trigger_option> [ ,...n ] ]
{ FOR | AFTER } { event_type | event_group } [ ,...n ]
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier >  [ ; ] }

<ddl_trigger_option> ::=
    [ ENCRYPTION ]
    [ EXECUTE AS Clause ]

<method_specifier> ::=    assembly_name.class_name.method_nameTrigger on a LOGON event (Logon Trigger)
CREATE TRIGGER trigger_name 
ON ALL SERVER 
[ WITH <logon_trigger_option> [ ,...n ] ]
{ FOR| AFTER } LOGON  
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier >  [ ; ] }

<logon_trigger_option> ::=
    [ ENCRYPTION ]
    [ EXECUTE AS Clause ]

<method_specifier> ::=    assembly_name.class_name.method_name

A trigger is a database object that is attached to a table. In many aspects it is similar to a stored procedure. As a matter of fact, triggers are often referred to as a "special kind of stored procedure." The main difference between a trigger and a stored procedure is that the former is attached to a table and is only fired when an INSERT, UPDATE or DELETE occurs. You specify the modification action(s) that fire the trigger when it is created.

SET NOCOUNT ON

CREATE TABLE Source (Sou_ID int IDENTITY, Sou_Desc varchar(10))
go
CREATE TRIGGER tr_Source_INSERT
ON Source
FOR INSERT
AS
PRINT GETDATE()
go
INSERT Source (Sou_Desc) VALUES ('Test 1')

-- Results --

Apr 28 2001  9:56AM

When to Use Triggers

There are more than a handful of developers who are not real clear when triggers should be used. I only use them when I need to perform a certain action as a result of an INSERT, UPDATE or DELETE and ad hoc SQL (aka SQL Passthrough) is used. I implement most of my data manipulation code via stored procedures and when you do this the trigger functionality can be moved into the procedure. For example, let's say you want to send an email to the Sales Manager when an order is entered whose priority is high. When ad hoc SQL is used to insert the Orders row, a trigger is used to determine the OrderPriority and send the email when the criteria is met. The following shows a partial code listing of what this looks like.

CREATE TABLE Orders (Ord_ID int IDENTITY, Ord_Priority varchar(10))
go
CREATE TRIGGER tr_Orders_INSERT
ON Orders
FOR INSERT
AS
IF (SELECT COUNT(*) FROM inserted WHERE Ord_Priority = 'High') = 1
 BEGIN
  PRINT 'Email Code Goes Here'
 END
go
INSERT Orders (Ord_Priority) VALUES ('High')

-- Results --

Email Code Goes Here

CREATE PROCEDURE ps_Orders_INSERT
@Ord_Priority varchar(10)
AS
BEGIN TRANSACTION
 INSERT Orders (Ord_Priority) VALUES (@Ord_Priority)

 IF @@ERROR <> 0
  GOTO ErrorCode

 IF @Ord_Priority = 'High'
  PRINT 'Email Code Goes Here'

COMMIT TRANSACTION

ErrorCode:
 IF @@TRANCOUNT <> 0
  PRINT 'Error Code'
go

INSERT Orders (Ord_Priority) VALUES ('High')
INSERT Orders (Ord_Priority) VALUES ('High')

-- Results --

Email Code Goes Here

Email Code Goes Here

INSERT Orders
SELECT Ord_Priority FROM Orders

ALTER TRIGGER tr_Orders_INSERT
ON Orders
FOR INSERT
AS
IF EXISTS (SELECT * FROM inserted WHERE Ord_Priority = 'High')
 BEGIN
  DECLARE @Count tinyint
  SET @Count = (SELECT COUNT(*) FROM inserted WHERE Ord_Priority = 'High')
  PRINT CAST(@Count as varchar(3))+' row(s) with a priority of High were entered'
 END
go

INSERT Orders
SELECT Ord_Priority FROM Orders

-- Results --

12 row(s) with a priority of High were entered

A Real-World Example

Those of you familiar with web site management know that counting the traffic on a site is key in determining which areas of the site are being used. Internet Information Server (IIS) has logging capabilities that tracks a number of attributes associated with each visitor. For example, every time a visitor accesses a page on a site that page and the user's information is logged. By default the data is logged in a text file, but you can alter the default behavior and log the data to an ODBC-compliant data store.

CREATE TABLE InetLog (ClientHost varchar(255), LogTime datetime, Target 
varchar(255))
go
CREATE TABLE LogSummary (LogSum_Category varchar(30), LogSum_Count int)
go
INSERT LogSummary VALUES ('About Us',0)
INSERT LogSummary VALUES ('Services',0)

CREATE TRIGGER tr_InetLog_INSERT
ON InetLog
FOR INSERT
AS

IF EXISTS (SELECT * FROM inserted WHERE Target = 'AboutUs.htm')
 BEGIN
  UPDATE LogSummary
  SET LogSum_Count = (SELECT COUNT(*) FROM InetLog WHERE Target = 'AboutUs.htm')
  WHERE LogSum_Category = 'About Us'
 END

IF EXISTS (SELECT * FROM inserted WHERE Target = 'Services.htm')
 BEGIN
  UPDATE LogSummary
  SET LogSum_Count = (SELECT COUNT(*) FROM InetLog WHERE Target = 'Services.htm')
  WHERE LogSum_Category = 'Services'
 END
go

INSERT InetLog VALUES ('111.111.111.111', '4/1/29 12:00:50','Default.htm')
INSERT InetLog VALUES ('111.111.111.111', '4/1/29 12:01:01','AboutUs.htm')
INSERT InetLog VALUES ('111.111.111.111', '4/1/29 12:02:01','Services.htm')
INSERT InetLog VALUES ('111.111.111.111', '4/1/29 12:03:01','Products.htm')
INSERT InetLog VALUES ('111.111.111.111', '4/1/29 12:04:50','Default.htm')
INSERT InetLog VALUES ('111.111.111.111', '4/1/29 12:05:01','AboutUs.htm')
INSERT InetLog VALUES ('111.111.111.111', '4/1/29 12:06:01','Services.htm')
INSERT InetLog VALUES ('111.111.111.111', '4/1/29 12:07:01','Products.htm')
go
SELECT * FROM LogSummary

-- Results --

LogSum_Category                LogSum_Count
------------------------------ ------------
About Us                       2
Services                       2

CREATE TRIGGER InsertPublication
ON Publications
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Num smallint
SET @Num = SELECT NumPublications FROM Books WHERE ISBN IN
(SELECT ISBN FROM inserted);
UPDATE Books
SET NumPublications = @Num + 1
WHERE ISBN IN
(SELECT ISBN FROM inserted);
END