Home » SQL & PL/SQL » SQL & PL/SQL » Trigger not firing (Oracle 10.2.0.3.0)
Trigger not firing [message #323124] Tue, 27 May 2008 06:39 Go to next message
sabi00050
Messages: 4
Registered: May 2008
Junior Member
Hello,

we have the following trigger on a table:
CREATE OR REPLACE TRIGGER TRIG.TAU_CUSTOMER_ON_1
BEFORE DELETE OR INSERT OR UPDATE
ON AURATIX.STD_CUSTOMER
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN 
 IF INSERTING THEN BEGIN 
  INSERT INTO TAU_CUSTOMER VALUES ( 
  'ONYX', 'I', SYSDATE, null, null, 
  :NEW.CUSTOMERNO); 
  END;
  ELSIF UPDATING THEN  IF 
:NEW.CITY <> :OLD.CITY
 OR :NEW.PHONE <> :OLD.PHONE
 OR :NEW.MOBILEPHONE <> :OLD.MOBILEPHONE
 OR :NEW.FAX <> :OLD.FAX
  THEN BEGIN 
  INSERT INTO TAU_CUSTOMER VALUES ( 
  'ONYX', 'U', SYSDATE, null, null, 
  :NEW.CUSTOMERNO); 
  END;
  END IF; 
  ELSIF DELETING THEN BEGIN 
  INSERT INTO TAU_CUSTOMER VALUES ( 
  'ONYX', 'D', SYSDATE, null, null, 
  :OLD.CUSTOMERNO); 
  END;
  END IF;
END;
/

The trigger works correctly if the column "city" is updated on the original table. However, if we update the columns Phone, Mobilephone or Fax, the trigger does not fire.
All columns are varchar2 (40 byte).

Does anyone have an idea what could be wrong here?
Thanks!
Sabine

[Edit MC: add code tags, please do it next time]

[Updated on: Tue, 27 May 2008 07:02] by Moderator

Report message to a moderator

Re: Trigger not firing [message #323125 is a reply to message #323124] Tue, 27 May 2008 06:42 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Don't worry, the trigger DOES fire.
Did you perhaps test it by updating NULL-columns to having a value?
Remember that NULL <> anything is never true.
Re: Trigger not firing [message #323127 is a reply to message #323125] Tue, 27 May 2008 06:50 Go to previous messageGo to next message
sabi00050
Messages: 4
Registered: May 2008
Junior Member
Hello,

I tested both cases: updating a NULL entry to something, and modifying existing entries.

I could understand that modifying NULL does not trigger, but in the other case, it should work....
"Not work" means that the trigger has not inserted a record into the table tau_customer.
Re: Trigger not firing [message #323130 is a reply to message #323124] Tue, 27 May 2008 07:01 Go to previous messageGo to next message
sabi00050
Messages: 4
Registered: May 2008
Junior Member
oh, and by the way:
how could I trigger an update if that update changes
a NULL entry into something?

Thanks!!
Re: Trigger not firing [message #323131 is a reply to message #323130] Tue, 27 May 2008 07:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use, for instance, something like "NLV(:NEW.CITY,'#') <> NVL(:OLD.CITY,'#')".

Regards
Michel
Re: Trigger not firing [message #323143 is a reply to message #323131] Tue, 27 May 2008 07:48 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
A neater way, which does not rely on assumptions that CITY will never have the value '#':
if  (  :new.city is null and :old.city is not null
    or :new.city is not null and :old.city is null
    or :new.city != :old.city
    ) 
and (...
Re: Trigger not firing [message #323149 is a reply to message #323143] Tue, 27 May 2008 08:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Wow! Too long to write. Wink

Regards
Michel
Re: Trigger not firing [message #323152 is a reply to message #323149] Tue, 27 May 2008 08:09 Go to previous message
sabi00050
Messages: 4
Registered: May 2008
Junior Member
Thanks a lot for your help!!
Previous Topic: change the format from APR 07 to 042007
Next Topic: Order by in cursor
Goto Forum:
  


Current Time: Tue Dec 06 08:51:52 CST 2016

Total time taken to generate the page: 0.11174 seconds