Thread: Preventing Deletions with triggers

I would like to have a trigger that fires whenever a delete is attempted. 
The trigger should update the status field of the tupple (to remove the 
tupple form the active set of tupples).


Here is what I got:

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

create trigger person_fake_delete
before delete on person
for each row EXECUTE PROCEDURE
person_fake_delete();

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

Thanks,    Jeff Post

O kyrios Jeff Post egrapse stis May 19, 2004 :

> I would like to have a trigger that fires whenever a delete is attempted. 
> The trigger should update the status field of the tupple (to remove the 
> tupple form the active set of tupples).
> 

A rule would be more appropriate for this task.

> 
> 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';
> 
> create trigger person_fake_delete
> before delete on person
> for each row EXECUTE PROCEDURE
> person_fake_delete();
> 
> This however does not work.  the tupple is still deleted from the 
> table.    Any Ideas?
> 
> Thanks,
>      Jeff Post
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> 

-- 
-Achilleus

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
WHEREprimary_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.

Jeff Post <POSTJL@milwaukee.k12.wi.us> writes:
> 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';

> create trigger person_fake_delete
> before delete on person
> for each row EXECUTE PROCEDURE
> person_fake_delete();

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

It works for me, in the sense that returning NULL prevents the
deletion.  However that assignment to OLD is a no-op: you can't change
the tuple that way.  You'd have to do something like
UPDATE person SET status = 1 WHERE key = OLD.key;

("key" being whatever your primary key for the table is)
        regards, tom lane