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

Answer 1

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.