Expresiones y columnas calculadas de los desencadenadores INSTEAD OF
La lista de selección de una vista puede tener expresiones que no sean expresiones sencillas compuestas sólo por un nombre de columna. Los desencadenadores INSTEAD OF de estas vistas deben tener una lógica para determinar correctamente los valores que deben definirse en columnas de la tabla base, a partir de los valores especificados en INSERT y UPDATE. Ejemplos de estas expresiones son:
Expresiones de vistas que no se asignan a ninguna columna de tabla, como una constante o algunos tipos de funciones.
Expresiones de vistas que se asignan a varias columnas, como expresiones complejas formadas por cadenas concatenadas procedentes de dos o más columnas.
Expresiones de vistas que transforman el valor de una sola columna de tabla base, como la referencia a una columna de una función.
Esto también se aplica a columnas de vistas que son expresiones sencillas que hacen referencia a una columna calculada de una tabla base. La expresión que define una columna calculada puede tener la misma forma que una expresión más compleja de la lista de selección de la vista.
La lista de selección de las vistas puede contener expresiones que no se asignan a ninguna columna de tabla base, por ejemplo:
CREATE VIEW dbo.ExpressionView
AS
SELECT BusinessEntityID, JobTitle, GETDATE() AS TodaysDate
FROM AdventureWorks2008R2.HumanResources.Employee;
Aunque la columna TodaysDate no se asigna a ninguna columna de tabla, SQL Server debe generar una columna TodaysDate en la tabla insertada que pasa a un desencadenador INSTEAD OF definido en ExpressionView. Sin embargo, la columna inserted.TodaysDate acepta valores NULL; por lo tanto, no es necesario que una instrucción INSERT que hace referencia a ExpressionView suministre un valor para esta columna. Dado que la expresión no se asigna a una columna de una tabla, el desencadenador puede pasar por alto los valores que INSERT suministra en esta columna.
Debe aplicarse el mismo método a expresiones sencillas de vistas que hacen referencia a columnas calculadas de tablas base, que a su vez generan un resultado no dependiente de otras columnas, por ejemplo:
CREATE TABLE dbo.ComputedExample
(
PrimaryKey int PRIMARY KEY,
ComputedCol AS SUSER_NAME()
);
Algunas expresiones complejas se asignan a varias columnas, por ejemplo:
CREATE TABLE dbo.SampleTable
(
PriKey int,
FirstName nvarchar(20),
LastName nvarchar(30)
);
GO
CREATE VIEW dbo.ConcatView
AS
SELECT PriKey, FirstName + ' ' + LastName AS CombinedName
FROM SampleTable;
La expresión CombinedName en ConcatView tiene los valores concatenados de los valores FirstName y LastName. Si hay un desencadenador INSTEAD OF INSERT definido en ConcatView, debe establecer una convención para definir la manera en que las instrucciones INSERT suministrarán un valor para la columna CombinedName. Es decir, un valor que permita al desencadenador determinar la parte de la cadena que se debe insertar en la columna FirstName y la que se debe insertar en la columna LastName. Si elige que sean las instrucciones INSERT las que especifiquen el valor de CombinedName mediante la convención 'first_name;last_name', este desencadenador puede procesar una instrucción INSERT correctamente:
CREATE TRIGGER InsteadSample on dbo.ConcatView
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO dbo.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;
Se requiere una lógica similar para procesar columnas de vistas, que son expresiones sencillas con referencias a columnas calculadas que tienen expresiones complejas.
Algunas expresiones de vistas pueden transformar el valor de una columna de una tabla base, por ejemplo, o bien realizando una operación matemática o bien utilizando la columna como parámetro de una función. En este caso, la lógica del desencadenador INSTEAD OF INSERT puede utilizar dos métodos:
Puede utilizar una convención para que todas las instrucciones INSERT suministren el valor sin formato que se debe colocar en la tabla base; la lógica del desencadenador mueve el valor de la tabla inserted a la tabla base.
Puede utilizar una convención para que todas las instrucciones INSERT suministren el valor que debía devolver una instrucción SELECT de la vista, en cuyo caso, la lógica del desencadenador debe invertir la operación. Por ejemplo:
CREATE TABLE dbo.BaseTable ( PrimaryKey int PRIMARY KEY, ColumnB int, ColumnC decimal(19,3) ); CREATE VIEW dbo.SquareView AS SELECT PrimaryKey, ColumnB, -- Square the value of ColumnC SQUARE(ColumnC) AS SquareC FROM BaseTable; CREATE TRIGGER SquareTrigger ON dbo.SquareView INSTEAD OF INSERT AS BEGIN INSERT INTO dbo.BaseTable SELECT PrimaryKey, ColumnB, -- Perform logical inverse of function in view. SQRT(SquareC) FROM inserted END;
Ocurre que, en algunas expresiones, como las expresiones complejas que utilizan operaciones matemáticas de suma y resta, los usuarios no pueden suministrar un valor que el desencadenador pueda utilizar con el fin de generar valores claros para las columnas de la tabla base de destino. Por ejemplo, si la lista de selección de una vista contiene la expresión IntColA + IntColB AS AddedColumns, ¿qué significa un valor de 10 en inserted.AddedColumns? ¿Es 10 el resultado de 3 + 7, 2 + 8 ó 5 + 5? El valor de inserted.AddedColumns no permite determinar por sí mismo qué valores deben colocarse en IntColA y en IntColB.
En estos casos, se puede codificar el desencadenador para utilizar orígenes alternativos de información con el fin de determinar los valores que se deben definir en las columnas de la tabla base. Para las vistas que tienen desencadenadores INSTEAD OF, la lista de selección de la vista debe contener información suficiente que permita generar valores para todas las columnas NOT NULL en las tablas base modificadas por el desencadenador. No es necesario que todos los datos procedan directamente de la tabla inserted. En algunos casos, los valores de la tabla inserted pueden ser valores clave que el desencadenador utiliza para recuperar los datos importantes de otras tablas base.