Desencadenadores INSTEAD OF INSERT
Se pueden definir desencadenadores INSTEAD OF INSERT en una vista o tabla para reemplazar la acción estándar de la instrucción INSERT. Normalmente, el desencadenador INSTEAD OF INSERT se define en una vista para insertar datos en una o más tablas base.
Las columnas de la lista de selección de la vista pueden o no admitir valores NULL. Si una columna de la vista no admite valores NULL, una instrucción INSERT debe proporcionar los valores para la columna. Las columnas de la vista admiten valores NULL si la expresión que define la columna de la vista incluye elementos como:
- Referencias a cualquier columna de la tabla base que admita valores NULL
- Operadores aritméticos
- Referencias a funciones
- CASE o COALESCE con una subexpresión que admita valores NULL.
- NULLIF
Puede utilizar la propiedad AllowsNull mostrada por la función COLUMNPROPERTY para determinar si una columna de una vista admite valores NULL. El procedimiento almacenado sp_help también informa de las columnas que admiten valores NULL.
Una instrucción INSERT con referencias a una vista que contiene un desencadenador INSTEAD OF INSERT debe suministrar valores para todas las columnas de la vista que no admiten valores NULL. Esto incluye columnas de vistas que hacen referencia a columnas de la tabla base para las que no se pueden especificar valores de entrada, como:
- Columnas calculadas de la tabla base.
- Columnas de identidad de la tabla base en las que el valor de IDENTITY INSERT es OFF.
- Columnas de la tabla base con el tipo de datos timestamp.
Si el desencadenador INSTEAD OF INSERT de una vista genera una instrucción INSERT en la tabla base mediante los datos de la tabla inserted, el desencadenador debe omitir los valores de estos tipos de columna; para ello, no incluye las columnas en la lista de selección de la instrucción INSERT. La instrucción INSERT puede generar datos ficticios para estos tipos de columnas.
Por ejemplo, aunque una instrucción INSERT debe especificar un valor para una columna de vista que asigne a una identidad o una columna calculada de una tabla base, puede proporcionar un marcador. El desencadenador INSTEAD OF puede omitir el valor suministrado cuando forma la instrucción INSERT que inserta los valores en la tabla base.
Las siguientes instrucciones crean una tabla, una vista y un desencadenador para ilustrar el proceso:
CREATE TABLE BaseTable
(PrimaryKey int PRIMARY KEY IDENTITY(1,1),
Color nvarchar(10) NOT NULL,
Material nvarchar(10) NOT NULL,
ComputedCol AS (Color + Material)
)
GO
--Create a view that contains all columns from the base table.
CREATE VIEW InsteadView
AS SELECT PrimaryKey, Color, Material, ComputedCol
FROM BaseTable
GO
--Create an INSTEAD OF INSERT trigger on the view.
CREATE TRIGGER InsteadTrigger on InsteadView
INSTEAD OF INSERT
AS
BEGIN
--Build an INSERT statement ignoring inserted.PrimaryKey and
--inserted.ComputedCol.
INSERT INTO BaseTable
SELECT Color, Material
FROM inserted
END
GO
Una instrucción INSERT que hace referencia directamente a BaseTable no puede suministrar un valor para las columnas PrimaryKey y ComputedCol. Por ejemplo:
--A correct INSERT statement that skips the PrimaryKey and ComputedCol columns.
INSERT INTO BaseTable (Color, Material)
VALUES (N'Red', N'Cloth')
--View the results of the INSERT statement.
SELECT PrimaryKey, Color, Material, ComputedCol
FROM BaseTable
--An incorrect statement that tries to supply a value for the
--PrimaryKey and ComputedCol columns.
INSERT INTO BaseTable
VALUES (2, N'Green', N'Wood', N'GreenWood')
Sin embargo, una instrucción INSERT que hace referencia a InsteadView debe suministrar un valor para PrimaryKey y ComputedCol.
--A correct INSERT statement supplying dummy values for the
--PrimaryKey and ComputedCol columns.
INSERT INTO InsteadView (PrimaryKey, Color, Material, ComputedCol)
VALUES (999, N'Blue', N'Plastic', N'XXXXXX')
--View the results of the INSERT statement.
SELECT PrimaryKey, Color, Material, ComputedCol
FROM InsteadView
La tabla inserted pasada a InsteadTrigger se genera con unas columnas PrimaryKey y ComputedCol que no admiten valores NULL, de manera que la instrucción INSERT que hace referencia a la vista debe suministrar el valor para esas columnas. Los valores 999 y N'XXXXXX' se pasan a InsteadTrigger, pero la instrucción INSERT del desencadenador no selecciona ni inserted.PrimaryKey ni inserted.ComputedCol; por lo que los valores se omiten. La fila que se inserta realmente en BaseTable tiene 2 en PrimaryKey y N'BluePlastic' en ComputedCol.
Los valores de la tabla inserted para las columnas calculadas, de identidad y timestamp son diferentes para los desencadenadores INSTEAD OF INSERT especificados en las tablas y los desencadenadores INSTEAD OF especificados en las vistas.
| Columna de tabla base | Valor de la tabla insertada en cualquier desencadenador INSERT de una tabla | Valor de la tabla insertada en un desencadenador INSTEAD OF INSERT de una vista |
|---|---|---|
Es una columna calculada. |
Expresión calculada |
Valor especificado por el usuario o NULL |
Tiene una propiedad IDENTITY. |
0 si IDENTITY_INSERT es OFF, valor especificado si IDENTITY_INSERT es ON |
Valor especificado por el usuario o NULL |
Tiene un tipo de datos timestamp. |
Ceros binarios si la columna no admite valores NULL, y NULL si los admite |
Valor especificado por el usuario o NULL |
Una instrucción INSERT que hace referencia directa a una tabla base no tiene que suministrar valores para una columna NOT NULL que tiene también una definición DEFAULT. Si la instrucción INSERT no suministra ningún valor, se utiliza el valor predeterminado. No obstante, si una expresión simple de una vista que tiene un desencadenador INSTEAD OF INSERT hace referencia a una columna NOT NULL que tiene una definición DEFAULT, cualquier instrucción INSERT que haga referencia a una vista debe suministrar un valor para la columna. Este valor es necesario para generar la tabla inserted pasada al desencadenador. Se requiere una convención para un valor que indica al desencadenador que debería utilizarse el valor predeterminado. La mejor convención es que la instrucción INSERT suministre el valor predeterminado.
La tabla deleted de un desencadenador INSTEAD OF INSERT siempre está vacía.
Vea también
Otros recursos
COLUMNPROPERTY (Transact-SQL)
sp_help (Transact-SQL)