Триггеры 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
  (ID     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 ID, 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.ID and 
  --inserted.ComputedCol.
  INSERT INTO BaseTable
       SELECT Color, Material
       FROM inserted
END;
GO

Инструкция INSERT, ссылающаяся напрямую на BaseTable, не может определить значения для столбцов ID и ComputedCol. Например:

--A correct INSERT statement that skips the ID and ComputedCol columns.
INSERT INTO BaseTable (Color, Material)
       VALUES (N'Red', N'Cloth');

--View the results of the INSERT statement.
SELECT ID, Color, Material, ComputedCol
FROM BaseTable;

--An incorrect statement that tries to supply a value for the 
--ID and ComputedCol columns.
INSERT INTO BaseTable
       VALUES (2, N'Green', N'Wood', N'GreenWood');

Однако инструкция INSERT, ссылающаяся на InsteadView, должна определять значение для ID и ComputedCol:

--A correct INSERT statement supplying dummy values for the 
--PrimaryKey and ComputedCol columns.
INSERT INTO InsteadView (ID, Color, Material, ComputedCol)
       VALUES (999, N'Blue', N'Plastic', N'XXXXXX')
--View the results of the INSERT statement.
SELECT ID, Color, Material, ComputedCol
FROM InsteadView;

Таблица inserted, переданная триггеру InsteadTrigger, содержит столбцы ID и ComputedCol, которые не могут иметь значение NULL. Таким образом, инструкция INSERT, которая ссылается на представление, должна определить значения для этих столбцов. Значения 999 и N'XXXXXX' передаются триггеру InsteadTrigger, но инструкция INSERT триггера не выбирает ни inserted.ID, ни inserted.ComputedCol, поэтому эти значения игнорируются. Вставленная в таблицу BaseTable строка имеет значение 2 в столбце ID и значение N'BluePlastic' в столбце ComputedCol.

Содержащиеся во вставленной таблице значения для вычисляемого столбца, столбца идентификаторов и столбца timestamp различны для триггеров INSTEAD OF INSERT, определенных в таблицах, и для триггеров INSTEAD OF INSERT, определенных для представлений.

Инструкция INSERT, ссылающаяся напрямую на базовую таблицу, не должна определять значения для столбца NULL, который уже имеет определенные значения DEFAULT. Если инструкция INSERT не определяет значение, то используется заданное по умолчанию значение. Если на столбец NOT NULL с заданными значениями DEFAULT ссылается простое выражение представления, содержащего триггер INSTEAD OF INSERT, то любая инструкция INSERT, ссылающаяся на представление, должна определить значения этого столбца. Это значение необходимо для создания вставленной таблицы, передающейся триггеру. Для значения требуется соглашение, указывающее триггеру на то, что должно использоваться определенное по умолчанию значение. Наилучшим соглашением является определение инструкцией INSERT заданного по умолчанию значения.

Удаленная таблица в триггере INSTEAD OF INSERT всегда пуста.