Re: trigger: NEW/OLD-error or nothing happens

Knut Suebert wrote:
> Joe Conway schrieb:
>
> > OLD does not make sense on an insert because there is no "OLD" data for a
> > brand new row. Similarly, there is no "NEW" data during a delete. See
> > http://www.postgresql.org/idocs/index.php?plpgsql-trigger.html
>
> Makes sense, thanks. And at the bottom of that page is a nice hint
> regarding the trigger BEFORE/AFTER insert.
>
> So I changed to BEFORE -- but still nothing happens
>
> An exception for debugging:
>
>    create function nac_viceversa() returns opaque as'
>    declare
>         x int4;
>    begin
>         x := nacmin(NEW.sport,NEW.dport)
>         NEW.minport := x;
>         NEW.maxport := nacmax(NEW.sport,NEW.dport);
>         raise exception ''%'',x;
>    return NEW;
>    end;
>    'language 'plpgsql';
>
>    create trigger nac_update before insert or update on traf
>    for each row execute procedure nac_viceversa();
>
> the calculation works...
>
>    ERROR:  110
>    ERROR:  110
>    ERROR:  110
>
> ... but is not written into the row -- even after removing the exception ;-)
>
>     id   | ... | device | minport | maxport
>    12151 | ... | eth0   |         |
>    12152 | ... | eth0   |         |
>
> Am I stupid?

    You  can use RAISE NOTICE to have *debugging* without rolling
    back the transaction. Maybe it'd help to add more RAISE's  to
    see the values of NEW.sport and NEW.dport as well?

    And you don't need the eXtra variable. NEW.attrib should work
    well in the RAISE.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com