为什么不允许MERGE语句的目标表具有已启用的规则? [英] Why is the target table of a MERGE statement not allowed to have enabled rules?

问题描述

我们有一个使用以下SQL更新数据库的过程

We have a process updating the database which uses the following SQL

IF NOT EXISTS (SELECT * FROM Target_Table WHERE Target_Table_ID = ...)
BEGIN
    INSERT ...
END
ELSE
BEGIN
    UPDATE ...
END

我想将其更改为使用MERGE语句，以避免可能的竞态条件，在这种情况下，分别评估IF和INSERT或UPDATE语句.

I wanted to change this to use a MERGE statement in order to avoid a possible race condition where the IF and then INSERT or UPDATE statements are evaluated separately.

但是，一旦执行查询，我就会收到错误消息:

However, as soon as I executed the query I got the error:

MERGE语句的目标表"Target_Table"不能具有任何启用的规则.找到规则"TargetTable_RULE"

The target table 'Target_Table' of the MERGE statement cannot have any enabled rules. Found rule 'TargetTable_RULE'

我了解文档指出，该表无法启用规则，尽管我在初次阅读时就错过了它，除了在执行MERGE之前禁用规则(我认为这不是可行的解决方案)之外似乎我坚持使用IF NOT EXISTS解决方案.

I understand the documentation states that the table cannot have rules enabled, although I missed it when first reading it, and apart from disabling the rule before performing the MERGE (which I don't see as a workable solution) it seems I'm stuck with the IF NOT EXISTS solution.

文档未解释的是为什么不允许目标表具有规则.就我而言，规则是对值进行简单的> 0检查.

What the document does not explain is why the target table is not allowed to have rules. In my case the rule is a simple > 0 check on a value.

有人知道为什么在这种情况下不允许使用规则吗，以及是否存在另一种以原子方式执行upsert的方法?

Does anyone know why rules are not allowed in this situation and if there is another way to perform the upsert in an atomic way?

编辑:正如Andriy M评论的问题原子UPSERT SQL Server 2005中的SQL Server 已经包含了大量有关原子更新的信息，因此在此不再重复讨论.相反，我只想知道为什么MERGE语句不能在定义了规则的表上执行，并且有变通办法吗?

As Andriy M commented the question Atomic UPSERT in SQL Server 2005 already has a wealth of information on atomic upserts so I'll not duplicate the discussion here. Instead I'd like just to know why the MERGE statement cannot execute on tables with rules defined and is there a work-around?

推荐答案

• 规则是SQL Server的旧功能.
• 在将来的Microsoft SQL Server版本中将删除此功能.
一个>.我认为，这就是MERGE语句不支持带有规则的表的原因.
• 您可以将规则替换为检查约束.
• Rules are old feature of SQL Server.
• This feature will be removed in a future version of Microsoft SQL Server.. I think, this is the reason why MERGE statement does not supports tables with rules.
• You can replace rules with check constraints.

MERGE示例(具有CHECK约束的规则和):

MERGE example (with rules & with CHECK constraints):

CREATE RULE MyRule
AS 
@Status IN ('Y', 'N');
GO

CREATE TABLE dbo.SalesOrder
(
    SalesOrderID INT PRIMARY KEY
    ,OrderDate DATETIME NOT NULL
    ,IsDeleted CHAR(1) NOT NULL DEFAULT 'N'
);
GO

EXEC sp_bindrule @rulename='MyRule', @objname='dbo.SalesOrder.IsDeleted';
GO

INSERT  dbo.SalesOrder (SalesOrderID, OrderDate)
SELECT  1, '20110101'
UNION ALL
SELECT  2, '20110202'
UNION ALL
SELECT  3, '20110303';
GO

SELECT  *
FROM    dbo.SalesOrder;

PRINT '*****First test*****';
GO

MERGE   dbo.SalesOrder Dst
USING   (VALUES (1,'Y'), (4,'Y')) AS Src(SalesOrderID, IsDeleted) 
ON      Dst.SalesOrderID = Src.SalesOrderID
WHEN    MATCHED THEN UPDATE SET IsDeleted = Src.IsDeleted
WHEN    NOT MATCHED BY TARGET THEN INSERT (SalesOrderID, OrderDate, IsDeleted) VALUES (Src.SalesOrderID, GETDATE(), Src.IsDeleted);
GO

EXEC sp_unbindrule 'dbo.SalesOrder.IsDeleted'; --Disabling `MyRule` for IsDeleted column
ALTER TABLE dbo.SalesOrder --We "replace" the old rule with a new `CHECK` constraint
ADD CONSTRAINT CK_SalesOrder_IsDeleted CHECK( IsDeleted IN ('Y', 'N') );
GO

PRINT '*****Second test*****';
MERGE   dbo.SalesOrder Dst
USING   (VALUES (1,'Y'), (4,'Y')) AS Src(SalesOrderID, IsDeleted) 
ON      Dst.SalesOrderID = Src.SalesOrderID
WHEN    MATCHED THEN UPDATE SET IsDeleted = Src.IsDeleted
WHEN    NOT MATCHED BY TARGET THEN INSERT (SalesOrderID, OrderDate, IsDeleted) VALUES (Src.SalesOrderID, GETDATE(), Src.IsDeleted);
GO

SELECT  *
FROM    dbo.SalesOrder;

DROP TABLE dbo.SalesOrder;
DROP RULE MyRule;

结果:

Rule bound to table column.

(3 row(s) affected)
SalesOrderID OrderDate               IsDeleted
------------ ----------------------- ---------
1            2011-01-01 00:00:00.000 N
2            2011-02-02 00:00:00.000 N
3            2011-03-03 00:00:00.000 N

(3 row(s) affected)

*****First test*****
Msg 358, Level 16, State 1, Line 2
The target table 'Dst' of the MERGE statement cannot have any enabled rules.  Found rule 'MyRule'.
Rule unbound from table column.
*****Second test*****

(2 row(s) affected)
SalesOrderID OrderDate               IsDeleted
------------ ----------------------- ---------
1            2011-01-01 00:00:00.000 Y
2            2011-02-02 00:00:00.000 N
3            2011-03-03 00:00:00.000 N
4            2011-09-20 16:03:56.030 Y

(4 row(s) affected)

这篇关于为什么不允许MERGE语句的目标表具有已启用的规则?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！