SQL Constraint Check or a Trigger?

Question

I've been searching the net for some answer to this. but all I get is the simple answers.

I have a few columns in my database that should have a constraint on their maximum value but not if another column is set to false

eg. I have the following values in the table

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]

In this table Row2 is being provided by an external program, where ValueA is initially 5000, however the value for that row is set to FALSE, therefore it will limit the value to 3750 and add 1250 to ValueB

Now the idea is, that if set to true, it can bypass the check. but if it's set to false for that row and the value is bigger than 3750it should reduce ValueA back to 3750 and put the remainder in ValueB.

Is this possible to do with a Constraint Check expression, or would it be more prudent to use a Before Update trigger on this?

score 1 · Answer 1 · answered Feb 21 '12 at 19:03

1

my opinion, this sounds like 'complicated' business logic.

by complicated, i mean some if checks, and some differing behavior based on rules.

in this case i would do the check in a trigger.

answered Feb 21 '12 at 19:03

Randy

16,480
1
37
55

Thank you for your switft responce, I was leaning towards a trigger myself just unsure whether It would be bad to use as I read so much negative things about them. – Raskaroth Feb 21 '12 at 19:05
this type of use is great - people get in trouble when burying huge procedural behavior that slows performance. – Randy Feb 21 '12 at 19:08
Thanks, then I have the answer I required! – Raskaroth Feb 21 '12 at 19:10
1

Of course don't forget to write (and test) that trigger so it can handle multiple record inserts/updates. SQL Server does not loop through each record in a batch in a trigger. – HLGEM Feb 21 '12 at 20:52
good point HGLEM... there is probably an underlying normalization issue here as well. – Randy Feb 22 '12 at 00:00

score 1 · Answer 2 · answered Feb 21 '12 at 19:08

1

You need a trigger for this because constraints can't set the value of another column based on a condition (as you are doing here)

answered Feb 21 '12 at 19:08

BYS2

5,199
5
24
32

onedaywhen · Accepted Answer · 2012-02-22T15:27:09.600

I suggest you have two tables: a staging table to hold the raw data from the external program and a second table to which the business rules have been applied. This second table could be simply a VIEW that queryies the data by applying the rules e.g. (I'm using a CTE to simulate the staging table with sample data; also, SQL Server doesn't hav3e a true Boolean type so I'm simulating it using a CHAR(1) column and the name is doing my head in so I'll change it to 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 you want the second table to be a base table, then the following constraints (using traditional logic) would seem to apply:

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

In SQL as CHECK constraints (I've guessed some 'common sense' business rules):

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 )
);

You could then use the above query to copy data from the staging table to the base table while not violating any constraints but safe in the knowledge that if you got the query wrong the procedure wold fail!

Thank you, Couldn't come up with a better name for the true false bit, it's indeed a char field containing either T or F. Thank you for your insight. I'll mark this as my answer as it contains an example. — Raskaroth, Feb 24 '12 at 08:55

SQL Constraint Check or a Trigger?

3 Answers3