The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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?

asked 30 Nov '12, 14:46

roadtrain64's gravatar image

roadtrain64
1366710
accept rate: 0%


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
permanent link

answered 30 Nov '12, 15:02

Breck%20Carter's gravatar image

Breck Carter
26.6k418575824
accept rate: 21%

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
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×18

question asked: 30 Nov '12, 14:46

question was seen: 529 times

last updated: 30 Nov '12, 15:14