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

Question

我们正在进行数据库迁移到SQL Server，并为支持遗留应用程序定义了视图，以按照该应用程序的期望呈现数据。

然而，当字段可能具有默认值时，在那些视图上定义的INSTEAD OF INSERT触发器出现问题。

我将尝试举一个例子。

数据库中的一张表有3个字段a、b和c。c是全新的，遗留应用程序不知道它，所以我们还有一个只有2个字段a和b的视图。

当遗留应用程序尝试向其视图插入值时，我们使用INSTEAD OF INSERT触发器来查找应该放入字段c的值，类似于以下内容：

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

（查找的详细信息并不相关。）

无论 b 字段是否有默认值，此触发器都能正常工作。问题在于如果查询语句中没有包含 b 字段，那么当 b 字段有默认值时，它将无法触发对应操作。

INSERT INTO legacyView(a) VALUES (123)

如果执行了以下语句，那么在触发器中，Inserted.b将为NULL，而不是b的默认值。现在我有一个问题，因为我无法区分上述查询会将默认值放入b和这个查询：

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

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

编辑：我不想在触发器中复制默认值。 默认值已经在数据库模式中，我希望能直接使用它们。

- Paul Smith

---

知道所有带有默认值的字段都不可为空是非常重要的！如果我知道这个信息，我本可以为您提供一个解决方案。 - ErikE

抱歉Emtucifor，我在发布问题时不知道我们的可空字段没有任何默认值。你的解决方案是什么？如果好的话，我仍然可以点赞并可能更改接受的答案！ - Paul Smith

Answer 1

保罗：我最终解决了这个问题。解决方案有点欠妥，可能并不适合所有人，但我对SQL Server及类似技术还很陌生：

在Instead_of_INSERT触发器中：

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

   SELECT * INTO aTempInserted FROM Inserted WHERE 1=2
   

2. 创建一个视图来确定视图底层表的默认约束（从系统表中），并使用它们来构建语句，以在临时表中复制约束：

   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. 将插入的虚拟表记录复制到视图底层表中（如果触发器没有阻止此操作，则会将其插入到原始位置），连接临时表以提供默认值。这需要使用 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. 删除临时表

- user268559

---

这已经接近完成了 - 在第5步中，你仍然无法区分一个NULL是因为值未指定（因此使用默认值），还是因为指定了一个NULL值（因此不要使用默认值）。但是，只有在具有默认值的可空字段存在时才会出现此问题，我认为我们没有这样的字段，因此这个答案足以被接受。 - Paul Smith

没错。这是一种相当复杂的方法，但可以在插入语句中需要进一步连接/处理的情况下实现。 - user268559

Answer 2

一些想法：

• 如果传统应用程序为INSERT指定列列表，并命名列而不是使用SELECT *，那么您是否可以将默认值绑定到列c并让应用程序使用您的原始（修改后）表？

• 如果有任何方法可以使传统应用程序在其INSERTs中使用不同的视图或表而不是SELECT或DELETE，则可以将所需的默认值放在该表上，并使用常规的after-trigger将新列移动到真实表中。

• 如何保留原始表并在单独的表中添加其他列，该表与原始表具有1-1关系？然后创建一个视图来组合这两个表，并在这个新视图上放置适当的instead-of触发器来处理跨两个表分割的所有数据操作。我意识到这会影响性能，但这可能是解决问题的唯一方法。这将是材料化视图的理想情况，它会减慢更新速度，但对于读取来说，结果的执行效果与表完全相同。（材料化视图最适合内部连接，不需要聚合。它们还会在源表上放置模式锁定。）

• 我遇到了类似的问题，无法区分视图上instead-of UPDATE触发器中的有意NULL值和跳过的列。最终，我在视图上创建了一个instead-of INSERT触发器，将插入转换为更新（如果键已经存在，则为更新，否则为插入）。虽然这对您没有直接帮助，但它可能会激发您或其他人的一些想法。

- ErikE

---

谢谢您的评论，虽然我无法指出具体帮助了什么，但它让我们感觉不再像撞到了一堵墙！ - Paul Smith

Answer 3

使用类似这样的东西怎么样？

insert into realtable
values inserted.a, isnull(inserted.b, DEFAULT), computedC
from inserted

- estebanuri

---

这将插入DEFAULT值而不是真正的NULL。 - Eugen Konkov

Incorrect syntax near the keyword 'DEFAULT'. - Danny Beckett