Re: Delete triggers

> "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)