TSQL期望使用代替插入触发器时插入标识列 [英] TSQL is expecting the identity column to be inserted when using an instead of insert trigger

问题描述

我要设置一个视图（foo），以代替具有相同名称的表格。新视图将使用3个子表（A，B，C）代替原始表。我正在使用代替触发器来完成此操作。

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列：oldkey（身份）|船|州|名称

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

我的问题是我写了一个较大的代码库，如下所示插入foo：

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')

但是该代码当前无法正常工作，因为插入/视图的替代也希望提供键值。关键值是表B上的标识，因此不需要插入它。

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?

推荐答案

由于 INSERT 不会不指定预期的列，而是使用隐式标识-跳过列的技巧，我想您简直不走运。

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).

在视图的 INSERT 中指定了列 时，可以跳过 identity 列，方法是修改视图，以便该列不再在视图的元数据中保留 identity 属性或PK状态。您可以这样简单地做到这一点：

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期望使用代替插入触发器时插入标识列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！