[CDBI] Disabling cascading before_update/after_update triggers

Hi all,

I've just had cause to add some functionality to a project that uses
cdbi and ran into some issues with update triggers (using db-level
triggers isn't an option here).

I've found a way around my problem and I'm sharing it here just in case
its useful but I would also ask if anyone can think of a nicer way to
achieve this?


I have the situation where a table contains some derived columns. Ie
simplistically:

  time        unsigned int not null
  skill_level decimal(2,2) not null default 1.0
  total_time  unsigned int not null

Where total_time is floor(time * skill_level).

I need to make sure that every create/update of an entry in this table
causes the total_time to be recalculated (there are quite few other more
complex relationships there as well and, no, I can't change the schema
:) ). Autoupdate is on.

For various reasons I chose to do this in a single
after_create/after_update trigger rather than after_set_X triggers (of
which there would have been many and varied).

No, I'm not bothering with code-level transactions here.

I had to extend the disabling triggers example on
http://wiki.class-dbi.com/wiki/Working_with_triggers to allow me to only
disable the after_update trigger while I was in the function call - all
other triggers had to remain functional.

Thus, in your base class:

use vars qw/$DISABLE_TRIGGERS/;
# override call_trigger to check flag or hash of flags
# allows either a $DISABLE_TRIGGERS=1 to disable all or
# a hash of trigger names to specifically disable.
sub call_trigger{
  my $self = shift;
  return if ( ( !ref($DISABLE_TRIGGERS) && $DISABLE_TRIGGERS ) ||
              ( ref($DISABLE_TRIGGERS) eq 'HASH' &&
$DISABLE_TRIGGERS->{$_[0]} ) );
  return $self->SUPER::call_trigger(@_);
}

In your class code:

sub _recalc_derived_fields {
  my $self = shift;

  # turn off cascading update triggers.
  local $My::Base::Class::DISABLE_TRIGGERS = { after_update => 1 };

  # do all calculations then a single ->set()
}
__PACKAGE__->add_trigger( after_create => \&_recalc_derived_fields );
__PACKAGE__->add_trigger( after_update => \&_recalc_derived_fields );


Other ways? Is this useful to anyone and worth adding to the wiki?

-- 
Andrew O'Brien
Development Manager
e: [email protected]    p: +61 2 9461 0114
w: www.oriel.com.au        f: +61 2 9431 6700