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, Jul 08 '13 at 16:08
@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, Jul 08 '13 at 16:12
@AaronBertrand I've added the generalized code for the view. — Ryan, Jul 08 '13 at 16:30

ErikE · Accepted Answer · 2013-07-08T20:39:54.100

2

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
;

edited Jul 08 '13 at 20:39

answered Jul 08 '13 at 17:14

ErikE

48,881
23
151
196

If you are still having trouble, instead of only describing the problem as in "expect the key value to be provided as well", please show example code and exact error messages provided by the engine. This will better guide those who wish to help you. – ErikE Jul 08 '13 at 17:14
That fix did not seem to work. I hadn't included the specific error because it was just the general: "Column name or number of supplied values does not match table definition." I have edited the post to show the error since people may have thought it was an error about the identity from my explanation. – Ryan Jul 08 '13 at 17:25
Unfortunately that was the same conclusion I had come to as well. I couldn't find another way, but I figured it wouldn't hurt to ask. Thank you! – Ryan Jul 08 '13 at 20:43
1

Fortunately, @Ryan, fortunately. :) It might have been unfortunate if you had come to the conclusion *without* anyone suggesting you the same. That way you'd probably have had to do the changes with a heavy heart, having doubts as to whether the decision was indeed right. As it is, you know for sure there are people that are with you on this. :) – Andriy M Jul 09 '13 at 07:05
@AndriyM Very true. Thank you for the positive spin! :) – Ryan Jul 09 '13 at 16:03

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

1 Answers1

Linked