sql - 在 INSTEAD OF INSERT 触发器中使用默认值

Question

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

但是，当字段可能具有默认值时，我们现在在使用这些 View 上定义的 INSTEAD OF INSERT 触发器时遇到了问题。

我会尝试举一个例子。

数据库中的表有 3 个字段，a、b、c。 c 是全新的，旧版应用程序不知道它，因此我们还有一个包含 2 个字段 a 和 b 的 View 。

当旧应用程序尝试将值插入其 View 时，我们使用 INSTEAD OF INSERT 触发器来查找应放入字段 c 的值，如下所示:

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

(查找的详细信息不相关。)

除非字段 b 有默认值，否则此触发器工作正常。这是因为如果查询

INSERT INTO legacyView(a) VALUES (123)

被执行，那么在触发器中，Inserted.b为NULL，而不是b的默认值。现在我有一个问题，因为我无法区分上面的查询，这会将默认值放入 b 中，这是:

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

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

编辑:补充说我不想重复触发器中的默认值。默认值已经在数据库模式中，我希望我可以直接使用它们。

Answer 1

Paul:我已经解决了这个问题；最终。这是一个有点肮脏的解决方案，可能不适合每个人的口味，但我对 SQL Server 等还很陌生:

在 Replace_of_INSERT 触发器中:

1. 将 Inserted 虚拟表的数据结构复制到临时表:

   SELECT * INTO aTempInserted FROM Inserted WHERE 1=2
   

2. 创建一个 View 来确定 View 基础表(来自系统表)的默认约束，并使用它们构建将在临时表中复制约束的语句:

   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
   

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

4. 将默认记录插入临时表(从插入的虚拟表创建的所有字段都可以为空):

   INSERT INTO aTempInserted DEFAULT VALUES
   

5. 将记录从 Inserted 虚拟表复制到 View 的基础表中(如果触发器没有阻止的话，它们最初会插入到该表中)，并连接临时表以提供默认值。这需要使用 COALESCE 函数，以便仅默认未提供的值:

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

6. 删除临时表