Enforce functional dependencies by using triggers in SQL99

Question

The question is as below:

For R(A, B, C, D, E), write a trigger to enforce AB->C on insert.

This is my solution:

CREATE TRIGGER fdEnforceInsert
BEFORE INSERT ON R
REFERENCING NEW ROW AS N
FOR EACH ROW
DECLARE counter INT
BEGIN 
  SELECT COUNT(*) INTO counter
  FROM R
  WHERE R.A = N.A AND R.B = N.B AND R. C <> N.C;
  IF(counter>0)
    THEN raise_exception ('AB->C on R was violated');
END;

One thing I'd like to ask is whether to use "before" or "instead of" in the second line?

score 2 · Answer 1 · answered Nov 29 '18 at 13:44

An instead of trigger is exactly that; it won't perform an insert even if data satisfy the constraint, so it won't be appropriate.

I understand that yours is a contrived example and that in real life you'll use a unique constraint to enforce such a rule, but the exists predicate nevertheless might be more efficient:

BEGIN 
  IF EXISTS (
    SELECT 1 FROM R
    WHERE R.A = N.A AND R.B = N.B AND R. C <> N.C;
  )
    THEN raise_exception ('AB->C on R was violated');
END;

score 1 · Answer 2 · answered Oct 17 '19 at 19:09

For validation triggers I find the WHEN clause to be the most intuitive:

CREATE TRIGGER fdEnforceInsert
BEFORE INSERT ON R
REFERENCING NEW ROW AS N
FOR EACH ROW
    WHEN ( EXISTS (
               SELECT 1 
               FROM R
               WHERE R.A = N.A 
                 AND R.B = N.B 
                 AND R. C <> N.C 
           )
         ) raise_exception ('AB->C on R was violated');

score 0 · Answer 3 · answered Oct 17 '19 at 17:28

Since this question is about standard SQL, your proposed solution will work, because the SQL standard decrees that the default transaction isolation level be SERIALIZABLE.

In practice, no database I know uses that isolation level by default because of the impact on performance or concurrency, and some (like Oracle) even do not implement it properly.

On a lower isolation level, enforcing integrity with a trigger like that won't work reliably, because concurrent executions of the trigger would not see the other transaction's effects on the database, and you could end up with inconsistent data. This race condition can only be avoided with SERIALIZABLE isolation.

Enforce functional dependencies by using triggers in SQL99

3 Answers3