Benefits of Triggers
In Chapter 7, "Enforcing Data Integrity," you learned how to enforce business rules by defining constraints. In some real case scenarios, you can find problems that cannot be solved by using constraints. However, using the programmatic capabilities of triggers, you can create complex rules with endless possibilities.
A trigger is a stored procedure that is called automatically whenever you execute the action to which the trigger is defined. You cannot call a trigger directly, but you can execute the action that fires the trigger.
A trigger does not accept parameters and cannot call the RETURN statement to return a value, but it can return results, as any other stored procedure, although it is not recommended. A trigger executes in the background and it shouldn't return anything other than error messages, if required.
You can define several triggers for every INSERT, UPDATE, or DELETE action, or any combination of them.
To define a trigger to fire automatically whenever you insert or update data in the Products table, you can use the simplified syntax from Listing 9.1. Later in this chapter, you will study the CREATE TRIGGER syntax in detail.
Code Listing 9.1. Use the CREATE TRIGGER Statement to Define Triggers
|
CREATE TRIGGER TriggerName ON Products
AFTER INSERT, UPDATE
AS
-- Here you write your program logic
Caution
You cannot define a trigger on a SELECT action because this action does not modify data.
When you have a trigger defined in a table, it does not matter how you modify the table. The trigger is always fired whether you write DML statements directly, execute stored procedures, or use a database library from a client application.
Tip
A trigger is defined in a table or view inside a database, but the code inside the trigger can refer to objects in other databases, providing a unique way to implement referential integrity between objects in different databases.
Using Triggers to Enforce Complex Domain Integrity
Think about an Employees table where you register employees'data. The field DriverNo stores the driver license number, and because not every employee drives, this field must accept NULL. However, you want to enforce uniqueness in this field because two employees cannot have the same driver license number. Let's consider different ways to enforce this rule:
You cannot create a UNIQUE constraint because you can have more than one employee without a driver's license and, as you remember, UNIQUE constraints can accept only one NULL value.
You cannot create a CHECK constraint to see whether you already have the same value in other rows in the same table, because a CHECK constraint cannot access other rows in the same table.
You cannot create a RULE object because these objects check for values in the selected field and affected row only.
You can create a stored procedure to insert data into the table, and inside the procedure you can check for uniqueness of this value. In this case, you need two stored procedures, one to insert data and another one to modify data. Because this business rule is enforced only through these two stored procedures, you must make sure that your client applications insert and update employees'data only through these stored procedures. System administrators can modify the base tables directly and break this business rule.
The solution could be a trigger that checks for uniqueness of the newly inserted value and either accepts or rejects the modification. This trigger could be implemented as detailed in Listing 9.2
Code Listing 9.2. You Can Create a Trigger to Enforce Complex Uniqueness
|
USE Northwind
GO
-- Add the DriverNo fields to the Employees table
ALTER TABLE Employees
ADD DriverNo varchar(15) NULL
GO
-- Create the trigger to check
-- for uniqueness of the DriverNo field
CREATE TRIGGER isrEmployees
ON Employees
FOR INSERT, UPDATE
AS
IF UPDATE (DriverNo)
IF EXISTS (
SELECT DriverNo, COUNT(*)
FROM Employees
WHERE DriverNo IS NOT NULL
AND DriverNo IN
(SELECT DISTINCT DriverNo
FROM Inserted)
GROUP BY DriverNo
HAVING COUNT(*) > 1)
BEGIN
RAISERROR ('Driver license number not unique, INSERT aborted', 16, 1)
ROLLBACK TRA6N
END
-- This statement succeeds
UPDATE Employees
SET DriverNo = '74914173'
WHERE EmployeeID = 5
-- This statement fails
-- the driverno is not unique
UPDATE Employees
SET DriverNo = '74914173'
WHERE EmployeeID = 6
-- This statement fails
-- Trying to insert multiple repeated
-- DriverNo values
UPDATE Employees
SET DriverNo = '74914175'
WHERE EmployeeID BETWEEN 6 AND 10
-- reset to NULL succeeds because
-- NULL values are not considered
-- in this trigger
UPDATE Employees
SET DriverNo = NULL
Server:Msg 50000, Level 16, State 1, Procedure isrEmployees, Line 16
Driver license number not unique, INSERT aborted
Triggers do not represent much overhead for SQL Server because their execution time depends mostly on data access to other tables. However, it is not advisable to create complex logic inside triggers.
Triggers always run inside the transaction context of the action that fires them.
Caution
Be careful when using ROLLBACK TRAN inside a trigger, because it cancels the execution of the batch that fired the trigger. The complete transaction is rolled back, including any other actions performed during the transaction scope
Using Triggers to Maintain Denormalized Data
Let's consider a different example. You want to know the total sales for product categories. You want to know this total for any given category at any time, and the results must reflect the actual data. You have several different ways to do this.
Use Aggregrate Functions
Execute the query from Listing 9.3 every time you want to obtain this information, modifying the value for CategoryName. This is not very efficient, because you must remember the complete syntax or save the query in a script or template. Every time you execute this query, SQL Server must parse, compile, and execute the query with the overhead associated to aggregate functions. If you rarely need to call this script, this could be an acceptable solution.
Code Listing 9.3. Use Aggregate Functions to Get Summary Information
|
USE Northwind
GO
SELECT P.CategoryID, C.CategoryName,
SUM(OD.UnitPrice * Quantity * (1 - Discount)) as Total
FROM [Order Details] OD
JOIN Products P
ON P.ProductID = OD.ProductID
JOIN Categories C
ON C.CategoryID = P.CategoryID
WHERE CategoryName = 'Confections'
GROUP BY P.CategoryID, C.categoryName
CategoryID CategoryName Total
----------- --------------- ------------------------------
3 Confections 167357.22483158112
Create a View
Create the TotalSalesByCategory view, as in Listing 9.4, to produce this result and select from this view filtering to the required CategoryName. This solution is flexible and provides good security control, but it does not provide any improvements in speed because the view definition must be merged with the outer query, and the final resulting query plan will be executed every time you use this view, with the overhead inherent to the use of aggregate functions. This could be an adequate solution if the view is not going to be executed frequently.
Code Listing 9.4. Create a View to Retrieve Summary Information.
|
USE Northwind
GO
-- Create the view
CREATE VIEW TotalSalesByCategory
AS
SELECT P.CategoryID, C.CategoryName,
SUM(OD.UnitPrice * Quantity * (1 - Discount)) as Total
FROM [Order Details] OD
JOIN Products P
ON P.ProductID = OD.ProductID
JOIN Categories C
ON C.CategoryID = P.CategoryID
GROUP BY P.CategoryID, C.categoryName
GO
-- Use the view to search
-- for 'Confections'totals
SELECT *
FROM TotalSalesByCategory
WHERE CategoryName = 'Confections'
Create a Stored Procedure
Create the stored procedure GetSalesByCategory, as in Listing 9.5, to produce the required results for a given category. This solution can be efficient and faster than the previous ones, due to the reuse of the query plan. If this procedure is not frequently used, compared to the insert and update operations on the underlying tables, this solution is sufficiently efficient.
Code Listing 9.5. Create a Stored Procedure to Retrieve Summary Information
|
-- Create the procedure
CREATE PROCEDURE GetSalesByCategory
@CatName nvarchar(15)
AS
SELECT P.CategoryID, C.CategoryName,
SUM(OD.UnitPrice * Quantity * (1 - Discount)) as Total
FROM [Order Details] OD
JOIN Products P
ON P.ProductID = OD.ProductID
JOIN Categories C
ON C.CategoryID = P.CategoryID
WHERE CategoryName = @CatName
GROUP BY P.CategoryID, C.categoryName
GO
-- Use the procedure to search
-- for 'Confections'totals
EXEC GetSalesByCategory 'Confections'
Note
The query plan of the last three examples is exactly the same, so it is the execution performance. The latest method, using a stored procedure, is more efficient for repeated execution because it avoids the need for parsing, optimizing, and compiling the query. The query plan can be reused more easily than direct queries, and it produces lower network traffic .
Use Triggers
The fastest way to retrieve this information is to have the precomputed total in a new table TotalCategoriesSales, and create triggers, as in Listing 9.6, to maintain this information automatically, whenever you change data in the Order Details table. If your more frequent query is to get these totals, you should provide this information as fast as possible. In this case, retrieving these values is highly efficient, but modifying data is less efficient because triggers must run after every data modification.
Code Listing 9.6. Use Triggers to Maintain Summary Data
|
USE Northwind
GO
-- Create denormalized table
CREATE TABLE TotalCategoriesSales
(CategoryID int NOT NULL PRIMARY KEY,
CategoryName nvarchar(15) NOT NULL,
TotalSales money DEFAULT 0)
-- Synchronization Procedure
-- Including initial data population
CREATE PROCEDURE SyncTotalCategoriesSales
AS
TRUNCATE TABLE TotalcategoriesSales
INSERT TotalCategoriesSales
SELECT CategoryID, CategoryName, 0
FROM Categories
UPDATE TC
SET TotalSales =
(SELECT SUM(OD.UnitPrice * Quantity * (1 - Discount))
FROM [Order Details] OD
JOIN Products P
ON P.ProductID = OD.ProductID
WHERE P.CategoryID = TC.categoryID)
FROM TotalCategoriesSales TC
GO
-- Synchronize totals
-- We can run this procedure if necessary
EXEC SyncTotalcategoriesSales
GO
CREATE TRIGGER modOrderDetails
ON [Order Details]
AFTER INSERT, UPDATE, DELETE
AS
UPDATE TC
SET TotalSales = TotalSales
- D.UnitPrice * Quantity * (1 - Discount)
FROM TotalCategoriesSales TC
JOIN Products P
ON P.CategoryID = TC.CategoryID
JOIN Deleted D
ON D.ProductID = P.productID
UPDATE TC
SET TotalSales = TotalSales
+ I.UnitPrice * Quantity * (1 - Discount)
FROM TotalCategoriesSales TC
JOIN Products P
ON P.CategoryID = TC.CategoryID
JOIN Inserted I
ON I.ProductID = P.productID
GO
-- Select initial values
PRINT CHAR(10) + 'Initial summary values'+ CHAR(10)
SELECT *
FROM TotalCategoriesSales
-- Insert a new Order
INSERT Orders (CustomerID)
SELECT 'ALFKI'
DECLARE @id int
SET @id = @@IDENTITY
-- Show the @@identity for later reference
SELECT @id AS OrderID
-- Sell 10 units of Ikura (cat 8) at $30.00 and 10% discount
PRINT CHAR(10) + 'Insert Ikura (cat 8) Order'+ CHAR(10)
INSERT [Order Details]
(orderID, ProductID, UnitPrice, Quantity, Discount)
VALUES (@id, 10, 30, 10, 0.1)
-- Sell 20 units of Tofu (cat 7) at $20.00 and 20% discount
PRINT CHAR(10) + 'Insert Tofu (cat 7) Order'+ CHAR(10)
INSERT [Order Details]
(orderID, ProductID, UnitPrice, Quantity, Discount)
VALUES (@id, 14, 20, 100, 0.2)
-- Sell 5 units of Queso Cabrales (cat 4) at $20.00 and no discount
PRINT CHAR(10) + 'Insert Queso Cabrales (cat 4) Order'+ CHAR(10)
INSERT [Order Details]
(orderID, ProductID, UnitPrice, Quantity, Discount)
VALUES (@id, 11, 20, 5, 0.0)
-- Test the new totals
SELECT *
FROM TotalCategoriesSales
-- Update quantity of the Queso Cabrales to 100
PRINT CHAR(10) + 'Increase Queso Cabrales (cat 4) to 100'+ CHAR(10)
UPDATE [Order Details]
SET Quantity = 100
WHERE OrderID = @id
AND ProductID = 11
-- Test the new totals
SELECT *
FROM TotalCategoriesSales
-- Remove Tofu from this order
PRINT CHAR(10) + 'Remove Tofu (cat 7) Order'+ CHAR(10)
DELETE [Order Details]
WHERE OrderID = @id
AND ProductID = 14
-- Test the new totals
SELECT *
FROM TotalCategoriesSales
-- remove the order completely
PRINT CHAR(10) + 'Remove Ikura (cat 8) Order'+ CHAR(10)
DELETE [Order Details]
WHERE OrderID = @id
AND ProductID = 10
PRINT CHAR(10) + 'Remove Queso Cabrales (cat 4) Order'+ CHAR(10)
DELETE [Order Details]
WHERE OrderID = @id
AND ProductID = 11
DELETE Orders
WHERE OrderID = @id
-- Test the new totals
SELECT *
FROM TotalCategoriesSales
Initial summary values
CategoryID CategoryName TotalSales
----------- --------------- ---------------------
1 Beverages 267868.1805
2 Condiments 106047.0850
3 Confections 167357.2248
4 Dairy Products 234507.2813
5 Grains/Cereals 95744.5875
6 Meat/Poultry 163022.3591
7 Produce 99984.5781
8 Seafood 131261.7344
OrderID
-----------
11083
Insert Ikura (cat 8) Order
Insert Tofu (cat 7) Order
Insert Queso Cabrales (cat 4) Order
CategoryID CategoryName TotalSales
----------- --------------- ---------------------
1 Beverages 267868.1805
2 Condiments 106047.0850
3 Confections 167357.2248
4 Dairy Products 234607.2813
5 Grains/Cereals 95744.5875
6 Meat/Poultry 163022.3591
7 Produce 101584.5781
8 Seafood 131531.7344
Increase Queso Cabrales (cat 4) to 100
CategoryID CategoryName TotalSales
----------- --------------- ---------------------
1 Beverages 267868.1805
2 Condiments 106047.0850
3 Confections 167357.2248
4 Dairy Products 236507.2813
5 Grains/Cereals 95744.5875
6 Meat/Poultry 163022.3591
7 Produce 101584.5781
8 Seafood 131531.7344
Remove Tofu (cat 7) Order
CategoryID CategoryName TotalSales
----------- --------------- ---------------------
1 Beverages 267868.1805
2 Condiments 106047.0850
3 Confections 167357.2248
4 Dairy Products 236507.2813
5 Grains/Cereals 95744.5875
6 Meat/Poultry 163022.3591
7 Produce 99984.5781
8 Seafood 131531.7344
Remove Ikura (cat 8) Order
Remove Queso Cabrales (cat 4) Order
CategoryID CategoryName TotalSales
----------- --------------- ---------------------
1 Beverages 267868.1805
2 Condiments 106047.0850
3 Confections 167357.2248
4 Dairy Products 234507.2813
5 Grains/Cereals 95744.5875
6 Meat/Poultry 163022.3591
7 Produce 99984.5781
8 Seafood 131261.7344
Caution
The example from Listing 9.6 only works with single-row operations. Later in this chapter you will see how to deal with multirow operation using the same example.
Note
To complete the example, you should create triggers in the Products and Categories tables as well, because a product could be moved to a new category, and a category can change its name.
You could drop the CategoryName field from the TotalCategoriesSales table, but in that case you have to join to the Categories table to filter the category by name. After you denormalized your database to maintain summary values, why not denormalize one step further? In this way, you need to access only one table to produce the required results.
Use Indexed Views
A different possibility would be to create an index on the SalesByCategory view, as in Listing 9.7. In this case, no triggers are necessary to maintain this information, and the Query Optimizer can decide to use this view to solve queries requesting data compatible with the data generated by this view, even if the queries do not reference the view. This is the optimal solution for summary data retrieval, but it is not as efficient as the solution based on triggers—if you expect many data updates.
Code Listing 9.7. Use Indexed Views to Retrieve Precomputed Summary Information
|
-- DROP the existing view
IF OBJECT_ID('TotalSalesByCategory') IS NOT NULL
DROP VIEW TotalSalesByCategory
GO
-- Setting required to create an indexed view
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF
GO
-- Create the view
-- With Schemabinding
-- and use two part names for objects
CREATE VIEW TotalSalesByCategory
WITH SCHEMABINDING
AS
SELECT P.CategoryID, C.CategoryName,
SUM(OD.UnitPrice * Quantity * (1 - Discount)) as Total,
COUNT_BIG(*) as CB
FROM dbo.[Order Details] OD
JOIN dbo.Products P
ON P.ProductID = OD.ProductID
JOIN dbo.Categories C
ON C.CategoryID = P.CategoryID
GROUP BY P.CategoryID, C.categoryName
GO
-- Create the index on the view
CREATE UNIQUE CLUSTERED INDEX ndx_CatTotals
ON TotalSalesByCategory (CategoryName)
-- Use the view to search
-- for 'Confections'totals
-- set SHOWPLAN_TEXT
-- to show that the view is used
-- instead of the base tables
SET SHOWPLAN_TEXT ON
SELECT CategoryID, CategoryName, Total
FROM TotalSalesByCategory (NOEXPAND)
WHERE CategoryName = 'Confections'
StmtText
---------
|--Clustered Index Scan( OBJECT:([Northwind].[dbo].[TotalSalesByCategory]
.[ndx_CatTotals]), WHERE:([TotalSalesByCategory].[CategoryName]='Confections'))