Introduction

Building and Optimizing Triggers in SQL Server

Florin Angelescu

Instructor

What is a trigger?

Special type of stored procedure
Executed when an event occurs in the database server

How a Trigger Works

Types of trigger (based on T-SQL commands)

Data Manipulation Language (DML) triggers
- INSERT, UPDATE or DELETE statements
Data Definition Language (DDL) triggers
- CREATE, ALTER or DROP statements
Logon triggers
- LOGON events

Types of trigger (based on behavior)

AFTER trigger
- The original statement executes
- Additional statements are triggered
Examples of use cases
- Rebuild an index after a large insert
- Notify the admin when data is updated

Types of trigger (based on behavior)

INSTEAD OF trigger
- The original statement is prevented from execution
- A replacement statement is executed instead
Examples of use cases
- Prevent insertions
- Prevent updates
- Prevent deletions
- Prevent object modifications
- Notify the admin

Trigger definition (with AFTER)

-- Create the trigger by giving it a descriptive name
CREATE TRIGGER ProductsTrigger

-- The trigger needs to be attached to a table
ON Products

-- The trigger behavior type
AFTER INSERT

-- The beginning of the trigger workflow
AS

-- The action executed by the trigger
PRINT ('An insert of data was made in the Products table.');

Trigger definition (with INSTEAD OF)

-- Create the trigger by giving it a descriptive name
CREATE TRIGGER PreventDeleteFromOrders
-- The trigger needs to be attached to a table
ON Orders
-- The trigger behavior type
INSTEAD OF DELETE
-- The beginning of the trigger workflow
AS
-- The action executed by the trigger
PRINT ('You are not allowed to delete rows from the Orders table.');

AFTER vs. INSTEAD OF

CREATE TRIGGER MyFirstAfterTrigger
ON Table1
-- Triggered after 
-- the firing event (UPDATE)
AFTER UPDATE
AS
{trigger_actions_section};

CREATE TRIGGER MyFirstInsteadOfTrigger
ON Table2
-- Triggered instead of 
-- the firing event (UPDATE)
INSTEAD OF UPDATE
AS
{trigger_actions_section};

Let's practice!

Building and Optimizing Triggers in SQL Server