INSTEAD OF DELETE trigger (Postgresql)

database, function, postgresql, sql, triggers

Solution

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;

Problem

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?

Original source