[CDBI] Using before_update trigger to history

Bill Moseley moseley at hank.org
Fri Jan 20 20:14:11 GMT 2006

I thought there was a wiki entry on using before_update to keep a
history of changes to a record.

I want to create a copy of a row before updating.  I'm not thrilled
about the code below, so I'm wondering what others might be doing.

Is there a "suggested" way to keep history records?  Probably better
as a trigger in the database, I suspect.

__PACKAGE__->add_trigger( before_update => sub {
    my $self        = shift;
    my $table       = $self->table;
    my $id_col      = $self->primary_column->name;
    my $hist_table  = $table . '_history';
    my $hist_class  = ref($self) . 'History';

    $self->last_updated( 'now()' )
        if $self->find_column( 'last_updated' );

    # Gather up columns common in both tables.
    my @hist_cols =
        grep { $hist_class->find_column( $_ ) }
            grep { $_ ne $id_col }
                map { $_->name } $self->columns;

    my @source_cols = @hist_cols;

    # copy the id from the source table to the id in the history table
    push @source_cols, $id_col;
    push @hist_cols, 'copy_of_' . $id_col;

    my $source_cols = join ', ', @source_cols;
    my $hist_cols = join ', ', @hist_cols;

    my $sth = $self->db_Main->prepare(<<"");
        INSERT INTO $hist_table ($hist_cols)
            SELECT $source_cols from $table
            WHERE id = ?

    $sth->execute( $self->id );


Bill Moseley
moseley at hank.org

More information about the ClassDBI mailing list