INSTEAD OF DELETE trigger (Postgresql)

Question

I would like to disable the DELETE statement on a table. What I need to do is a SET a field value instead of removing the respective record.

So far I have tried the following:

CREATE TRIGGER delete_trg
INSTEAD OF DELETE
ON schema.tbl
FOR EACH ROW
EXECUTE PROCEDURE schema.tbl_delete_fn();

My schema.tbl_delete_fn() function is as follows:

CREATE OR REPLACE FUNCTION schema.tbl_delete_fn()
  RETURNS trigger AS
BEGIN
  NEW.deleted := true;

  RETURN NEW;
END;

So far this doesn't seem to work... any ideas?

Answer 1

You want a BEFORE DELETE trigger whose function returns NULL and the row variable is OLD, not NEW.

CREATE TRIGGER delete_trg
BEFORE DELETE
ON schema.tbl
FOR EACH ROW
EXECUTE PROCEDURE schema.tbl_delete_fn();

CREATE OR REPLACE FUNCTION schema.tbl_delete_fn()
  RETURNS trigger AS '
BEGIN
  UPDATE schema.tbl SET deleted=true WHERE ctid=OLD.ctid;
  RETURN NULL;
END; ' language plpgsql;