trigger question
| От | Furesz Peter |
|---|---|
| Тема | trigger question |
| Дата | |
| Msg-id | 001001c73994$c72ee1f0$0202fea9@bixerverintel обсуждение исходный текст |
| Ответы |
Re: trigger question
Re: trigger question |
| Список | 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 по дате отправления: