Re: trigger question

["Lenorovitz, Joel" <Joel.Lenorovitz@xxxxxxxx>]

 I ran into a similar problem and the solution I came up with (which
admittedly feels like a kludge) was to temporarily disable the triggers
on the table being modified while an update was made and then
re-enabling them immediately after the update.  I am sure there is
potential for problems with this approach and I too would like to find a
better one, but right now this works as I am still in the development
stage and not dealing with any critical data.  Anyway, this is
essentially the code I use (applied now to table foobar) and maybe
sharing it will help inspire a better solution.  Please keep the list
and me informed if you have one....Thanks, Joel

Code excerpt from within on delete trigger function for foobar.....

-- Disable triggers on table foobar
UPDATE pg_catalog.pg_class SET reltriggers = 0 WHERE oid =
'foobar'::pg_catalog.regclass';
-- Perform update
UPDATE foobar SET is_deleted=TRUE WHERE foobar_id=OLD.foobar;
-- Re-enable triggers on table foobar
UPDATE pg_catalog.pg_class SET reltriggers = 1 WHERE oid =
'foobar'::pg_catalog.regclass';


-----Original Message-----
From: Furesz Peter [mailto:fureszpeter@xxxxxx] 
Sent: Tuesday, January 16, 2007 10:36 AM
To: postgres levlista
Subject: trigger question

Hello,

I have a table named foobar  and I don't want to allow from DELETE or
UPDATE its rows.

I have a table as described below:
foobar(foobar_id, value, is_deleted);

I don't want to allow directly delete or modify the table's rows. I plan
to make an on before update or delete trigger and on delete action I
update the actual row is_deleted flag, on UPDATE action I also update
the is_deleted flag and I insert a new row with the new values.
Everything is ok, but when I capture the delete action I am execute an
update what triggering the trigger again and I got an unwanted row.

CREATE TRIGGER "tr_foobar" BEFORE UPDATE OR DELETE  ON "public"."foobar"
FOR EACH ROW EXECUTE PROCEDURE "public"."tr_foobar_func"();

BEGIN
  IF TG_OP='DELETE' THEN
     UPDATE foobar SET is_deleted=TRUE WHERE foobar_id=OLD.foobar;
     RETURN NULL;
  ELSEIF TG_OP='UPDATE' THEN
     INSERT INTO foobar(value) VALUES(NEW.value);
     NEW.is_deleted=TRUE;
     NEW.value=OLD.value;
     RETURN NEW;
  END IF;
END;

What is the right solution for this situation. Thank you for the help!