Chapter 37 - Extending Triggers with INSTEAD OF

In Microsoft® SQL Server™ 2000, the data manipulation and data-integrity features of tables and views have been significantly enhanced with the introduction of INSTEAD OF triggers. Using INSTEAD OF triggers, you can enforce new business rules by extending or replacing the normal triggering actions of an INSERT, UPDATE, or DELETE statement. Using INSTEAD OF triggers in views, you can:

• Update multiple tables through a single view. 

• Enforce more complex restrictions on columns than CHECK constraints allow. 

• Evaluate the state of a table before and after modifying data and performing additional actions, such as modifying other tables or displaying a customized error message. 

• Allow parts of a batch to be rejected while allowing other parts of the batch to succeed. 

This chapter presents code examples illustrating the use of INSTEAD OF triggers, design guidelines, and optimization recommendations. The code examples are also available on the SQL Server 2000 Resource Kit CD-ROM in file, \Docs\ChapterCode\CH37Code.txt. For more information, see Chapter 39, "Tools, Samples, eBooks, and More," in this book.

What Are INSTEAD OF Triggers?

Similar to AFTER triggers (FOR triggers in earlier versions of SQL Server), INSTEAD OF triggers are special stored procedures that perform user-defined actions and are activated by INSERT, UPDATE, or DELETE statements. Triggers fire (execute) each time an INSERT, UPDATE, or DELETE statement executes on a specified table or view. A single trigger can apply to one specific triggering action (for example, INSTEAD OF UPDATE) or to multiple actions (INSTEAD OF INSERT, UPDATE, DELETE) in which the same trigger is applied to all the actions listed. As with the AFTER trigger, the INSTEAD OF trigger uses the logical inserted and deleted tables to store the modified records while the trigger is active. Each column in these tables maps directly to a column in the base table referenced in the trigger. For example, if table T1 contains the columns C1, C2, C3, and C4, the inserted and deleted tables will contain the values for all four columns, even if the trigger only modifies columns C1 and C3. SQL Server automatically creates and manages the inserted and deleted tables as memory-resident objects.

An INSTEAD OF trigger differs from an AFTER trigger by when the trigger is fired, what actions are performed, and what objects the trigger can be created on. The actions defined in the AFTER trigger code are supplemental to the triggering action and occur only after constraint processing has been applied and the transaction created by the triggering action has been committed. For example, an AFTER trigger attached to an Order Detail table may update an Order Summary table whenever the Order Detail table is modified. In contrast with the AFTER trigger, the INSTEAD OF trigger fires before constraint processing and replaces the normal triggering action with the actions defined in the trigger. For example, an INSTEAD OF trigger attached to a view of historical sales data can prevent the data from being modified by replacing the insert, update, and delete triggering actions with a customized error message. Because the INSTEAD OF trigger supersedes the triggering action, the data modification that caused the trigger to execute in this example is never executed. The INSTEAD OF trigger code must include the appropriate INSERT, UPDATE, or DELETE statements if those actions are required. Executing the INSERT, UPDATE, or DELETE statement from inside the INSTEAD OF trigger code will not fire the same trigger again; instead, the insert, update, or delete action is performed.

Customizing Error Messages with INSTEAD OF Triggers

You can customize error messages and perform complex error handling such as validating columns in the inserted or deleted tables with columns in other tables with INSTEAD OF triggers. Because the INSTEAD OF trigger fires before constraints are applied, you can perform error checking prior to the data changes and either continue with the triggering action if there are no errors, or perform other actions such as displaying an error message and inserting the erroneous data into an error log table. From within the INSTEAD OF trigger, different actions can be performed for different inserted, updated, or deleted records.

In the following example, the T_ProtectSalaryUpdate INSTEAD OF trigger:

• Checks individual updated rows, allowing those that meet the trigger criteria to succeed and others to fail.

• Produces customized messages. 

• Enforces complex rules that cannot be enforced using simple constraints. 

The trigger is based on a new Salary table. Code Example 37.1 creates this table in the Northwind sample database. The table contains the columns EmployeeID, SalaryClass, and Salary. The EmployeeID uses a reference to the EmployeeID column in the table Employees to ensure that the value entered refers to a valid employee. SalaryClass holds the payment frequency (for example, monthly or weekly), and Salary contains the current salary amount. The trigger, as shown in Code Example 37.2, ensures that modifications to the Salary table are not performed on the first day of any month, because this could interfere with the processing of monthly salaries. Modifications made on the first of the month are rejected, and a customized error message is displayed. Although rowset-based logic is recommended, this example uses a cursor to allow batch updates to the table.

Code Example 37.1 

-- ==============================================================================
-- Create the Salary table for use with the T_ProtectSalaryUpdate trigger.
-- ==============================================================================
CREATE TABLE Salary (EmployeeID int References Employees (EmployeeID), 
SalaryClass int not null, 
Salary int not NULL)

Code Example 37.2 

-- =============================================
-- This INSTEAD OF trigger shows how the INSTEAD OF trigger may be used to:
-- Check individual updated rows, allowing those that meet the trigger criteria 
-- to succeed and others to fail. 
-- Produce customized messages.
-- Enforce complex rules that cannot be enforced using simple constraints.

-- =============================================
CREATE TRIGGER T_ProtectSalaryUpdateTwo ON Salary
INSTEAD OF Update
AS 
BEGIN
DECLARE @EmployeeID int, @SalaryClass int, @Salary int, @OldSalary int
DECLARE OneChange CURSOR LOCAL READ_ONLY FORWARD_ONLY STATIC 
FOR SELECT [EmployeeID], [SalaryClass], [Salary] FROM inserted
OPEN OneChange
FETCH NEXT FROM OneChange INTO @EmployeeID, @SalaryClass, @Salary 
WHILE (@@FETCH_STATUS=0)
BEGIN
SELECT @OldSalary=Salary 
FROM Salary 
WHERE EmployeeID=@EmployeeID
IF ((DATEPART(day,getdate())= 1) AND (@OldSalary <> @Salary))
PRINT 'Error. Salary change for employee '+ Cast(@EmployeeID as varchar(10))+ ' cannot be changed on the first day of the month.'
ELSE 
UPDATE Salary SET Salary=@Salary, SalaryClass=@SalaryClass where EmployeeID=@EmployeeID
FETCH NEXT FROM OneChange INTO @EmployeeID, @SalaryClass, @Salary
END
END

Code Example 37.3 inserts test data into the Salary table and tests the triggering action. You may want to temporarily reset your system date to the first of the month to see the trigger results when an updated row fails.

Code Example 37.3 

-- ==============================================================================
-- Test the T_ProtectSalaryUpdate INSTEAD OF TRIGGER.
-- These modifications should fail and produce the custom error message.
-- ==============================================================================
USE Northwind
GO
-- Add test data to the Salary table for later testing. 
INSERT INTO Salary
VALUES (1,1, 3500)
INSERT INTO Salary
VALUES (2,1, 5000)
INSERT INTO Salary
VALUES (3,4,1000)
GO
--This UPDATE statement will fail if the current date is the first of the month.
UPDATE Salary
SET "Salary" = 3750
WHERE "EmployeeID" = 1
GO
-- This UPDATE statement will succeed because only the SalaryClass column is being updated.
UPDATE Salary
SET "SalaryClass" = 4
WHERE "EmployeeID" = 3
GO

When UPDATE statements are executed, the inserted table contains the new values supplied in the UPDATE statement, and the deleted table contains the current values for each column in the database. This allows you to compare the existing data with the updated values before the transaction is committed to the database.

To force the UPDATE statement to fail, temporarily set your system date to the first day of the month and execute this UPDATE statement:

UPDATE Salary
SET "SalaryClass" = 4000
WHERE "EmployeeID" = 1
GO

Additional triggers would need to be applied to the table to cover the insert and delete triggering actions.

Creating Updatable Views with INSTEAD OF Triggers

In earlier versions of SQL Server, views could only modify data in a single base table. In SQL Server 2000, using INSTEAD OF triggers, you can modify multiple base tables from the view and manage the complexities associated with multiple table updates.

In the following example, the INSTEAD OF trigger T_InsertInvoice demonstrates:

• Inserting data into multiple base tables. 

• Resolving foreign key constraints between tables. 

• Excluding columns not requiring modification. 

• Handling columns containing system generated data. 

When you update base tables through a view, it is important to understand the relationships among the base tables, how the view will be used, and other database features, such as data types or constraints that may affect the actions required of the trigger. Permissions granted on the view and underlying base tables must also be considered. If the same user owns both the view and the base table, user permissions to read or update has to be granted only on the view. When the view owner does not own a base table, their respective owners must grant user permissions separately for both the view and the base table.

In the following example, the INSTEAD OF INSERT trigger is attached to the Invoices view (part of the Northwind sample database) and modifies columns in the Orders and Order Details tables. The Invoice view would probably be used in a Point of Sale (POS) application or as the basis for a data entry form developed in a product, such as Microsoft Access. The Invoice view references the Orders, Order Details, Shippers, Employees, and Products tables; however, only columns in the Orders and Order Details tables require modification. The Shippers, Employee, and Products tables are used to verify existing information.

A FOREIGN KEY constraint on the OrderID column keeps the Orders and Order Details tables synchronized, which must be enforced in the trigger. The OrderID column in the Orders table uses the IDENTITY property to determine the OrderID value, and this data must be passed to the Order Details table. In addition, the Invoices view references the EmployeeName and the shipper's CompanyName columns, but the Orders table records the Employee ID and the Shipper ID columns. To insert the correct data into the Orders table, the SELECT statement in the trigger must reference the Shipper and Employees tables to extract the corresponding ID data. The example assumes that a single row is being inserted. The example is not designed to handle multiple row INSERT statements.

Code Example 37.4 

-- =============================================

-- Create INSTEAD OF INSERT trigger to insert new
-- records through the Invoices view to
-- multiple base tables Orders and Order Details.
-- =============================================
USE Northwind
GO
IF EXISTS (SELECT name FROM sysobjects 
WHERE name = 'T_InsertInvoice' 
AND type = 'TR')
DROP TRIGGER T_InsertInvoice
GO
CREATE TRIGGER T_InsertInvoice ON Invoices INSTEAD OF INSERT
AS
BEGIN
-- Insert the Invoices information into the Orders table from the inserted table.
-- Get the EmployeeID and ShipperID information from the Employees and Shippers tables.
INSERT INTO Orders (CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate,
Freight, ShipVia, ShipName, ShipAddress, ShipCity, ShipRegion, 
ShipPostalCode, ShipCountry)
SELECT
inserted.CustomerID,Employees.EmployeeID,inserted.OrderDate,inserted.RequiredDate,
inserted.ShippedDate,inserted.Freight,Shippers.ShipperID,inserted.ShipName,
inserted.ShipAddress, inserted.ShipCity, inserted.ShipRegion,
inserted.ShipPostalCode, inserted.ShipCountry
FROM Employees, inserted, Shippers
WHERE inserted.Salesperson = (Employees.FirstName + ' ' + Employees.LastName)
and inserted.ShipperName = Shippers.CompanyName
-- Insert the Invoices information into the Order Details table.
-- Get the current OrderID from the inserted table.
INSERT INTO "Order Details"
SELECT SCOPE_IDENTITY() AS OrderID,ProductID,UnitPrice,Quantity,Discount
FROM inserted
COMMIT TRANSACTION
END

To test the trigger, execute the sample data in Code Example 37.5.

Code Example 37.5 

INSERT INTO Invoices (CustomerID,OrderDate, RequiredDate, ShippedDate,ShipperName,
Freight,ShipName,ShipAddress, ShipCity, ShipRegion,
ShipPostalCode,
ShipCountry,ProductID,UnitPrice,Quantity,Discount,
CustomerName,Salesperson,OrderID,ProductName)
VALUES ('SAVEA',
GETDATE(),
GETDATE() + 10,
GETDATE() + 3,
'Speedy Express',
214.27,
'Save-a-lot Markets',
'187 Suffolk Ln.',
'Boise',
'ID',
'83720',
'USA',
16,
13.9000,
21,
0.15000001,
'Save-a-lot Markets',
'Anne Dodsworth',
IDENT_CURRENT('Orders')+ 1,
'Pavlova'
)

Handling NOT NULL Values and Computed Columns in Updatable Views with INSTEAD OF Triggers

When you use INSTEAD OF triggers to modify base tables through a view, certain rules apply to INSERT and UPDATE statements referencing base-table columns that are defined with one of these attributes:

• NOT NULL

• NOT NULL with a DEFAULT definition 

  Columns for which input values are not allowed, but may be treated by SQL Server as not nullable:

  • Computed columns 

  • IDENTITY columns for which IDENTITY INSERT is set to OFF 

  • Columns with the timestamp data type 

Columns with one of these NOT NULL definitions are treated differently in the column list of an INSERT or UPDATE statement, depending on whether the statement references the updatable view or is embedded in the INSTEAD OF trigger code. INSERT and UPDATE statements referencing the view must supply values for every base table column defined with the NOT NULL attributes listed above. This is required to satisfy the NOT NULL requirement for those columns. However, when the INSERT or UPDATE statements are part of the INSTEAD OF trigger logic, the treatment of these columns varies. Modified columns explicitly defined as NOT NULL or NOT NULL with a DEFAULT value must be included in the column list of the INSERT or UPDATE statement. However, the computed IDENTITY or timestamp data type columns must not be included in column list of these statements. When these columns are not listed, the INSTEAD OF trigger ignores the values passed by the INSERT statement referencing the view, and the correct values are set by SQL Server.

Columns that are defined as nullable can be included or excluded in the column list of INSERT or UPDATE statements under both conditions.

This table summarizes the treatment of NOT NULL columns, system-generated columns, and nullable columns in the column list of INSERT and UPDATE statements.

To meet the NOT NULL requirement for these columns, the INSERT or UPDATE statement referencing the view may pass dummy values for those columns that are system generated. For example, a view CurrentInventory references the base table Inventory, which contains the computed column TotalQuantity defined as QtyOnOrder + QtyInStock. A value must be passed for the TotalQuantity column, but that value does not need to represent the actual computed value. In the following example, zero (0) is passed as the dummy value for this column; however, SQL Server generates the actual value inserted into the database.

Code Example 37.6 

-- =============================================
-- Create the Inventory table
-- with a computed column TotalQuantity.
-- =============================================
USE Northwind
GO
CREATE TABLE Inventory
(PartNumber char(10) NOT NULL,
Description varchar(30) NOT NULL DEFAULT 'Unknown',
QtyOnOrder smallint,
QtyInStock smallint,
TotalQuantity AS (QtyOnOrder + QtyInStock),
LastModified datetime NULL DEFAULT GETDATE())
GO

-- =============================================
-- Create the CurrentInventory view
-- selecting all columns from the Inventory table.
-- =============================================
IF EXISTS (SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.VIEWS 
WHERE TABLE_NAME = N'CurrentInventory')
DROP VIEW CurrentInventory
GO
CREATE VIEW CurrentInventory
AS 
SELECT PartNumber, Description, QtyOnOrder, QtyInStock, TotalQuantity, LastModified
FROM Inventory
GO

Bypassing the Computed Column in the INSTEAD OF Trigger

The INSERT statement in the INSTEAD OF INSERT trigger lists QtyOnOrder and QtyInStock, but not the computed column TotalQuantity.

Code Example 37.7 

CREATE TRIGGER T_InsertInventory ON CurrentInventory
INSTEAD OF INSERT AS
BEGIN
INSERT INTO Inventory (PartNumber, Description, QtyOnOrder, QtyInStock)
SELECT PartNumber, Description, QtyOnOrder, QtyInStock
FROM inserted
END

Including the Computed Column when Referencing the View

The INSERT statement referencing the CurrentInventory view in the following example will succeed. Because the INSTEAD OF trigger code does not include the TotalQuantity column, the value 0 is ignored and the column is computed correctly.

Code Example 37.8 

INSERT INTO CurrentInventory (PartNumber,Description, QtyOnOrder, QtyInStock,TotalQuantity)
VALUES ('LA-61-4679', 'Unknown', 100,25,0)

Executing a SELECT statement on the CurrentInventory view would return a result set similar to that shown in the table.

INSTEAD OF Triggers on Partitioned Views

Tables that have been spread across one or more databases or servers can be updated by using a partitioned view. However, several restrictions are associated with partitioned views that may prevent their use. You can use INSTEAD OF triggers to replace the actions of the partitioned view command. For more information about partitioned views, see Chapter 38, "Scaling Out on SQL Server," in this book.

Guidelines for Designing INSTEAD OF Triggers

INSTEAD OF triggers are often used to enforce specific business rules or apply application logic and error handling. They can add flexibility by providing cross-table or cross-database referential integrity, and they work no matter what caused the data modification: a data entry form, a report calculation, or a

user interface application. This section provides several guidelines to consider when you design tables and updatable views.

Restrictions of INSTEAD OF Triggers

When you design INSTEAD OF triggers, consider these restrictions:

• INSTEAD OF triggers are not allowed on updatable views WITH CHECK OPTION. 

• Only one INSTEAD OF trigger per INSERT, UPDATE, or DELETE statement can be defined on a table or view. 

• The CREATE TRIGGER statement must be the first statement in the batch. All other statements that follow in that batch are interpreted as part of the definition of the CREATE TRIGGER statement. 

• Permission to create triggers defaults to the table owner, who cannot transfer it to other users. 

• Triggers are database objects, and their names must follow the rules for identifiers. 

• You can create a trigger only in the current database, although a trigger can reference objects outside of the current database. 

• A trigger cannot be created on a temporary or system table, although triggers can reference temporary tables. System tables should not be referenced; use the information schema views instead. 

• The WRITETEXT statement does not cause the INSERT or UPDATE triggers to fire. 

Constraints vs. Triggers

You should use constraints rather than triggers to enforce rules or default values whenever possible. Constraints usually perform the same task faster than triggers. Entity integrity should always be enforced at the lowest level by indexes that are part of PRIMARY KEY and UNIQUE constraints. Domain integrity should be enforced by CHECK constraints, and referential integrity enforced through FOREIGN KEY constraints.

Triggers are most useful when the features supported by constraints cannot meet the needs of the application, for example:

• A CHECK constraint can validate a column value only against a logical expression or another column in the same table. If your application requires that a column value be validated against a column in another table, you must use a trigger. 

• Constraints can only display standardized system error messages. Customized error messages and more complex error handling require the use of triggers. 

• Constraints are checked at the statement level, but INSTEAD OF triggers can be programmed to perform checks selectively and to allow some of the modifications to succeed and others not.

If constraints exist on the trigger table, they are checked after the INSTEAD OF trigger execution. If the constraints are violated, the INSTEAD OF trigger actions are rolled back.

Using the bcp Utility and BULK INSERT with INSTEAD OF Triggers

All bulk copy operations (the BULK INSERT statement, the bcp utility, and the bulk copy API) support a bulk copy hint, FIRE_TRIGGERS. If FIRE_TRIGGERS is specified on a bulk copy operation that is copying rows into a table, INSERT and INSTEAD OF triggers defined on the destination table are executed once for all rows inserted by the bulk copy operation. By default, bulk copy operations do not execute triggers.

These considerations apply to bulk copy operations that specify FIRE_TRIGGERS:

• Bulk copy operations that would usually be minimally logged are fully logged. 

• Triggers are fired once for each batch in the bulk copy operation. The inserted table passed to the trigger contains all of the rows inserted by the batch. Specify FIRE_TRIGGERS only when bulk copying into a table with INSERT and INSTEAD OF triggers that support multiple-row inserts. 

• No result sets generated by the insert triggers are returned to the client performing the bulk copy operation. 

Order of Trigger Execution

It is important to understand the order of event execution when using an INSTEAD OF trigger to modify data through a view. INSTEAD OF triggers are invoked after the inserted and deleted tables are created. If the UPDATE statement in the trigger references the same view as the trigger, the trigger is not called recursively. Rather, the UPDATE statement is applied directly to the base tables. Each UPDATE statement must reference a single table and begins the chain of applying all constraints and AFTER triggers defined on the base tables.

Performance Guidelines for INSTEAD OF Triggers

The overhead for trigger performance is usually very low. The time involved in running a trigger is spent mostly in referencing other tables, which may be in memory or on the disk. The deleted and inserted tables are always in memory because they are logical tables. The location of the other tables referenced by the trigger determines the amount of time the operations requires.

Consider these optimization guidelines:

• Minimize the number of tables referenced to reduce time spent referencing tables on disk. 

• Minimize the number of rows affected by the trigger. 

• Avoid text, ntext, and image columns with INSTEAD OF triggers because the query is built on the fly. The insert and delete virtual tables will contain the data for those columns and may result in slow processing of the trigger. 

  Take these actions to optimize SELECT statements in the trigger:

  • Include appropriate indexes. 

  • Minimize the number of referenced tables. 

  • Minimize the result set affected by the query. 

• Avoid the use of cursors in triggers. Cursors can result in poor performance and are not recommended. Use rowset-based logic rather than cursors for triggers affecting multiple rows.