current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
stack overflow careers
Sign up ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free.
up vote 0 down vote favorite

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?

share|improve this question
1  
Are you trying to define trigger on a view or on a table? – Igor Romanchenko Aug 13 '13 at 17:23
1  
"Didn't work" ? Exact behaviour, error message, etc? – Craig Ringer Aug 14 '13 at 1:11

2 Answers 2

up vote 3 down vote

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;
share|improve this answer
    
This trigger will block DELETE but wont change the row that is deleated. – Igor Romanchenko Aug 13 '13 at 17:21
    
@Igor: you're right, edited to use UPDATE instead, tested and it appears to work – Daniel Vérité Aug 13 '13 at 17:28
up vote 0 down vote

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.