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

Re: basic trigger using OLD not working? >>THANKS!

From: Rick Casey <rick(dot)casey(at)colorado(dot)edu>
To: Sven Willenberger <sven(at)dmv(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: basic trigger using OLD not working? >>THANKS!
Date: 2005-02-28 16:23:30
Message-ID: 42234582.8060706@colorado.edu (view raw or flat)
Thread:
Lists: pgsql-general
Hey, thanks to everyone who replied to my questions: problem solved!

I needed to: 1) do BEFORE DELETE to see the OLD variables, and 2) use a 
placeholder in my format string in the RAISE EXCEPTION/NOTICE statement.

Open source newsgroups rock!

--rick

Rick Casey, Research Associate
Institute for Behavioral Genetics
rick(dot)casey(at)colorado(dot)edu
303.735.3518



Sven Willenberger wrote:

>
>
> Rick(dot)Casey(at)colorado(dot)edu presumably uttered the following on 02/25/05 
> 19:14:
>
>> 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;
>>>
>>>
>>
>
> Need a place holder for your variable in your RAISE expression (like a 
> printf syntax):
>
>     RAISE EXCEPTION ''OLD.famindid = %'', test;
>
> btw, if you just want to see the variable without having your function 
> bail on you, try RAISE NOTICE ''OLD.famindid = %'', test;
>
> Sven


In response to

pgsql-general by date

Next:From: Cristian PrietoDate: 2005-02-28 16:38:05
Subject: Re: GUI
Previous:From: Richard HuxtonDate: 2005-02-28 15:43:37
Subject: Re: GUI

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