trigger howto question

Поиск
Список
Период
Сортировка
От Furesz Peter
Тема trigger howto question
Дата
Msg-id 005f01c73996$1121e4a0$0202fea9@bixerverintel
обсуждение исходный текст
Ответы Re: trigger howto question  (Richard Huxton <dev@archonet.com>)
Список pgsql-general
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!


В списке pgsql-general по дате отправления:

Предыдущее
От: "Merlin Moncure"
Дата:
Сообщение: Re: Elegant copy of a row using PL
Следующее
От: Bruno Wolff III
Дата:
Сообщение: Re: Performance with very large tables