创建 SQL Server 触发器以从自然键转换为代理键

Question

背景故事

在工作中，我们计划弃用主表之一中的自然键列。该项目由 100 多个链接到此表/列的应用程序组成； 400 多个直接引用此列的存储过程；这些应用程序之间的大量公用表也引用了此列。

大爆炸和从头开始的方法都不适用。我们将一次弃用本专栏的一个应用程序，验证更改，然后继续下一个……并且我们有一个漫长的目标来使这项工作切实可行。

我遇到的问题是，许多应用程序都共享存储过程和表。如果我完全转换应用程序 A 的所有表/存储过程，应用程序 B 和 C 将在转换之前被破坏。这些反过来可能会破坏应用程序 D、E、F...等。我已经为代码类和存储过程实施了策略，我所坚持的部分是数据库的转换状态。

这是我们所拥有的一个基本示例：

Users
---------------------------
Code          varchar(32) natural key

Access
---------------------------
UserCode      varchar(32) foreign key
AccessLevel   int

我们现在的目标只是像这样的过渡状态：

Users
---------------------------
Code          varchar(32) 
Id            int         surrogate key

Access
---------------------------
UserCode      varchar(32)   
UserID        int         foreign key      
AccessLevel   int

这个想法是在过渡阶段，未迁移的应用程序和存储过程仍然能够访问所有适当的数据和新数据可以开始推送到正确的列——一旦所有存储过程和应用程序的迁移完成，我们最终可以删除额外的列。

我想使用 SQL Server 的触发器自动拦截任何新的插入/更新，并对每个受影响的表执行如下操作：

CREATE TRIGGER tr_Access_Sync
ON Access
INSTEAD OF INSERT(, UPDATE)
AS
BEGIN
  DIM @code as Varchar(32)
  DIM @id as int

  SET @code = (SELECT inserted.code FROM inserted)
  SET @id = (SELECT inserted.code FROM inserted)

  -- This is a migrated application; find the appropriate legacy key
  IF @code IS NULL AND @id IS NOT NULL
     SELECT Code FROM Users WHERE Users.id = @id

  -- This is a legacy application; find the appropriate surrogate key
  IF @id IS NULL AND @code IS NOT NULL
     SELECT Code FROM Users WHERE Users.id = @id

  -- Impossible code:
  UPDATE inserted SET inserted.code=@code, inserted.id=@id
END

问题

到目前为止我遇到的 2 个巨大问题是：

1. 我可以不要执行“AFTER INSERT”，因为 NULL 约束会使插入失败。
2. 我提到的“不可能的代码”是我希望如何干净地代理原始查询；如果原始查询中有 x、y、z 列或只有 x，我理想情况下希望使用相同的触发器来执行这些操作。如果我添加/删除另一列，我希望触发器保持功能。

任何人都有一个可以实现这一点的代码示例，或者甚至有一个替代解决方案，即使只有一个值传递给 SQL，也能正确填充这些列？

Answer 1

是否有可能使架构更改“大爆炸”，但在那些“隐藏”更改的表顶部创建视图？

我认为您可能会发现您只是将损坏推迟到稍后的时间点：“我们将一次弃用本专栏的一个应用程序” - 这可能是我的天真，但我不知道这是如何进行的去工作。

当然，当不同的应用程序以不同的方式做事时，可能会出现更糟糕的情况？

Answer 2

在解决了这个问题之后，这似乎是我在 SQL 语法中能想到的最通用/可重用的解决方案。即使两列都有 NOT NULL 限制，即使您在插入中根本不引用“其他”列，它也能正常工作。

CREATE TRIGGER tr_Access_Sync
ON Access
INSTEAD OF INSERT
AS 
BEGIN

    /*-- Create a temporary table to modify because "inserted" is read-only */
    /*-- "temp" is actually "#temp" but it throws off stackoverflow's syntax highlighting */
    SELECT * INTO temp FROM inserted

    /*-- If for whatever reason the secondary table has it's own identity column */
    /*-- we need to get rid of it from our #temp table to do an Insert later with identities on */
    ALTER TABLE temp DROP COLUMN oneToManyIdentity

    UPDATE temp 
    SET 
        UserCode = ISNULL(UserCode, (SELECT UserCode FROM Users U WHERE U.UserID = temp.UserID)),
        UserID = ISNULL(UserID, (SELECT UserID FROM Users U WHERE U.UserCode = temp.UserCode))

    INSERT INTO Access SELECT * FROM temp

END

Answer 3

棘手的业务...

好吧，首先：此触发器在许多情况下不工作：

SET @code = (SELECT inserted.code FROM inserted)
SET @id = (SELECT inserted.code FROM inserted)

可以使用Inserted伪表中的一组行来调用触发器- 你要在这里选哪一个？您需要以这样的方式编写触发器，即使 Inserted 表中包含 10 行，它也能正常工作。如果 SQL 语句插入 10 行，您的触发器将不会被触发十次 - 每行一次 - 但整个批次只会一次 - 您需要将其考虑在内帐户！

第二点：我会尝试创建 ID 的 IDENTITY 字段 - 然后它们总是会获得一个值 - 即使对于“旧版”应用程序也是如此。那些“旧”应用程序应该提供一个旧密钥 - 所以你应该没问题。我看到的唯一问题并且不知道您如何处理这些是来自已转换的应用程序的插入 - 它们是否也提供“旧式”遗留密钥？如果没有 - 您需要多快获得这样一把钥匙？

我正在考虑的是一个“清理作业”，它将在表上运行并获取具有 NULL 旧键的所有行，然后为其提供一些有意义的值。将此设置为常规存储过程，并每隔一天、四小时、30 分钟执行一次 - 只要适合您的需要即可。那么您就不必处理触发器及其所具有的所有限制。