from null to not null and vice versa

[Former Member]

trying to write trigger when a) when emp_id changes from NULL to a NOT NULL value OR b) when emp_id changes from NOT NULL value to NULL Then only Update some xyz table. How to write that condition?

[Breck_Carter]

CREATE TABLE t (
   pkey     INTEGER NOT NULL PRIMARY KEY,
   emp_id   INTEGER NULL );

CREATE TRIGGER tru_t 
   BEFORE UPDATE
   ON t
   REFERENCING OLD AS old_t
   NEW AS new_t
   FOR EACH ROW
BEGIN
   IF old_t.emp_id IS NULL AND new_t.emp_id IS NOT NULL THEN
      MESSAGE 'emp_id changing from NULL to NOT NULL';
   ELSEIF old_t.emp_id IS NOT NULL AND new_t.emp_id IS NULL THEN
      MESSAGE 'emp_id changing from NOT NULL to NULL';
   ELSE
      MESSAGE 'emp_id staying NULL, or staying NOT NULL';
   END IF;
END;

INSERT t VALUES ( 1, NULL );
UPDATE t SET emp_id = 123 WHERE pkey = 1;
UPDATE t SET emp_id = NULL WHERE pkey = 1;
UPDATE t SET emp_id = 456 WHERE pkey = 1;
UPDATE t SET emp_id = 789 WHERE pkey = 1;

emp_id changing from NULL to NOT NULL
emp_id changing from NOT NULL to NULL
emp_id changing from NULL to NOT NULL
emp_id staying NULL, or staying NOT NULL

[VolkerBarth]

Oops, I was about to hint at the new v12 "IS DISTINCT FROM" search condition - but that would also be TRUE if emd_id would be altered from one NOT NULL value to a different NOT NULL value, and that seems not desired here...