INSTEAD OF DELETE trigger (Postgresql)
You want a BEFORE DELETE
trigger whose function returns NULL
and the row variable is OLD
, not NEW
.
CREATE TRIGGER delete_trgBEFORE DELETEON schema.tblFOR EACH ROWEXECUTE 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;
Or...
CREATE RULE delete_rule AS ON DELETE TO schema.tblDO INSTEAD NOTHING;
Pros: Clearer, no code is called for each row visited, and no SP required.
Cons: Less standard than the trigger solution.