AFTER UPDATE trigger not working trying to specify WHEN

From: Joe Saling <rojoni8_at_hotmail.com>
Date: 6 Nov 2003 15:43:55 -0800
Message-ID: <f61c85b2.0311061543.4eaa8380@posting.google.com>

I don't know triggers, but this seems like it should be simple. Want to update a history table with changes to particular field of other table. Field is updated by third party software, and seems to update table three times every time. I only want the actual change recorded. Are there any suggestions on why this doesn't work?

When price_status = 'M','L' or 'F' and is switched to another of these, the trigger will record history. When price_status = ' ' (instead of null) and is changed to 'M','L' of 'F' or from one of these to ' ', no history is recorded.

When I remove the when statement, price_status changes are recorded with new and old values, but also recorded twice more with values exactly the same. This generates an enormous amount of data.

---

CREATE OR REPLACE TRIGGER t_hist_trig
AFTER UPDATE OF price_status ON t FOR EACH ROW WHEN (old.price_status != new.price_status)

DECLARE
l_action CHAR(1);
BEGIN
l_action := 'U';

INSERT INTO t_history

(ap_num, action, datestamp, oracle_user, 
old_lock_type, old_price_status, 
old_lockin_date, old_lockin_expires, 
new_lock_type, new_price_status, 
new_lockin_date, new_lockin_expires) 

VALUES
(:OLD.ap_num, l_action, SYSDATE, USER,
:OLD.lockin_type, :OLD.price_status,
:OLD.lockin_date, :OLD.lockin_expires,
:NEW.lockin_type, :NEW.price_status,
:NEW.lockin_date, :NEW.lockin_expires);
END;
/

I also have tried:
IF :old.price_status != :new.price_status THEN
INSERT ..... This also fails.

Any suggestions are appreciated.

Thank You.
Joe Received on Thu Nov 06 2003 - 17:43:55 CST