Home » SQL & PL/SQL » SQL & PL/SQL » Trigger not working with empty fields
Trigger not working with empty fields [message #238395] Thu, 17 May 2007 15:53 Go to next message
challey
Messages: 4
Registered: May 2007
Junior Member
Hi there,

I'm working on a trigger and I'm having a hard time when I have empty fields in my record.

I'm keeping track of changes for audit purposes and I only want to copy fields that have been modified and not the entire record. So I compare the OLD and NEW values to see if they differ.

If the OLD and NEW values are not empty strings and are different, the trigger works fine. But as soon as one of the values is empty the trigger doesn't do anything. So if :old.field is empty but :new.field is not, nothing happens. And same for the other way around.

Here's an example of my trigger:

CREATE OR REPLACE TRIGGER articles_update
    AFTER UPDATE ON articles
    FOR EACH ROW
    DECLARE
        new_id number(9) := 0;
    BEGIN
        if(:new.title_eng != :old.title_eng) then
                SELECT tracking_id_seq.nextval INTO new_id FROM dual;
                INSERT INTO tracking VALUES(new_id,'title_eng',:new.title_eng,'update',:new.article_id);
        end if;
        if(:new.title_fra != :old.title_fra) then
                SELECT tracking_id_seq.nextval INTO new_id FROM dual;
                INSERT INTO tracking VALUES(new_id,'title_fra',:new.title_fra,'update',:new.article_id);
        end if;

   END articles_update;


Can anyone help me?

Thank you!
Smile
Re: Trigger not working with empty fields [message #238401 is a reply to message #238395] Thu, 17 May 2007 16:23 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Null is never equal to anything, including itself.

Also, null is never not equal to anything, including itself.

You gotta do explicit null comparisons, or something else to account for the nulls like a nvl().

MYDBA@orcl > create table test(a number, b number);

Table created.

MYDBA@orcl > insert into test values (null, null);

1 row created.

MYDBA@orcl > select * from test where a = b;

no rows selected

MYDBA@orcl > select * from test where a != b;

no rows selected

MYDBA@orcl >

Re: Trigger not working with empty fields [message #239927 is a reply to message #238401] Wed, 23 May 2007 12:21 Go to previous messageGo to next message
challey
Messages: 4
Registered: May 2007
Junior Member
Ah, gotcha!

Now I just have to figure out how to do the explicit null comparison (been trying a few things).

Thank you!
Re: Trigger not working with empty fields [message #239929 is a reply to message #239927] Wed, 23 May 2007 12:27 Go to previous messageGo to next message
challey
Messages: 4
Registered: May 2007
Junior Member
Got it!
if((:new.keywords_eng <> :old.keywords_eng) 
   or (:new.keywords_eng is NULL and :old.keywords_eng is not NULL) 
   or (:new.keywords_eng is not NULL and :old.keywords_eng is NULL)) then

Works like a charm! Smile
Re: Trigger not working with empty fields [message #239932 is a reply to message #239929] Wed, 23 May 2007 12:32 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Good work finding that by yourself!
And thanks for the feedback.

Keep it up, keep trying yourself, and if you're stuck come back for some directions where to go Smile
Re: Trigger not working with empty fields [message #240012 is a reply to message #238395] Wed, 23 May 2007 20:50 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
I agree with Frank's comments.

I'd also like to add that I typically try and do things with AND's, instead of OR's, if it otherwise wouldn't matter. It can make for more efficient processing due to short circuiting.

Also I like to (these are just my preferences mind you, feel free to disagree or ignore them) state things "in the positive", or to put it another way, "what you are trying to do".

Hmm, neither of those phrases is what I mean...

Basically, lets say you have a trigger or a procedure for that matter. You want to only perform the main actions of the body if two values change (lets ignore nulls for the following).

You could say:
if val1 != val2 then
    do some work here
    which might turn out
    to be long and complicated
end if;


Or, instead you could do:

if val1 = val2 then
    return;
end if;

do some work here
which might turn out
to be long and complicated
but it will be easier to read
and to maintain
because it continues to the end of the procedure



This is just a preference, and actually disagrees with the notion of one entry and one exit point, which is a notion held by many. But in my view, I like to put such checks at the top, where it is easier to see what the checks are, and it just seems more explicit to me that you test something and exit right away if your condition passed. It especially helps if you have a chain of several conditions that you want to test.

After re-reading your initial post, I realize that a lot of what I just typed is probably not applicable to your current situation. But, I'll share these thoughts anyway as they are intended to be more general and stylistic anyway.

BUT, on the more concrete side, you can refer to your sequence nextval right inside your insert statement, so there is no need to first get it from dual into a local variable. Just do it all in one step (insert into table values (seq.nextval);

Also, in situations like yours, try to limit the "field mapping" to only once if possible. You know, the determining of what source data item goes into your tracking table structure. Basically try and have just one insert statement, or one call to a procedure with the field pointers. It helps avoid errors and it helps when you have to change something. Again a style thing, and it must coincide with your business rules, which may or may not be possible in your current example.
Re: Trigger not working with empty fields [message #240273 is a reply to message #240012] Thu, 24 May 2007 09:02 Go to previous message
challey
Messages: 4
Registered: May 2007
Junior Member
Thanks!

I'll definitely use the sequence call in my insert, it makes the code shorter.

As for the rest of your recommendations, I'll keep them in mind for a future project cause I'm really tight on time for this application.

I'm a Web Developer not a DBA, but we don't really have a DB designer to help us with our DB stuff so I have to do it myself. My DBA helps me out and gives me tips when he can, but it's not the same as having a dedicated person working on the applications with us.

I like that I can do this though, I like learning new things. It's just that sometimes I don't have all the knowledge I need and I don't have time to do much research.

The joys of working here... overworked and understaffed, lol! Smile
Previous Topic: Formatting data in phone format in SELECT
Next Topic: How to rename a column in a view.
Goto Forum:
  


Current Time: Sat Dec 03 11:48:33 CST 2016

Total time taken to generate the page: 0.11575 seconds