使用 ADO.NET 自动生成的更新存储过程进行级联更新

Question

我正在运行带有 .NET 2.0 的 MS SQL Server 2005。我当前的应用程序是用 C# 编写的。

在 MSSQL 中，我创建了 2 个测试表来说明我的问题：

表 1 设置为：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[T1](
 [n] [bigint] NOT NULL,
 [t] [varchar](10) NOT NULL,
 CONSTRAINT [PK_T1] PRIMARY KEY CLUSTERED 
(
 [n] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

表 2 设置为：

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[T2](
 [n] [bigint] NULL,
 [Test] [varchar](4) NOT NULL,
 [Num] [bigint] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[T2]  WITH CHECK ADD  CONSTRAINT [FK_T2_T1] FOREIGN KEY([n])
REFERENCES [dbo].[T1] ([n])
ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[T2] CHECK CONSTRAINT [FK_T2_T1]

我在每个表中填充了多行。

简而言之，表 1 (T1) 有一个 bigint 主键字段“n”，映射到子表 T2 的 n 字段。设置级联更新，因此当修改 T1.n 时，T2.n 也会更新。如果我执行一个简单的查询并在其中一行中设置 T1.n 的值，我可以看到执行计划中发生级联。如果我设置 T1.t 的值，则执行计划中不会按预期发生级联。

在 ADO.NET 中，我在表单中添加了一个 DataAdapter，并让它自动生成 4 个存储过程，T1 的更新显示如下：（我添加了一些空格以帮助提高可读性）

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[T1_Update]
(
 @n bigint,
 @t varchar(10),
 @Original_n bigint,
 @Original_t varchar(10)
)
AS
 SET NOCOUNT OFF;
UPDATE [T1] 
SET 
[n] = @n, 
[t] = @t 
WHERE (
([n] = @Original_n) AND 
([t] = @Original_t));

SELECT n, t FROM T1 WHERE (n = @n)

方式执行此存储过程：

DECLARE @RC int
DECLARE @n bigint
DECLARE @t varchar(10)
DECLARE @Original_n bigint
DECLARE @Original_t varchar(10)

EXECUTE @RC = [T1_Update] 
   5, '5', 5, '4' -- n, t, Original_n, Original_t

我可以使用以下 n 的值不变，t 的值发生变化。我希望看到与我刚刚执行“UPDATE T1 SET t='4' where n=5 and t='5'”相同的行为/执行计划。运行此查询的执行计划非常简单，其中包括 CI 查找、计算标量、CI 更新、更新。

但是，运行上面的执行时，执行计划显示它正在扫描表 T2 并在该表上执行更新，即使该值没有更改。

我认为这是由于以下几行造成的：

UPDATE [T1] 
SET 
[n] = @n, 

注释掉 T1_Update 存储过程中的 [n] = @n，即使该值没有更改，也会阻止级联更新触发。

因此，假设我想保留级联更新，是否有办法重写/修改自动生成的 T1_Update 存储过程，以便级联更新仅在“n”的值更改时激活，而在值不变时不激活？

编辑：我删除了 T1 上引用自身的外键关系，该关系是在我设置此测试时意外添加的。

Answer 1

我谨表示，如果您要更新主键字段，那么它不是主键。对于要成为主键的列，以下三个语句必须为真：

1. 它绝不能为 NULL。
2. 它必须是独一无二的。
3. 它永远不能改变。

前两个通常由数据库服务器强制执行，但大多数不强制执行第三个，而您的设计中似乎违反了第三个。根据我的经验，这是你真的不想做的事情。仅仅因为数据库允许您这样做并不意味着这是一个好主意。这里有龙。

好吧，你已经被警告过。至于如何做你想做的事情 - 我建议你废弃外键约束上的 ON UPDATE CASCADE 选项，而是在父表上创建一个 ON UPDATE 触发器。如果您将其设置为在更新后触发，则旧值和新值都将可用，并且您的代码可以测试以准确查看哪些字段发生了更改并采取适当的操作。您可能还需要将外键设置为在 COMMIT 上触发，而不是在动词时间上触发 - 否则违反约束的短时间内将导致更新失败，即使它会在 COMMIT 时得到纠正被发出。 （我对 SQL Server 不像对其他数据库（例如 Oracle）那样熟悉，并且我不是 100% 肯定您可以将约束执行推迟到 SQL Server 下的 COMMIT 时间。您可能需要做一些挖掘）。

分享并享受。