Thread: trigger: NEW/OLD-error or nothing happens
Hello,
I'm trying to anaylze a net-acct-log.
so:
create table traf ( id serial primary key, time timestamp, proto int2,
sip inet, sport int4, dip inet, dport int4, size int4, device text,
minport int4, maxport int4);
minport is to group by the service, it (and maxport for completeness)
should be filled by
create function nacmin(int4,int4) returns int as'
begin
if $1<$2 then
return $1;
end if;
return $2;
end;
' language 'plpgsql';
create function nacmax(int4,int4) returns int as'
begin
if $1>$2 then
return $1;
end if;
return $2;
end;
' language 'plpgsql';
these functions work.
the automatic execution should be done by a function with a stupid name:
create function nac_viceversa() returns opaque as'
begin
NEW.minport := nacmin(OLD.sport,OLD.dport);
NEW.maxport := nacmax(OLD.sport,OLD.dport);
return NEW;
end;
'language 'plpgsql';
create trigger nac_update after insert or update on traf
for each row execute procedure nac_viceversa();
but than
ERROR: record old is unassigned yet
if i take NEW. instead of OLD.: no errors -- but minport and maxport
are empty after insert. (could it be that OLD makes no sense on
insert? I'd lie if I'd say that I understood the conecpt, but
databases are hard stuff for me even in my native language)
I searched documentation and archives[1], tried some tricks after
'DECLARE', tried with and without OLD and NEW, but all i get are
errors or nothinng happens.
Thanks a lot for your help,
Knut Suebert
[1] everything found was in -general or -hackers. So I decided to send
this to -general and not to -novice, sorry if i'm wrong.
> create function nac_viceversa() returns opaque as' > begin > NEW.minport := nacmin(OLD.sport,OLD.dport); > NEW.maxport := nacmax(OLD.sport,OLD.dport); > return NEW; > end; > 'language 'plpgsql'; > > create trigger nac_update after insert or update on traf > for each row execute procedure nac_viceversa(); > > but than > > ERROR: record old is unassigned yet > > if i take NEW. instead of OLD.: no errors -- but minport and maxport > are empty after insert. (could it be that OLD makes no sense on > insert? I'd lie if I'd say that I understood the conecpt, but > databases are hard stuff for me even in my native language) 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 NEW Data type RECORD; variable holding the new database row on INSERT/UPDATE operations on ROW level triggers. OLD Data type RECORD; variable holding the old database row on UPDATE/DELETE operations on ROW level triggers. Hope this helps, Joe
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? Knut Sübert
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
Jan Wieck schrieb:
> You can use RAISE NOTICE to have *debugging* without rolling
> back the transaction.
Nice, thnx |-)
> Maybe it'd help to add more RAISE's to
> see the values of NEW.sport and NEW.dport as well?
I tried and it works, I removed it and it still works. I really don't
know, where the difference compared to the first tries is, but anyway:
create function nac_viceversa() returns opaque as'
begin
NEW.minport := nacmin(NEW.sport,NEW.dport);
NEW.maxport := nacmax(NEW.sport,NEW.dport);
return NEW;
end;
'language 'plpgsql';
create trigger nac_update before insert or update on traf
for each row execute procedure nac_viceversa();
Bye,
Knut