[CDBI] Using before_update trigger to history

[prev] [thread] [next] [Date index for 2006/01/20]

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@xxxx.xxx


_______________________________________________
ClassDBI mailing list
ClassDBI@xxxxx.xxxxxxxxxxxxxxxx.xxx
http://lists.digitalcraftsmen.net/mailman/listinfo/classdbi