Triggers failing from 7.0.2 to 7.1.3

From: Danny Aldham <danny(at)lennon(dot)postino(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Triggers failing from 7.0.2 to 7.1.3
Date: 2001-10-28 22:39:17
Message-ID: 200110282239.f9SMdHX00718@lennon.postino.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice


I have a postgresql application that relies on triggers to function.
I have migrated from 7.0.2 to 7.1.3 and the triggers are failing.
I have also tried 7.1.2 and they also fail there.
The application fails on updates to the package table. If I remove the
triggers, the application runs, but of course I do not get the
updates that should come from the trigger functions.

Below I have included the pg_dump for the table that was working.

Danny Aldham
------------------

-- $Revision: 1.1.1.1 $
-- $Author: falstaff $
-- $Date: 2001/07/28 19:22:02 $
-- $Id: crpackage.sql,v 1.1.1.1 2001/07/28 19:22:02 falstaff Exp $

create table package (
pack_code varchar,
status varchar,
config_id int4,
pack_id varchar,
asset_tag varchar,
mac varchar,
ipaddr varchar,
date1 date,
date2 date,
num_prod int4,
remarks varchar,
p_order varchar,
batch varchar,
cap_lease_num varchar,
cap_cost float,
exp_cost float,
tot_cost float,
rev int4,
revdate timestamp,
revby int4
);
create index pack_indx on package using btree (config_id);
create index pmac_indx on package using btree (mac);

create table mupack (
xfop varchar,
xfstat varchar,
xftm timestamp
) inherits (package);

create function inspack() returns opaque as '
begin
NEW.revdate := ''now'';
NEW.rev := 1;
insert into mupack values (NEW.pack_code, NEW.status, NEW.config_id,
NEW.pack_id, NEW.asset_tag, NEW.mac, NEW.ipaddr, NEW.date1, NEW.date2,
NEW.num_prod, NEW.remarks, NEW.p_order, NEW.batch, NEW.cap_lease_num,
NEW.cap_cost, NEW.exp_cost, NEW.tot_cost, NEW.rev, NEW.revdate,
NEW.revby, ''INS'', ''CRE'', ''now'' );
return NEW;
end;
' LANGUAGE 'plpgsql';
create function updpack() returns opaque as '
begin
NEW.revdate := ''now'';
NEW.rev := OLD.rev + 1;
insert into mupack values (OLD.pack_code, OLD.status, OLD.config_id,
OLD.pack_id, OLD.asset_tag, OLD.mac, OLD.ipaddr, OLD.date1, OLD.date2,
OLD.num_prod, OLD.remarks, OLD.p_order, OLD.batch, OLD.cap_lease_num,
OLD.cap_cost, OLD.exp_cost, OLD.tot_cost, OLD.rev, OLD.revdate,
OLD.revby, ''UPD'', ''CRE'', ''now'' );
return NEW;
end;
' LANGUAGE 'plpgsql';
create function delpack() returns opaque as '
begin
insert into mupack values (OLD.pack_code, OLD.status, OLD.config_id,
OLD.pack_id, OLD.asset_tag, OLD.mac, OLD.ipaddr, OLD.date1, OLD.date2,
OLD.num_prod, OLD.remarks, OLD.p_order, OLD.batch, OLD.cap_lease_num,
OLD.cap_cost, OLD.exp_cost, OLD.tot_cost, OLD.rev, OLD.revdate,
OLD.revby, ''DEL'', ''CRE'', ''now'' );
return OLD;
end;
' LANGUAGE 'plpgsql';

--create trigger ins_pck before insert on package for each row execute
--procedure inspack();
create trigger upd_pck before update on package for each row execute
procedure updpack();
create trigger del_pck before delete on package for each row execute
procedure delpack();

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Roth, Michael J. 2001-10-29 09:26:24 Re: Too much postmaster prozesses / CPU near 100%
Previous Message The Cadaver 2001-10-28 15:34:37 IDE