Re: basic trigger using OLD not working?

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
>