Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-novice by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group