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

Re: basic trigger using OLD not working?

From: elein(at)varlena(dot)com (elein)
To: Rick(dot)Casey(at)colorado(dot)edu
Cc: Ian Harding <iharding(at)tpchd(dot)org>, pgsql-general(at)postgresql(dot)org
Subject: Re: basic trigger using OLD not working?
Date: 2005-02-26 02:08:57
Message-ID: 20050226020857.GH10213@varlena.com (view raw or flat)
Thread:
Lists: pgsql-general
Try creating the trigger on BEFORE DELETE.

--elein

On Fri, Feb 25, 2005 at 05:14:18PM -0700, Rick(dot)Casey(at)colorado(dot)edu wrote:
> Yes, thank you, I corrected my function from statement level to row level.
> This did get rid of the error message. However, I still get no output from
> an OLD variable that should contain data: see the test variable in the
> simple case below.
> 
> How else can I test OLD variables? This is the simplest test case I can
> think of. Any suggestions would be appreciated!
> 
> Thanks,
> Rick
> 
> > I think you have created a statement level trigger (If they existed in
> > 7.4.7...)  by not including FOR EACH ROW in your create statement.  In
> > statement level triggers, there is no OLD or NEW.
> >
> >>>> Rick Casey <rick(dot)casey(at)colorado(dot)edu> 02/24/05 1:22 PM >>>
> > Hello all,
> >
> > I am trying to a simple thing: create a log history of deletes, and
> > updates; but which I am having trouble getting to work in PG 7.4.7
> > (under Debian Linux 2.6.8).
> >
> > I have reduced my code to the following trivial case:
> >
> > Here is the code that creates the delete trigger:
> > create trigger PEDIGREES_hist_del_trig
> > AFTER DELETE
> > on PEDIGREES
> > EXECUTE PROCEDURE logPedigreesDel();
> >
> >
> > Here is the trigger code: (famindid is an integer field in the Pedigrees
> >
> > table):
> >
> > CREATE OR REPLACE FUNCTION logPedigreesDel() RETURNS TRIGGER AS '
> > DECLARE
> >     test integer;
> > begin
> >     test := OLD.famindid;
> >     RAISE EXCEPTION ''OLD.famindid = '', test;
> >     return OLD;
> > end;
> > ' LANGUAGE plpgsql;
> >
> >
> > Here is the error message returned:
> > psql:testphdtrig.sql:1: ERROR:  record "old" is not assigned yet
> > DETAIL:  The tuple structure of a not-yet-assigned record is
> > indeterminate.
> > CONTEXT:  PL/pgSQL function "logpedigreesdel" line 4 at assignment
> >
> > Would *really appreciate* any suggestions! This could help us decide
> > whether to PostGres for a major project...
> >
> > thanks --rick
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 8: explain analyze is your friend
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> >     (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
> >
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match
> 

In response to

pgsql-general by date

Next:From: Tzahi FadidaDate: 2005-02-26 02:17:46
Subject: SPI vs low level functions.
Previous:From: eleinDate: 2005-02-26 02:06:25
Subject: Re: Newbie: help with FUNCTION

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