Re: Preventing Deletions with triggers

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.