Database trigger: comparison of column value with empty variable

Question

I'm preparing for an exam on Model-Driven Development. I came across a specific database trigger:

CREATE TRIGGER tManager_bi
FOR Manager BEFORE INSERT AS
DECLARE VARIABLE v_company_name CHAR(30);
BEGIN
  SELECT M.company
  FROM   Manager M
  WHERE  M.nr = NEW.reports_to
  INTO  :v_company_name;

  IF (NOT(NEW.company = v_company_name))
    THEN EXCEPTION eReportsNotOwnCompany;
END

This trigger is designed to prevent input in which a manager reports to an outside manager, i.e. one that is not from the same company. The corresponding OCL constraint is:

context Manager
   inv: self.company = self.reports_to.company

The relevant table looks like (simplified):

CREATE TABLE Manager
(
  nr INTEGER NOT NULL,
  company VARCHAR(50) NOT NULL,
  reports_to INTEGER,
  PRIMARY KEY (nr),
  FOREIGN KEY (reports_to) REFERENCES Manager (nr)
);

The textbook says that this trigger will also work correctly when the newly inserted manager doesn't report to anyone (i.e. NEW.reports_to is NULL), and indeed, upon testing, it does work correctly.

But I don't understand this. If NEW.reports_to is NULL, that would mean the variable v_company_name will be empty (uninitialized? NULL?), which would then mean the comparison NEW.company = v_company_name would return false, causing the exception to be thrown, right?

What am I missing here?

(The SQL shown is supposed to be SQL:2003 compliant. The MDD tool is Cathedron, which uses Firebird as an RDBMS.)

_"This trigger is designed to prevent input in which a manager reports to himself."_ Are you sure? That trigger seems written to prevent input in which a manager reports to someone at another company. — pilcrow, Aug 22 '16 at 13:39

score 6 · Accepted Answer · edited Aug 21 '16 at 08:05

6

You're missing the fact that when you compare NULL to NULL (or to any other value), the answer is NULL, not false. And negation of NULL is still NULL, so in the IF statement the ELSE part would fire (if there is one).

I suggest you read the Firebird Null Guide for better understanding it all.

edited Aug 21 '16 at 08:05

Mark Rotteveel

100,966
191
140
197

answered Aug 20 '16 at 18:38

ain

22,394
3
54
74

So, just to be sure: the value that is put into the variable `v_company_name` after the select that returns an empty table is `NULL`, not some (other) kind of undefined value? – Stefan van den Akker Aug 20 '16 at 18:49
1

When the select statement returns empty resultset, then the value of the `v_company_name` remains unchanged (it is not set to NULL!). So it would have the value it had before the select statement is executed, which in this case should be NULL. – ain Aug 20 '16 at 18:54
You're absolutely right. From your link: "If you declare a variable in a stored procedure or trigger, its value is undefined and its state is `NULL` from the moment of creation until some value is assigned to it." – Stefan van den Akker Aug 20 '16 at 18:58

score 2 · Answer 2 · answered Aug 22 '16 at 10:02

AS. Making this answer for the sake of code highlighting.

You might want to modify your trigger to respond to both updates and inserts.

CREATE TRIGGER tManager_bi
FOR Manager BEFORE INSERT OR UPDATE AS
...

You also may avoid hand-writing the trigger at all, if you do not need that specific exception identifier.

You may just use SQL Check Constraint for that

alter table Manager  
    add constraint chk_ManagerNotRespondsOneself
        CHECK ( NOT EXISTS (
          SELECT * FROM Manager M
          WHERE  M.nr = reports_to
            AND  M.company = company 
        ) )

Specifying custom exceptions over CHECK constraints does not look possible now... http://tracker.firebirdsql.org/browse/CORE-1852

Database trigger: comparison of column value with empty variable

2 Answers2