Skip site navigation (1) Skip section navigation (2)

Re: Preventing Deletions with triggers

From: Manuel Sugawara <masm(at)fciencias(dot)unam(dot)mx>
To: Jeff Post <POSTJL(at)milwaukee(dot)k12(dot)wi(dot)us>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Preventing Deletions with triggers
Date: 2004-05-21 15:01:20
Message-ID: m3fz9t4yf3.fsf@conexa.fciencias.unam.mx (view raw or flat)
Thread:
Lists: pgsql-sql
Jeff Post <POSTJL(at)milwaukee(dot)k12(dot)wi(dot)us> writes:

> Here is what I got:
> 
> CREATE or replace FUNCTION person_fake_delete() RETURNS TRIGGER AS '
>      BEGIN
>          OLD.status := 1; -- This does the fake deletion
>          RETURN NULL;  -- I thought this would prevent the delete from
> actually happening.
> 
>      END;
> ' LANGUAGE 'plpgsql';

You need to explicitly update the row because changes to the OLD
reference will be lost, for instance:

CREATE or replace FUNCTION person_fake_delete() RETURNS TRIGGER AS '
     BEGIN
         UPDATE your_table SET status = 1 WHERE primary_key = OLD.primary_key;
         RETURN NULL;  -- I thought this would prevent the delete from actually happening.
     END;
' LANGUAGE 'plpgsql';

> This however does not work.  the tupple is still deleted from the table.
> Any Ideas?

Really?, The tuple is still deleted? There should be something that you are
not telling us (may be you are creating the trigger to run AFTER instead of
BEFORE). It have worked for me in each version since the trigger system
were added to postgres.

Regards,
Manuel.

In response to

pgsql-sql by date

Next:From: Tom LaneDate: 2004-05-21 15:05:28
Subject: Re: Problem with JOINS
Previous:From: Stephan SzaboDate: 2004-05-21 14:17:24
Subject: Re: Problem with JOINS

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group