pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Najm Hashmi <najm@mondo-live.com>
To: pgsql <pgsql-sql@postgresql.org>
Subject: is it me or trigger side effects
Date: Mon, 26 Mar 2001 10:41:15 -0500
Message-ID: <3ABF631B.18B66558@mondo-live.com> (raw)

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

view thread (3+ messages)  latest in thread

Message-ID: <3ABF631B.18B66558@mondo-live.com>
Permalink:  ../3ABF631B.18B66558@mondo-live.com/
Also on:    postgresql.org/message-id/3ABF631B.18B66558@mondo-live.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: najm@mondo-live.com
  Subject: Re: is it me or trigger side effects
  In-Reply-To: <3ABF631B.18B66558@mondo-live.com>
  References: <3ABF631B.18B66558@mondo-live.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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