Neftas Neftas - 3 months ago 15
SQL Question

Database trigger: comparison of column value with empty variable

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 himself. The corresponding OCL constraint is:

context Manager
inv: self.reports_to = null or (self <> self.reports_to)


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

ain ain
Answer

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