TSQL is expecting the identity column to be inserted when using an instead of insert trigger

Question

I'm setting up a view (foo), in place of a table with the same name. The new view will use 3 sub-tables (A, B, C) instead of the original table. I'm using instead-of triggers to accomplish this.

CREATE VIEW [dbo].[foo]
AS
select
    b2 as oldkey,
    b3 as boat,
    c2 as state,
    a2 as name
from
    A
    join B on a1 = b1
    join C on a1 = c1

foo columns: oldkey (identity) | boat | state | name

My issue is that I have a large code base that is written to insert into foo as follows:

insert into foo
values
('fishing boat', 'California', 'Skipper')

But this code is not currently working because the instead-of-insert/view expect the key value to be provided as well. The key value is the identity on table B, so I don't need it to be inserted.

Error: "Column name or number of supplied values does not match table definition."

What changes do I need to make to allow the code base of inserts that already exist to continue functioning?

Inside the INSTEAD OF INSERT trigger, you need to insert into the first table, get the newly defined identity column value, and then fill the other tables including that foreign key. You should show us your trigger code! — marc_s
@marc_s I actually do that. All of my code for inserting/updating/deleting works. I believe the issue is in how I declare the view. — Ryan
@AaronBertrand I've added the generalized code for the view. — Ryan

ErikE ErikE · Accepted Answer · 2013-07-08T17:14:49

Since the INSERT does not specify the intended columns, but uses the implicit identity-column-skipping trick, I think you are simply out of luck.

This is a good example of why specifying column names is best practice. If the original code had specified column names like so:

INSERT INTO dbo.Foo (ItemType, Location, Name)
VALUES ('fishing boat', 'California', 'Skipper');

then the insert would work (with the below modification).

When the columns are specified in an INSERT to a view, you can skip an identity column by modifying the view so the column no longer retains the identity property or PK status in the view's metadata. You can do that as simply as this:

SELECT
   IdentityColumn = IdentityColumn * 1, -- a math expression removes `identity`
   OtherColumns
FROM
   TablesHere
;

TSQL is expecting the identity column to be inserted when using an instead of insert trigger

1 Answers