on 2012 Nov 30 2:46 PM
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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...
User | Count |
---|---|
71 | |
10 | |
9 | |
7 | |
6 | |
6 | |
6 | |
5 | |
5 | |
5 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.