Implementing Triggers

Overview

Introducing Triggers
Creating, Altering, and Dropping Triggers
Working with Triggers
Implementing Triggers
Lesson: Introducing Triggers

SQL Server Triggers

What Are Triggers Used For?
Sequence of Events and Restrictions
SQL Server Triggers

Associated with a Table

Invoked Automatically
Cannot Be Called Directly
Is Part of a Transaction
 Can include a ROLLBACK TRANSACTION statement
 If a ROLLBACK TRANSACTION statement is
encountered, the entire transaction rolls back.
What Are Triggers Used For?

Cascade Changes Through Related Tables

Enforce More Complex Data Integrity
Define Custom Error Messages
Compare Before and After States of Data
Under Modification
Sequence of Events and Restrictions

Triggers Are Reactive; Constraints Are Proactive

Constraints Are Checked First
Tables Can Have Multiple Triggers for Any Action
Table Owners Can Designate the First and Last Trigger
to Fire
You Must Have Permission to Perform All Statements
That Define Triggers
Lesson: Creating, Altering, and Dropping Triggers

The CREATE TRIGGER Statement

The ALTER TRIGGER Statement
The DROP TRIGGER Statement
The CREATE TRIGGER Statement

Requires Appropriate Permissions

Cannot Contain Certain Statements
USE Northwind
GO
CREATE TRIGGER Empl_Delete ON Employees
FOR DELETE
AS
IF @@ROWCOUNT > 1
BEGIN
print(
'You cannot delete more than one employee at a time.')
ROLLBACK TRANSACTION
END
The ALTER TRIGGER Statement

Altering a Trigger
Changes the definition without dropping the trigger
Can disable or enable a trigger
USE Northwind
GO
ALTER TRIGGER Empl_Delete ON Employees
FOR DELETE
AS
IF @@ROWCOUNT > 6
BEGIN
print(
'You cannot delete more than six employees at a time.')
ROLLBACK TRANSACTION
END
The DROP TRIGGER Statement

Dropping a Trigger

 Dropped automatically when its associated table

is dropped

 Information is removed from the sysobjects

and syscomments

USE Northwind
GO
DROP TRIGGER Empl_Delete
GO
Lesson: Working with Triggers

How INSERT Triggers Work

How DELETE Triggers Work
How UPDATE Triggers Work
How INSTEAD OF Triggers Work
How Nested Triggers Work
Recursive Triggers
How INSERT Triggers Work

TRIGGER
INSERT Actions
statement to aExecute
table with an INSERT Trigger Defined
INSERTINSERT
[OrderStatement
Details] to a Table with an INSERT
VALUES
1
(10525,
Trigger Code:
USE2,Northwind
Trigger 19.00,
Defined5, 0.2)
CREATE TRIGGER OrdDet_Insert
ON [Order
OrderID Details]
ProductID UnitPrice Quantity Discount
2 INSERT
AS
Statement
FOR INSERT
10522 10 Logged 31.00 7 0.2
UPDATE10523
P SET 41 9.65 9 0.15

3 UnitsInStock
Trigger Actions
10524 = 7 (P.UnitsInStock
Executed 30.00 24– I.Quantity)
FROM Products AS P INNER JOIN Inserted AS I
0.0
10525
ON P.ProductID 2 = I.ProductID
19.00 5 0.2

OrderID ProductID UnitPrice Quantity Discount Insert statement logged

Products
10522 10 31.00 7 0.2 ProductID UnitsInStock … …
inserted
10523 41 9.65 9 0.15 1 15
10524 7 10525 30.00 2 24 19.00 0.0 5 2
0.2
10
5
10525 2 19.00 5 0.2 3 65
4 20
How DELETE Triggers Work

DELETEActions
Trigger Statement
Execute
to a table with a DELETE Trigger Defined
DELETE Categories
Statement to a Table with a DELETE
1
DELETE
USE
Categories
Northwind
WHERE
Statement
= 4 Defined
CREATE TRIGGER CategoryID CategoryName Description
Category_Delete Picture
CategoryID
ON Categories Soft drinks,
FOR DELETE 1 Beverages 0x15…
coffees…
AS
2 DELETE Statement Logged = 1
UPDATE P SET Discontinued
2 Condiments
Sweet and 0x15…
FROM Products AS P INNER JOIN deleted savory …AS d
ON P.CategoryID = d.CategoryID
Desserts,
3 Trigger Actions Executed
3 Confections
candies, …
0x15…

4 Products
Dairy Products Cheeses 0x15…
ProductID Discontinued … …
1 0
2 10
3 0
4 0
How UPDATE Triggers Work

UPDATE Statement
TRIGGER Actions Execute
to a table with an UPDATE Trigger Defined
USE Northwind Customers
UPDATE
GO Customers
1
SET
CREATEUPDATE
TRIGGER Statement
CustomerID
WHEREONCustomerID
Trigger
= ‘AHORN’to a Table
Customer_Update
= ‘AROUT’
Defined
Customers
with an UPDATE
CustomerID CompanyName ..
FOR UPDATE ALFKI Alfreds Futterkiste ~~~
AS Ana Trujillo
IF UPDATE (CustomerID) ANATR ~~~
Emparedados y helados
2
BEGIN UPDATE Statement Logged as INSERT
***** Statements
Customer ID cannot be ANTON
modified.',
and
RAISERROR ('Transaction cannot be processed.\
DELETE Antonio Moreno
10, 1) ~~~
ROLLBACK TRANSACTION Taquería
END Customers
AROUT Around the Horn ~~~
3
UPDATE
Trigger Actions Executed CustomerID
Statement
Transaction
CompanyName ..
cannot be logged as INSERT and DELETE Statements
ALFKI Alfreds Futterkiste ~~~
processed.
inserted Ana Trujillo
ANATR ~~~
***** CustomerAround
AHORN ID cannot be
the Horn ~~~ Emparedados y helados
modified Antonio Moreno
deleted ANTON ~~~
Taquería
AROUT Around the Horn ~~~ AROUT Around the Horn ~~~
How INSTEAD OF Triggers Work

Create a View That Combines Two or More Tables

UPDATE is Made Customers
to
SELECT * 1
CREATE VIEW Customers
the INSTEAD
View
FROM CustomersMex
OFASTrigger Can Be on a Table or View
CustomerID CompanyName Country Phone …
UNION ALFKI Alfreds Fu… Germany 030-0074321 ~~~
SELECT INSTEAD
* OF
2 directs
The
FROM CustomersGer
trigger
Action That Initiates
ANATRthe Trigger Does NOT
Ana Trujill… Occur(5) 555-4729 ~~~
Mexico

the update to ANTON Antonio M… Mexico (5) 555-3932 ~~~

3 base
the
CustomersMex
table
Allows Updates to Views Not Previously Updateable

Original
CustomerID Insert
CompanyName CountryCustomersGer
Phone …
ANATR to the
Ana Customers
Trujill… Mexico CustomerID CompanyName Country Phone …
ANTONView Does
Antonio M…Not Mexico ALFKI Alfreds Fu… Germany 030-0074321 ~~~
Occur BLAUS Blauer Se… Germany 0621-08460 ~~~
CENTC Centro Co… Mexico
DRACD Drachenb… Germany 0241-039123 ~~~
How Nested Triggers Work

Order_Insert Order Details

OrderID ProductID UnitPrice Quantity Discount
10522 10 31.00 7 0.2
10523 41 9.65 9 0.15
10524 7 30.00 24 0.0
10525 2 19.00 5 0.2
Placing an order causes the
Order_Insert trigger to execute
InStock_Update Products
ProductID UnitsInStock … …
1 15
2 15 Executes an UPDATE statement on
3 65 the Products table
4 20
InStock_Update trigger executes

UnitsInStock + UnitsOnOrder is Sends message

< ReorderLevel for ProductID 2
Recursive Triggers

Activating a Trigger Recursively

Types of Recursive Triggers
 Direct recursion
 Indirect recursion
Determining Whether to Use Recursive Triggers
Lesson: Implementing Triggers

Performance Considerations
Best Practices
Example: Auditing with Triggers
Example: Enforcing Business Rules
Performance Considerations

Triggers Work Quickly Because the Inserted and

Deleted Tables Are in Cache
Execution Time Is Determined by:
 Number of tables that are referenced
 Number of rows that are affected
Actions Contained in Triggers Implicitly Are Part
of a Transaction
Best Practices

 Use Triggers Only When Necessary

Keep Trigger Definition Statements as Simple

 as Possible
Include Recursion Termination Check Statements
 in Recursive Trigger Definitions
Minimize Use of ROLLBACK Statements
 in Triggers
Example: Auditing with Triggers

Employees
UPDATE Employees EmployeeID … Salary
SET Salary=40000 1 30,000
Updated
WHERE EmployeeId = 2 2 40,000
3 30,000
4 30,000

CREATE TRIGGER Salary_Audit Trigger

ON Employees FOR UPDATE AS… Inserts Row

AuditLog
EntryID EntryDate UserID Activity
Modified Salary of EmployeeID: 2
1 2005/6/2 dbo
From 30000 To 40000
Example: Enforcing Business Rules

Products with Outstanding Orders Cannot Be Deleted

IF (Select Count (*)
FROM [Order Details] INNER JOIN deleted
ON [Order Details].ProductID = deleted.ProductID
) > 0
ROLLBACK TRANSACTION

DELETE statement executed Trigger code checks the

on Product table Order Details table
Products Order Details
ProductID UnitsInStock … … OrderID ProductID UnitPrice Quantity Discount
1 15 10522 10 31.00 7 0.2
2 10 10523 2 19.00 9 0.15
3 65 10524 41 9.65 24 0.0
4 20 10525 7 30.00

'Transaction cannot be processed'

Transaction
'This product has order history'
rolled back
Lab A: Creating Triggers

Exercise 1: Creating Triggers

Exercise 2: Creating a Trigger for
Updating Derived Data
Exercise 3: Creating a Trigger That
Maintains a Complex Business Rule
Exercise 4: Testing the Firing Order of
Constraints and Triggers