An insert statement must specify a value for a view column that maps to an identity or computed column in a base table. However, it can supply a placeholder value. The insert statement in the instead of trigger that inserts the values into the base table is written to ignore the supplied value.
For example, these statements create a table, view, and trigger that illustrate the process:
CREATE TABLE BaseTable (PrimaryKey int IDENTITY Color varchar (10) NOT NULL, Material varchar (10) NOT NULL, TranTime timestamp ) --------------------- --Create a view that contains all columns from the base table. CREATE VIEW InsteadView AS SELECT PrimaryKey, Color, Material, TranTime FROM BaseTable --------------------- 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.TranTime. INSERT INTO BaseTable SELECT Color, Material FROM inserted END
An insert statement that refers directly to BaseTable cannot supply a value for the PrimaryKey and TranTime columns. For example:
--A correct INSERT statement that skips the PrimaryKey
--and TranTime columns.
INSERT INTO BaseTable (Color, Material)
VALUES ('Red', 'Cloth')
--View the results of the INSERT statement.
SELECT PrimaryKey, Color, Material, TranTime
FROM BaseTable
--An incorrect statement that tries to supply a value
--for the PrimaryKey and TranTime columns.
INSERT INTO BaseTable
VALUES (2, 'Green', 'Wood', 0x0102)
INSERT statements that refer to InsteadView, however,
must supply a value for PrimaryKey:
--A correct INSERT statement supplying a dummy value for
--the PrimaryKey column. A value for TranTime is not
--required because it is a nullable column.
INSERT INTO InsteadView (PrimaryKey, Color, Material)
VALUES (999, 'Blue', 'Plastic')
--View the results of the INSERT statement.
SELECT PrimaryKey, Color, Material, TranTime
FROM InsteadView
The inserted table passed to InsteadTrigger is built with a non-nullable PrimaryKey column; therefore, the insert statement referencing the view must supply a value for this column. The value 999 is passed in to InsteadTrigger, but the insert statement in InsteadTrigger does not select inserted.PrimaryKey, and therefore, the value is ignored. The row actually inserted into BaseTable has 2 in PrimaryKey and an Adaptive Server-generated timestamp value in TranTime.
If a not null column with a default definition is referenced in a view with an instead of insert trigger, any insert statement that references the view must supply a value for the column. This value is required to build the inserted table passed to the trigger. A convention is required for a value that signals the trigger that the default value should be used. A possible convention is to supply an explicit null value for the not null column in the insert statement. The instead of insert trigger can ignore the explicit null when inserting into the table upon which the view is defined, causing the default value to be inserted into the table. For example:
--Create a base table with a not null column that has --a default CREATE TABLE td1 (coll int DEFAULT 9 NOT NULL, col2 int) --Create a view that contains all of the columns of --the base table. CREATE VIEW vtd1 as select * from td1 --create an instead of trigger on the view CREATE TRIGGER vtd1insert on vtd1 INSTEAD OF INSERT AS BEGIN --Build an INSERT statement that inserts all rows --from the inserted table that have a NOT NULL value --for col1. INSERT INTO td1 (col1,col2) SELECT * FROM inserted WHERE col != null --Build an INSERT statement that inserts just the --value of col2 from inserted for those rows that --have NULL as the value for col1 in inserted. In --this case, the default value of col1 will be --inserted. INSERT INTO td1 (col2) SELECT col2 FROM inserted WHERE col1 = null END
The deleted table in an instead of insert trigger is always empty.
