CODEWITHSUNDEEP
sqldatabasefunctionpostgresqltriggers
Emanuele Mazzoni
Emanuele Mazzoni

Reputation: 716

INSTEAD OF DELETE trigger (Postgresql)

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?

Upvotes: 7

Views: 8578

Answers (2)

Brian.D.Myers
Brian.D.Myers

Reputation: 2518

Or...

CREATE RULE delete_rule 
AS ON DELETE TO schema.tbl
DO INSTEAD NOTHING;

Pros: Clearer, no code is called for each row visited, and no SP required.

Cons: Less standard than the trigger solution.

Upvotes: 6

Daniel Vérité
Daniel Vérité

Reputation: 61526

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;

Upvotes: 12

Related Questions