|
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? |
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
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...
(30 Nov '12, 15:13)
Volker Barth
|
