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?

Are you trying to define trigger on a view or on a table?

Ihor Romanchenko
– Ihor Romanchenko

2013-08-13 17:23:27 +00:00
Commented Aug 13, 2013 at 17:23 — Ihor Romanchenko
– Ihor Romanchenko, Commented Aug 13, 2013 at 17:23
"Didn't work" ? Exact behaviour, error message, etc?

Craig Ringer
– Craig Ringer

2013-08-14 01:11:53 +00:00
Commented Aug 14, 2013 at 1:11 — Craig Ringer
– Craig Ringer, Commented Aug 14, 2013 at 1:11

Daniel Vérité · Accepted Answer · 2013-08-13 17:27:55Z

12

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;

edited Aug 13, 2013 at 17:27

answered Aug 13, 2013 at 17:18

Daniel Vérité

62.3k16 gold badges134 silver badges160 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ihor Romanchenko Over a year ago

This trigger will block DELETE but wont change the row that is deleated.

Daniel Vérité Over a year ago

@Igor: you're right, edited to use UPDATE instead, tested and it appears to work

Brian.D.Myers · Accepted Answer · 2013-08-14 15:48:33Z

6

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.

edited Aug 14, 2013 at 15:48

answered Aug 13, 2013 at 17:39

Brian.D.Myers

2,5282 gold badges21 silver badges17 bronze badges

1 Comment

Matthieu Over a year ago

But it doesn't update the column of the "not deleted" row.

Collectives™ on Stack Overflow

INSTEAD OF DELETE trigger (Postgresql)

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related