Триггеры INSTEAD OF INSERT
Триггеры INSTEAD OF INSERT могут быть заданы в представлении или таблице с целью замены стандартного действия инструкции INSERT. Обычно триггер INSTEAD OF INSERT определяется в представлении для вставки данных в одну или несколько базовых таблиц.
Столбцы в списке выборки представления могут быть либо пустыми, либо нет. Если столбцу представления не разрешено иметь значения NULL, то инструкция INSERT должна определить значения для этого столбца. Столбцы представления могут иметь значение NULL, если в определяющем этот столбец выражении содержатся следующие элементы:
- ссылки на любой столбец базовой таблицы, допускающий значения NULL;
- арифметические операторы;
- ссылки на функции;
- CASE или COALESCE с подвыражением, которое может иметь значение NULL;
- NULLIF
Свойство AllowsNull, предоставляемое функцией COLUMNPROPERTY, можно использовать для определения того, допускает ли столбец представления значения NULL. Хранимая процедура sp_help также сообщает о том, может ли принимать столбец представления значения NULL.
Ссылающаяся на представление с триггером INSTEAD OF INSERT инструкция INSERT должна определять значения для каждого столбца представления, не допускающего значения NULL. Сюда входят столбцы представления, которые содержат ссылки на столбцы базовой таблицы, для которых могут быть определены вводимые значения, например:
- вычисляемые столбцы базовой таблицы;
- столбцы идентификаторов в базовой таблице, для которых значение IDENTITY INSERT равно OFF;
- столбцы базовой таблицы с типом данных timestamp.
Если триггер представления INSTEAD OF INSERT создает инструкцию INSERT относительно базовой таблицы, используя данные вставленной таблицы, то он должен пропускать значения для этих типов столбцов, не включая их в список выборки инструкции INSERT. Инструкция INSERT может создать пустые значения для этих типов столбцов.
Например, при определении значения столбца представления, соответствующего столбцу идентификации или вычисляемому столбцу базовой таблицы, инструкция INSERT может задать значение-заполнитель. Триггер INSTEAD OF может пропустить предоставленное значение при формировании инструкции INSERT, которая производит вставку значений в базовую таблицу.
Следующие инструкции создают таблицу, представление и триггер, которые иллюстрируют процесс:
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
Инструкция INSERT, ссылающаяся напрямую на BaseTable, не может определить значения для столбцов PrimaryKey и ComputedCol. Например:
--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')
Однако инструкция INSERT, ссылающаяся на InsteadView, должна определять значение для PrimaryKey и 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
Добавленная таблица, передающаяся триггеру InsteadTrigger, создается со столбцами PrimaryKey и ComputedCol, которые не могут иметь значение NULL; таким образом, инструкция INSERT, ссылающаяся на представление, должна определить значения для этих столбцов. Значения 999 и N'XXXXXX' передаются триггеру InsteadTrigger, но инструкция INSERT триггера не выбирает ни inserted.PrimaryKey, ни inserted.ComputedCol, поэтому эти значения игнорируются. Вставленная в таблицу BaseTable строка имеет значение 2 в столбце PrimaryKey и значение N'BluePlastic' в столбце ComputedCol.
Содержащиеся в вставленной таблице значения для вычисляемого столбца, столбца идентификаторов и столбца timestamp различны для триггеров INSTEAD OF INSERT, определенных в таблицах, и для триггеров INSTEAD OF INSERT, определенных для представлений.
| Столбец базовой таблицы | Значение во вставленной таблице в любом триггере INSERT в таблице. | Значение во вставленной таблице в триггере INSTEAD OF INSERT в представлении. |
|---|---|---|
Вычисляемый столбец. |
Вычисляемое выражение |
Заданное пользователем значение или NULL. |
Имеет свойство IDENTITY. |
0, если IDENTITY_INSERT имеет значение OFF; указанное значение, если значение IDENTITY_INSERT равно ON |
Заданное пользователем значение или NULL. |
Имеет тип данных timestamp. |
Двоичные нули, если в столбце не допускаются нулевые значения; значение NULL, если в столбце допускаются нулевые значения |
Заданное пользователем значение или NULL. |
Инструкция INSERT, ссылающаяся напрямую на базовую таблицу, не должна определять значения для столбца NULL, который уже имеет определенные значения DEFAULT. Если инструкция INSERT не определяет значение, то используется заданное по умолчанию значение. Если на столбец NOT NULL с заданными значениями DEFAULT ссылается простое выражение представления, содержащего триггер INSTEAD OF INSERT, то любая инструкция INSERT, ссылающаяся на представление, должна определить значения этого столбца. Это значение необходимо для создания вставленной таблицы, передающейся триггеру. Для значения требуется соглашение, указывающее триггеру на то, что должно использоваться определенное по умолчанию значение. Наилучшим соглашением является определение инструкцией INSERT заданного по умолчанию значения.
Удаленная таблица в триггере INSTEAD OF INSERT всегда пуста.
См. также
Другие ресурсы
COLUMNPROPERTY (Transact-SQL)
sp_help (Transact-SQL)