To: Jeff Post <POSTJL@milwaukee.k12.wi.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: Preventing Deletions with triggers
References: <6.1.0.6.0.20040519090934.025963a8@mail.milwaukee.k12.wi.us>
In-Reply-To: <6.1.0.6.0.20040519090934.025963a8@mail.milwaukee.k12.wi.us>
From: Manuel Sugawara <masm@fciencias.unam.mx>
User-Name: Manuel Sugawara
User-Mail-Address: masm@fciencias.unam.mx
Date: 21 May 2004 10:01:20 -0500
Message-ID: <m3fz9t4yf3.fsf@conexa.fciencias.unam.mx>
Lines: 33
User-Agent: Gnus/5.0808 (Gnus v5.8.8) XEmacs/21.4 (Portable Code)
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

Jeff Post <POSTJL@milwaukee.k12.wi.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.