Re: Delete triggers

> "Mathew Frank" <mathewfrank(at)qushi(dot)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)