Re: trigger howto question

Furesz Peter wrote:
> 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.

The trick is to remember that only the trigger can be setting the
is_deleted flag, not other queries (or at least that's what I think you
want).

> 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
    ^^^^^^^^^^^^^^^^^^^^^^^^^^
   ELSEIF TG_OP='UPDATE' AND is_deleted=FALSE THEN
>     INSERT INTO foobar(value) VALUES(NEW.value);
>     NEW.is_deleted=TRUE;
>     NEW.value=OLD.value;
>     RETURN NEW;
>  END IF;
> END;

Does that do what you want?

--
   Richard Huxton
   Archonet Ltd