Well, the general logic/flow could look like this:

# $person is an object holding a record with getters for each field.

if( record_exists( $person->id() ) ) {
    log_change( update_record( $person ) );
} else {
    log_new( add_record( $person ) );
}
[download]

The rest is sort of fill-in-the-blanks, but you seem to already know how to handle DB work, so I don't think that's where you're having trouble. If that's the issue, you'll have to be more specific as to what kind of help you were after.

You should be using placeholders though, with your DB access. See the documentation for DBI for more info. Placeholders improve security as well as providing for easier code maintenance.

By the way: Are you sure that keying off of a person's phone number is a good idea? You're sure to get multiple entries for the same individual. People never can recall what number they used when they first interacted with an organization. Was it my cell? My home? My office? My Google Voice? My Skype? My wife's cell? Was it my old AT&T cell, or my new Verizon one? Addresses aren't exactly ideal either; people move more than you might guess. Email addresses change too. It's really hard to ensure that you don't end up with duplicates regardless of how you maintain the database, but phone numbers are a weak starting point. If human interaction is a possibility, the person at the front line could always ask, "Are you Mr Spacey from Los Angeles, or Mr Spacey from Detroit?"

Use a database-level trigger for creating the change_log entries. That way the integrity and completeness of the log is enforced at a database layer, and works fine even if someboy uses something else than your perl script to make changes to the catalog table.

That's the best way, in theory.

However, he is using MySQL. Does his version of Mysql support triggers? I think the newer versions do, but he doesn't say what version of Mysql he is using. (Yes, triggers have been added in 5.0.2.)

There's a reason why Mysql is traditionally considered a toy database, and this is one of them.

It's been ages since I've written direct database queries, but something like the following could be what you use.

Using prepared statements and placeholders are the biggest things I'd recommend:

#!/usr/bin/perl

use strict;
use warnings;

use DBI;
use DBD::mysql;

#this is my config module
use DBase::MySQL::Config qw(DB_CUSTOMER_CATALOG DB_USER DB_PW);

my $dbh = DBI->connect(DB_CUSTOMER_CATALOG, DB_USER, DB_PW);

my $catalog_u = $dbh->prepare(q{UPDATE catalog SET name=?, address=?, 
+notes=? WHERE phone_number=?});
my $catalog_i = $dbh->prepare(q{INSERT INTO catalog SET phone_number=?
+, name=?, address=?, notes=?});
my $catalog_c = $dbh->prepare(q{SELECT COUNT(*) FROM catalog WHERE pho
+ne_number=?});
my $change_i = $dbh->prepare(q{INSERT INTO change_log SET timestamp=NO
+W(), number_record=?, action=?});

while(<DATA>) {
    chomp;
    my ($phone, $name, $address, $notes) = split "\t";
    
    my $catalog_c->execute($phone) or die $dbh->errstr;
    my ($exists) = $catalog_c->fetchrow_array;
    
    if ($exists) {
        my $changed = $catalog_u->execute($name, $address, $notes, $ph
+one) or die $dbh->errstr;
        if ($changed) {
            $change_i->execute($phone, 'update') or die $dbh->errstr;
        }
    } else {
        $catalog_i->execute($phone, $name, $address, $notes) or die $d
+bh->errstr;
        $change_i->execute($phone, 'insert') or die $dbh->errstr;
    }
}

$catalog_u->finish;
$catalog_i->finish;
$catalog_c->finish;
$change_i->finish;

$dbh->disconnect();

__END__
[download]

However, stored procedures have been around in MySQL for 8 years. So it seems reasonable to write access stored procedures that have the logging to log tables built in. Calling these from Perl instead of coding SQL in the Perl code is usually preferable anyway. There are overheads to dragging data out of the database, processing it and putting it back. As a rule it is better to let Perl pre- or post-process database data but to use stored procedures where possible, especially if the database is on a different machine from where the Perl will run. A trigger is anyway a special case of a stored procedure.

...if a record exists, update the record... If a record doesn't exist, insert the new record