在INSTEAD OF INSERT触发器中使用默认值 [英] Using default values in an INSTEAD OF INSERT trigger

问题描述

我们正在执行向SQL Server的数据库迁移，并且为了支持旧版应用程序，我们在SQL Server表上定义了视图，这些视图按照旧版应用程序的期望显示数据。

We are performing a database migration to SQL Server, and to support a legacy app we have defined views on the SQL Server table which present data as the legacy app expects.

但是，当字段可能具有默认值时，我们现在在定义这些视图上的INSTEAD OF INSERT触发器时遇到了麻烦。

However, we're now having trouble with INSTEAD OF INSERT triggers defined on those views, when the fields may have default values.

我将尝试给出一个

数据库中的表具有3个字段，a，b和c。 c是全新的，旧版应用程序对此一无所知，因此我们也有一个包含两个字段a和b的视图。

A table in the database has 3 fields, a, b, and c. c is brand new, the legacy app doesn't know about it, so we also have a view with 2 fields, a and b.

当旧版应用程序尝试在视图中插入一个值，我们使用INSTEAD OF INSERT触发器来查找应在字段c中输入的值，如下所示：

When the legacy app tries to insert a value into its view, we use an INSTEAD OF INSERT trigger to lookup the value that should go in field c, something like this:

INSERT INTO realTable(a, b, c) SELECT Inserted.a, Inserted.b, Calculated.C FROM...

（查找的详细信息无关。）

(The details of the lookup aren't relevant.)

此触发器可以很好地起作用，除非字段b具有默认值。这是因为如果查询

This trigger works well, unless field b has a default value. This is because if the query

INSERT INTO legacyView(a) VALUES (123)

被执行，然后在触发器中，Inserted.b为NULL，而不是b的默认值。现在我遇到了一个问题，因为我无法区分上述查询，该查询会将默认值放入b，并且这样：

is executed, then in the trigger, Inserted.b is NULL, not b's default value. Now I have a problem, because I can't tell the difference the above query, which would put the default value into b, and this:

INSERT INTO legacyView(a,b) VALUES (123, NULL)

即使b是非NULLABLE的，我不知道如何在触发器中编写INSERT查询，以便如果为b提供了一个值，则在触发器中使用它，但如果没有使用默认值。

Even if b was non-NULLABLE, I don't know how to write the INSERT query in the trigger such that if a value was provided for b, it's used in the trigger, but if not the default is used instead.

编辑：添加了，我宁愿不重复触发器中的默认值。默认值已经在数据库模式中，我希望可以直接使用它们。

added that I'd rather not duplicate the default values in the trigger. The default values are already in the database schema, I would hope that I could just use them directly.

推荐答案

Paul：我ve解决了这个问题；最终。有点肮脏的解决方案，可能并不符合每个人的口味，但是我对SQL Server还是很陌生，例如：

Paul: I've solved this one; eventually. Bit of a dirty solution and might not be to everyone's taste but I'm quite new to SQL Server and such like:

在相反的INSERT_of_INSERT触发器中：

In the Instead_of_INSERT trigger:

1. 将插入的虚拟表的数据结构复制到临时表中：

1. Copy the Inserted virtual table's data structure to a temporary table:

SELECT * INTO aTempInserted FROM Inserted WHERE 1=2

创建一个视图以确定该视图的基础表（来自系统表）的默认约束，并使用它们来构建将复制临时表中约束的语句：

Create a view to determine the default constraints for the view's underlying table (from system tables) and use them to build statements which will duplicate the constraints in the temporary table:

SELECT  'ALTER TABLE dbo.aTempInserted
               ADD CONSTRAINT ' + dc.name + 'Temp' +
               ' DEFAULT(' + dc.definition + ') 
               FOR ' + c.name AS Cmd, OBJECT_NAME(c.object_id) AS Name
  FROM  sys.default_constraints AS dc
 INNER  JOIN sys.columns AS c
          ON dc.parent_object_id = c.object_id 
         AND dc.parent_column_id = c.column_id

使用游标遍历检索到的集合并执行每个语句。这将使您拥有一个临时表，其默认值与要插入的表相同。

Use a cursor to iterate through the set retrieved and execute each statement. This leaves you with a temporary table with the same defaults as the table to be inserted into.

将默认记录插入到临时表中（所有字段都可以为null

Insert default record into the temporary table (all fields are nullable as created from Inserted virtual table):

INSERT INTO aTempInserted DEFAULT VALUES

将记录从 Inserted虚拟表复制到视图的基础表中（如果没有触发，它们原本会插入到该表中），然后加入提供默认值的临时表。这需要使用COALESCE函数，以便仅默认不提供的值：

Copy the records from the Inserted virtual table into the view's underlying table (where they would have been inserted originally, had the trigger not prevented this), joining the temporary table to supply default values. This requires use of the COALESCE function so that only unsupplied values are defaulted:

INSERT INTO realTable([a], [b], 
            SELECT COALESCE(I.[a], T.[a]),
                   COALESCE(I.[a], T.[b])
            FROM   Inserted      AS I,
                   aTempInserted AS T

删除临时表

Drop the temporary table

这篇关于在INSTEAD OF INSERT触发器中使用默认值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！