Thread: is it me or trigger side effects

Hi all,
I have written a trigger to update a table called categories whenever a tuple is either deleted or inserted in tables articles, media, and links. It works fine for inserts but "pukes" on deletes :). I am using two auxiliary addcount(varchar) and delecount (varchar)
I am getting the following error on deletes:
 record new is unassigned yet .
below is my code.

drop function addCount(varchar);
create function AddCount(varchar) returns integer as '
declare
    cat_id alias for $1;
    len integer;
    cnt integer;
    cond varchar;

begin
     len:= length(cat_id);
     cnt:=0;
     for i in  1 .. len loop
          cnt:=cnt +1;
          cond:=substr(cat_id,1,cnt);

          update categories set items= items+1 where id like cond;
     end loop;
 

     return cnt;

end;
' language 'plpgsql';

select addCount('KDA');
 
 
 

drop function delCount(varchar);
create function delCount(varchar) returns integer as '
declare
    cat_id alias for $1;
    len integer;
    cnt integer;
    cond varchar;
begin
    len:= length(cat_id);
    cnt:=0;
     for i in  1 .. len  loop
           cnt:=cnt +1;
           cond:=substr(cat_id,1,cnt);
          update categories set items= items-1 where id like cond;

     end loop;
     return cnt;

end;
' language 'plpgsql';

select delCount('KDA');
drop trigger trigger_update_articles on articles;
drop trigger trigger_update_links on links;
drop trigger trigger_update_media on media;
drop function updateCat();

create function updateCat() returns opaque as '
declare
    rec     record;
    rename new to cat;
    rename old to ct;
    maxlen integer;

begin
     if tg_op = ''INSERT''  and cat.category is null then
          raise exception ''You are missing entry for category field'';

     else
          if cat.display then
               maxlen:= addCount(cat.category);
               return cat;
          else
               return cat;
          end if;
     end if;
     if tg_op = ''DELETE''then
          maxlen:= delCount(ct.category);
          return ct;
     end if;

end;
' language 'plpgsql';
create trigger trigger_update_articles before insert or delete
on articles for each row execute procedure updateCat();
create trigger trigger_update_media before  insert or delete
on media for each row execute procedure updateCat();
create trigger trigger_update_links before insert or delete
on links for each row execute procedure updateCat();
 
 
 

> create function updateCat() returns opaque as '
> declare
>     rec     record;
>     rename new to cat;
>     rename old to ct;
>     maxlen integer;
> 
> begin
>      if tg_op = ''INSERT''  and cat.category is null then
>           raise exception ''You are missing entry for category field'';
> 
>      else
>           if cat.display then
>                maxlen:= addCount(cat.category);
>                return cat;
>           else
>                return cat;
>           end if;
>      end if;
>      if tg_op = ''DELETE''then
>           maxlen:= delCount(ct.category);
>           return ct;
>      end if;
> 
> end;
> ' language 'plpgsql';

I think you want your if blocks to be more like:
if tg_op = ''INSERT''if cat.category is null (raise exception)else (do insert stuff)end if
end if
if tg_op = ''DELETE''(do delete stuff)
end if

I think you're currently going to get into the 
insert stuff on both inserts where it isn't
null and deletes (since in both cases the
and will be false).

On Mon, 26 Mar 2001, Stephan Szabo wrote:

> 
> > create function updateCat() returns opaque as '
> > declare
> >     rec     record;
> >     rename new to cat;
> >     rename old to ct;
> >     maxlen integer;
> > 
> > begin
> >      if tg_op = ''INSERT''  and cat.category is null then
> >           raise exception ''You are missing entry for category field'';
> > 
> >      else
> >           if cat.display then
> >                maxlen:= addCount(cat.category);
> >                return cat;
> >           else
> >                return cat;
> >           end if;
> >      end if;
> >      if tg_op = ''DELETE''then
> >           maxlen:= delCount(ct.category);
> >           return ct;
> >      end if;
> > 
> > end;
> > ' language 'plpgsql';
> 
> I think you want your if blocks to be more like:
> if tg_op = ''INSERT''
>  if cat.category is null
>   (raise exception)
>  else
>   (do insert stuff)
>  end if
> end if
> if tg_op = ''DELETE''
>  (do delete stuff)
> end if
> 
> I think you're currently going to get into the 
> insert stuff on both inserts where it isn't
> null and deletes (since in both cases the
> and will be false).

You probably want to handle UPDATEs as well.. make sure you plan for this
too.