Expressions and Computed Columns in INSTEAD OF Triggers
The select list of a view can have expressions other than simple expressions made up of only a column name. INSTEAD OF triggers on these views must have logic to correctly determine from the values specified on INSERT and UPDATE what values must be set into columns in the base table. Examples of such expressions include the following:
- View expressions that do not map to any column in any table, such as a constant or some types of functions.
- View expressions that map to multiple columns, such as complex expressions formed by concatenating strings from two or more columns.
- View expressions that transform the value of a single base table column, such as referencing a column in a function.
These issues also apply to view columns that are simple expressions referencing a computed column in a base table. The expression defining the computed column can have the same form as a more complex expression in the view select list.
Views can contain expressions in their select list that do not map to any base table columns, for example:
CREATE VIEW ExpressionView
AS
SELECT *, GETDATE() AS TodaysDate
FROM AdventureWorks.HumanResources.Employee
Although the TodaysDate column does not map to any table column, SQL Server 2005 must build a TodaysDate column in the inserted table it passes to an INSTEAD OF trigger defined on ExpressionView. However, the inserted.TodaysDate column is nullable; therefore, an INSERT statement that is referencing ExpressionView does not have to supply a value for this column. Because the expression does not map to a column in a table, the trigger can ignore any value supplied by the INSERT in this column.
The same approach should be applied to simple view expressions that reference computed columns in base tables that also build a result that is not dependent on other columns, for example:
CREATE TABLE ComputedExample
(
PrimaryKey int PRIMARY KEY,
ComputedCol AS SUSER_NAME()
)
Some complex expressions map to multiple columns, for example:
CREATE TABLE SampleTable
(
PriKey int,
FirstName nvarchar(20),
LastName nvarchar(30)
)
GO
CREATE VIEW ConcatView
AS
SELECT PriKey, FirstName + ' ' + LastName AS CombinedName
FROM SampleTable
The expression CombinedName in ConcatView has the concatenated values of the FirstName and LastName values. If an INSTEAD OF INSERT trigger is defined on ConcatView, you must have a convention for how INSERT statements supply a value for the CombinedName column that lets the trigger determine which part of the string should be put in the FirstName column and which part should be put in the LastName column. If you choose a convention of having INSERT statements specify the value of CombinedName by using the convention 'first_name;last_name', the following trigger can successfully process an INSERT:
CREATE TRIGGER InsteadSample on ConcatView
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO SampleTable
SELECT PriKey,
-- Pull out the first name string.
SUBSTRING(
CombinedName,
1,
(CHARINDEX(';', CombinedName) - 1)
),
-- Pull out the last name string.
SUBSTRING(
CombinedName,
(CHARINDEX(';', CombinedName) + 1),
DATALENGTH(CombinedName) - (CHARINDEX(';', CombinedName) + 1)
)
FROM inserted
END
Similar logic is required to process view columns that are simple expressions referring to computed columns that have complex expressions.
Some view expressions can transform the value of a base table column, for example, by performing a mathematical operation or using the column as a parameter to a function. In this case, the logic in the INSTEAD OF INSERT trigger can take two approaches:
The convention can be that all INSERT statements supply the raw value to place in the base table, and the trigger logic moves the value from the inserted table to the base table.
The convention can be that all INSERT statements supply the value they expect to have returned by a SELECT on the view, in which case the logic in the trigger must reverse the operation. For example:
CREATE TABLE BaseTable ( PrimaryKey int PRIMARY KEY, ColumnB int, ColumnC decimal(19,3) ) CREATE VIEW SquareView AS SELECT PrimaryKey, ColumnB, -- Square the value of ColumnC SQUARE(ColumnC) AS SquareC FROM BaseTable CREATE TRIGGER SquareTrigger ON SquareView INSTEAD OF INSERT AS BEGIN INSERT INTO BaseTable SELECT PrimaryKey, ColumnB, -- Perform logical inverse of function in view. SQRT(SquareC) FROM inserted END
For some expressions, such as complex expressions using mathematical operations such as addition and subtraction, users may not be able to supply a value that the trigger can use to unambiguously build values for the destination base table columns. For example, if a view select list contains the expression IntColA + IntColB AS AddedColumns, what does a value of 10 in inserted.AddedColumns mean? Is 10 the result of 3 + 7, 2 + 8, or 5 + 5? There is no way to tell from the value of inserted.AddedColumns alone what values should be placed in IntColA and IntColB.
In these cases, the trigger can be coded to use alternative sources of information to determine the values to set in the base table columns. For views that have INSTEAD OF triggers, the view select list must contain enough information to build values for all nonnull columns in the base tables modified by the trigger. Not all data must come directly from the inserted table. In some cases, the values in the inserted table can be key values that the trigger uses to retrieve the relevant data from other base tables.