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

韦修文

2023-03-14

问题内容：

我们正在执行向SQL Server的数据库迁移，并且为了支持旧版应用程序，我们在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具有默认值，否则此触发器效果很好。这是因为如果查询

INSERT INTO legacyView(a) VALUES (123)

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

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

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

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

问题答案：

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

在Replace_of_INSERT触发器中：

将插入的虚拟表的数据结构复制到临时表中：
```
SELECT * INTO aTempInserted FROM Inserted WHERE 1=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
使用游标遍历检索到的集合并执行每个语句。这将为您提供一个临时表，该表的默认值与要插入的表的默认值相同。
将默认记录插入临时表（从“插入的虚拟表”创建的所有字段都可以为空）：
```
INSERT INTO aTempInserted DEFAULT VALUES
```
将记录从“插入的”虚拟表复制到视图的基础表（如果没有触发，则原本应在其中插入记录），并加入临时表以提供默认值。这需要使用COALESCE函数，以便仅默认未提供的值：
```
INSERT INTO realTable([a], [b], 
        SELECT COALESCE(I.[a], T.[a]),
               COALESCE(I.[a], T.[b])
        FROM   Inserted      AS I,
               aTempInserted AS T
```
删除临时表