Обсуждение: Trigger problem
Hi guys Im tryint to make a trigger that marks a tuble as changed whenever someone has updated it my table looks something like this create table myTable ( ... changed boolean; ) now ive been working on a trigger and a sp that looks like this, but it doesnt work... create function myFunction returns trigger as ' begin new.changed = true; return new; end; ' language 'plpgsql'; create trigger myTrigger after update on lektioner for each row execute procedure myFunction(); the code compiles, runs, and doesnt whine about anything, but nothing changes... any ideas?? - Kasper
kasper wrote: > Hi guys > > Im tryint to make a trigger that marks a tuble as changed whenever someone > has updated it > > my table looks something like this > > create table myTable ( > ... > changed boolean; > ) > > now ive been working on a trigger and a sp that looks like this, but it > doesnt work... > > create function myFunction returns trigger as ' > begin > new.changed = true; The line above is using the SQL equaliy opperator, you want the assignment operator: := as in new.changed := true; > return new; > end; > ' language 'plpgsql'; > > create trigger myTrigger > after update on lektioner > for each row > execute procedure myFunction(); > > > the code compiles, runs, and doesnt whine about anything, but nothing > changes... > > any ideas?? > > - Kasper -miker
On Tue, 8 Jun 2004, kasper wrote: > Im tryint to make a trigger that marks a tuble as changed whenever someone > has updated it > > my table looks something like this > > create table myTable ( > ... > changed boolean; > ) > > now ive been working on a trigger and a sp that looks like this, but it > doesnt work... > > create function myFunction returns trigger as ' > begin > new.changed = true; > return new; > end; > ' language 'plpgsql'; > > create trigger myTrigger > after update on lektioner > for each row > execute procedure myFunction(); You want a before update trigger if you want to update the new row like that.
On 6/8/2004 2:57 PM, Mike Rylander wrote: > kasper wrote: > >> Hi guys >> >> Im tryint to make a trigger that marks a tuble as changed whenever someone >> has updated it >> >> my table looks something like this >> >> create table myTable ( >> ... >> changed boolean; >> ) >> >> now ive been working on a trigger and a sp that looks like this, but it >> doesnt work... >> >> create function myFunction returns trigger as ' >> begin >> new.changed = true; > > The line above is using the SQL equaliy opperator, you want the assignment > operator: > > := > > as in > > new.changed := true; PL/pgSQL accepts both. What's wrong is that it's an AFTER trigger, which is fired AFTER the new row is already stored on disk and thus cannot change it any more. Jan > >> return new; >> end; >> ' language 'plpgsql'; >> >> create trigger myTrigger >> after update on lektioner >> for each row >> execute procedure myFunction(); >> >> >> the code compiles, runs, and doesnt whine about anything, but nothing >> changes... >> >> any ideas?? >> >> - Kasper > > -miker > > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #