pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
is it me or trigger side effects
3+ messages / 3 participants
[nested] [flat]

* is it me or trigger side effects
@ 2001-03-26 15:41 Najm Hashmi <najm@mondo-live.com>
  2001-03-26 17:10 ` Re: is it me or trigger side effects Stephan Szabo <sszabo@megazone23.bigpanda.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Najm Hashmi @ 2001-03-26 15:41 UTC (permalink / raw)
  To: pgsql-sql

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();

Attachments:

najm.vcf (text/x-vcard, 183B) download begin:vcard n:Hashmi;Najm x-mozilla-html:FALSE org:Mondo-Live.com;www.flipr.com adr:;;;;;; version:2.1 email;internet:najm@mondo-live.com x-mozilla-cpt:;6144 fn:Najm Hashmi end:vcard
^ permalink raw reply [nested|flat] 3+ messages in thread

* Re: is it me or trigger side effects
  2001-03-26 15:41 is it me or trigger side effects Najm Hashmi <najm@mondo-live.com>
@ 2001-03-26 17:10 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
  2001-03-26 20:42   ` Re: is it me or trigger side effects Cedar Cox <cedarc@visionforisrael.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Stephan Szabo @ 2001-03-26 17:10 UTC (permalink / raw)
  To: Najm Hashmi <najm@mondo-live.com>; +Cc: pgsql-sql


> 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).





^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: is it me or trigger side effects
  2001-03-26 15:41 is it me or trigger side effects Najm Hashmi <najm@mondo-live.com>
  2001-03-26 17:10 ` Re: is it me or trigger side effects Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2001-03-26 20:42   ` Cedar Cox <cedarc@visionforisrael.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Cedar Cox @ 2001-03-26 20:42 UTC (permalink / raw)
  To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: Najm Hashmi <najm@mondo-live.com>; pgsql-sql


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.




^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2001-03-26 20:42 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-03-26 15:41 is it me or trigger side effects Najm Hashmi <najm@mondo-live.com>
2001-03-26 17:10 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-03-26 20:42   ` Cedar Cox <cedarc@visionforisrael.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox