SQL 约束检查还是触发器？

Question

我一直在网上搜索这个问题的一些答案。但我得到的只是简单的答案。

我的数据库中有几列应该对其最大值进行约束，但如果另一列设置为 false 则不会

例如。我在表中有以下值

ROW    ValueA          TRUE/FALSE       ValueB
0      3750            TRUE              0
1      5000            TRUE              0
2      5000            FALSE             0    [INITIAL VALUES PROVIDED]
2      3750            FALSE             1250 [ACTUAL VALUES ACCEPTED]

在此表中，Row2 由外部程序提供，其中 ValueA 最初为 5000，但是该行的值设置为 FALSE，因此它将将该值限制为 3750，并将 1250 添加到 ValueB

现在的想法是，如果设置为 true，它可以绕过检查。
但如果该行设置为 false 并且该值大于 3750，则应将 ValueA 减少回 3750 并将余数放入 ValueB。

这可能与约束检查表达式有关，还是在此情况下使用更新前触发器更谨慎？

Answer 1

我建议您有两个表:一个用于保存来自外部程序的原始数据的临时表和一个应用了业务规则的第二个表。第二个表可能只是一个 VIEW通过应用规则查询数据，例如(我使用 CTE 来模拟带有示例数据的临时表；另外，SQL Server 没有真正的 bool 类型，所以我使用 CHAR(1) 列模拟它，名称正在我脑海中浮现，所以我将其更改为 some_flag !):

WITH Staging
     AS
     (
      SELECT * 
        FROM (
              VALUES (0, 3750, 'T'), 
                     (1, 5000, 'T'), 
                     (2, 5000, 'F')
             ) AS T (ROW, ValueA, some_flag)
     )
SELECT ROW, ValueA, some_flag,  
       0 AS ValueB
  FROM Staging
 WHERE ( ValueA <= 3750 OR some_flag = 'T' )
UNION
SELECT ROW, 3750 AS ValueA, some_flag,  
       ( ValueA - 3750 ) AS ValueB
  FROM Staging
 WHERE ValueA > 3750 AND some_flag = 'F';

如果您希望第二个表成为基表，那么以下约束(使用传统逻辑)似乎适用:

IF some_flag = 'F' THEN ValueA <= 3750
IF some_flag = 'T' THEN ValueB = 0
IF some_flag = 'F' AND ValueA < 3750 THEN ValueB = 0

在 SQL 中为 CHECK约束(我已经猜到了一些“常识”业务规则):

CREATE TABLE MyTable
(
 ROW INTEGER NOT NULL UNIQUE, 
 ValueA INTEGER NOT NULL CHECK ( ValueA > 0 ), 
 some_flag CHAR(1) NOT NULL CHECK ( some_flag IN ( 'T', 'F' ) ), 
 ValueB INTEGER NOT NULL CHECK ( ValueB >= 0 ), 
 CHECK ( some_flag <> 'F' OR ValueA <= 3750 ), 
 CHECK ( some_flag <> 'T' OR ValueB = 0 ), 
 CHECK ( some_flag <> 'F' OR ValueA >= 3750 OR ValueB = 0 )
);

然后，您可以使用上述查询将数据从临时表复制到基表，同时不会违反任何约束，但安全的是，如果查询错误，过程将失败!