Home » SQL & PL/SQL » SQL & PL/SQL » Problem with trigger and NULLS
Problem with trigger and NULLS [message #207351] Tue, 05 December 2006 04:11 Go to next message
only_me
Messages: 1
Registered: December 2006
Location: Jersey
Junior Member
Hi All

I have a trigger that compares the old.name and new.name values and if they differ inserts a row into an audit table.

The problem is if name is NULL and is updated to something the trigger does not fire. Also if name has a value and the value is deleted so it is now NULL the trigger does not fire.

The only time the trigger will insert a row into the subscriber_amends table is when there is data in name and this data is changed to anything else other than NULL !! Is there a way around this ???

My trigger is as follows:
CREATE OR REPLACE TRIGGER test_trigger
before UPDATE Of 
NAME 
ON subscribers
REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW
WHEN 
(
NEW.NAME != OLD.NAME
)
BEGIN
IF (:NEW.NAME != :OLD.NAME) THEN
INSERT INTO SUBSCRIBER_AMENDS
(NAME_ID, 
COLUMN_NAME, 
OLD_VALUE, 
NEW_VALUE, 
AMEND_USER, 
AMEND_DT)
VALUES
(:old.name_id,'NAME',
:old.NAME,
:NEW.NAME,
UPPERCASE(DBMSINFO( 'USERNAME')), 
SYSDATE);
END IF;
END test_trigger; 
Re: Problem with trigger and NULLS [message #207356 is a reply to message #207351] Tue, 05 December 2006 04:25 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You could try
WHEN 
((:NEW.NAME != :OLD.NAME) OR (NEW.NAME IS NULL AND OLD.NAME IS NOT NULL) OR (NEW.NAME IS NOT NULL AND OLD.NAME IS NULL)
)
Or, if you can find some values (xxxx and yyyy) that you can guarantee won't be in NAME, then you can use
WHEN 
(
NVL(NEW.NAME,'xxxx') != NVL(OLD.NAME,'yyyy')
)

You can lose the IF statement
IF (:NEW.NAME != :OLD.NAME) THEN
as that just duplicates the WHEN clause of the trigger

[Edited to correct syntax]

[Updated on: Tue, 05 December 2006 04:31]

Report message to a moderator

Re: Problem with trigger and NULLS [message #207357 is a reply to message #207351] Tue, 05 December 2006 04:25 Go to previous messageGo to next message
praveena7781
Messages: 1
Registered: July 2006
Location: AndraPradesh
Junior Member
Hi,

I Think your problem will solve by using NVL.
you can try like this

WHEN
(
NVL(NEW.NAME,'N') != NVL(OLD.NAME,'Y')
)

Hope it is useful to you......
Re: Problem with trigger and NULLS [message #207359 is a reply to message #207351] Tue, 05 December 2006 04:26 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Not tested but try like this.


if (nvl(:NEW.NAME, '$$') != nvl(:OLD.NAME,'$$'))
then
Insert .....



i.e. Use NVL with some value that you do not expect and try
Re: Problem with trigger and NULLS [message #207360 is a reply to message #207356] Tue, 05 December 2006 04:29 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Now this post is not necessary. Cool

By
Vamsi

[Updated on: Tue, 05 December 2006 04:33]

Report message to a moderator

Re: Problem with trigger and NULLS [message #207362 is a reply to message #207360] Tue, 05 December 2006 04:31 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Actually, I need to remove the IF and THEN entirely.
Previous Topic: dynamic view
Next Topic: Alternative to avoid ORA-01795 Error
Goto Forum:
  


Current Time: Sun Dec 04 14:41:05 CST 2016

Total time taken to generate the page: 0.13351 seconds