Thread: Delete triggers

I have had a lot of trouble getting a DELETE trigger to do nothing (ie let =
the delete operation occur instead of cancelling it, as required)

The documentation on this is very thin on the ground - I`ve just spend 4 Ho=
urs googling and the best I could find was one of the main developers (Bruc=
e?? sorry - too long ago) replying to an email in 2001.    Which was to NOT=
 cancel the delete operation, you need to return NEW or OLD.

 - If I return NULL the operation is cancelled.  ("DELETE 0")
 - If I return NEW the operation is cancelled.  ("DELETE 0") Now NEW is not=
 set for a delete (because it would make no sense) so I am thinking this is=
 the same as returning NULL
 - If I return OLD the operation is cancelled ("DELETE 0").

I am using the 7.2 version.


To me this is either a bug in the system, or a 'bug' in the documentation. =
  Look forward to hearing from you.

Cheers,
Mathew

"Mathew Frank" <mathewfrank@qushi.com> writes:
> The documentation on this is very thin on the ground - I`ve just spend 4 Ho=
> urs googling and the best I could find was one of the main developers (Bruc=
> e?? sorry - too long ago) replying to an email in 2001.    Which was to NOT=
>  cancel the delete operation, you need to return NEW or OLD.

There is no NEW row in a delete trigger.  RETURN OLD should work.

>  - If I return OLD the operation is cancelled ("DELETE 0").

I suspect pilot error.

            regards, tom lane

> "Mathew Frank" <mathewfrank@qushi.com> writes:
> > The documentation on this is very thin on the ground - I`ve just spend 4
Ho=
> > urs googling and the best I could find was one of the main developers
(Bruc=
> > e?? sorry - too long ago) replying to an email in 2001.    Which was to
NOT=
> >  cancel the delete operation, you need to return NEW or OLD.
>
> There is no NEW row in a delete trigger.

Actually I think I just said that ;-)

> RETURN OLD should work.

> >  - If I return OLD the operation is cancelled ("DELETE 0").
>
> I suspect pilot error.

Fair enough.   Here is my test code (apologies - should have sent it the
first time):
-----
    CREATE FUNCTION "trg_test"() RETURNS "opaque" AS '
    DECLARE
        is_closed bool;
        result record;
    BEGIN
        is_closed := false;
        IF is_closed THEN
            RAISE NOTICE ''Operation Cancelled: Month has been closed'';
            return NULL;
        ELSE
            IF ( TG_OP = ''DELETE'' ) THEN
                RAISE NOTICE ''Operation NOT cancelled'';
                return OLD;
            ELSE
                RAISE NOTICE ''Operation NOT cancelled - NOT delete'';
                return NEW;
            END IF;
        END IF;
    END;
    ' LANGUAGE 'plpgsql';
    drop trigger "protectperiod_montly_figures" on monthly_figures;
    CREATE TRIGGER "protectperiod_montly_figures" BEFORE INSERT OR DELETE OR
UPDATE ON "monthly_figures" FOR EACH ROW EXECUTE PROCEDURE trg_test();
-----

and the result of a delete query:
    NOTICE:  Operation NOT cancelled
    ERROR:  fmgr_info: function 1455898: cache lookup failed

(I was sure I was getting a 'Delete 0' but since my computer has crashed
since - I`m not sure.   Maybe I was getting the above)
Now before you ask - the trigger was created after the trigger function.

I don`t see what can be wrong with the above - my code does not touch OLD -
merely returns it.

Cheers,
Mathew
ps - if you think I should move this to users I will, though at this point I
don`t see a code issue (I hope you do though)

"Mathew Frank" <mathewfrank@qushi.com> writes:
> and the result of a delete query:
>     NOTICE:  Operation NOT cancelled
>     ERROR:  fmgr_info: function 1455898: cache lookup failed

> (I was sure I was getting a 'Delete 0' but since my computer has crashed
> since - I`m not sure.   Maybe I was getting the above)
> Now before you ask - the trigger was created after the trigger function.

That trigger, maybe, but the fmgr_info failure clearly indicates a
dangling function reference somewhere.  Maybe you have other triggers
defined on this table?

Could I interest you in upgrading to 7.3?  It's supposed to prevent you
from dropping things that still have references to 'em ...

            regards, tom lane