Search Postgresql Archives

trigger question

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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!




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux