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

Rick Casey <rick.casey@xxxxxxxxxxxx> · Mon, 28 Feb 2005 09:23:30 -0700

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.casey@xxxxxxxxxxxx
303.735.3518

Sven Willenberger wrote:

Rick.Casey@xxxxxxxxxxxx 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.casey@xxxxxxxxxxxx> 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

---------------------------(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