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 Hours googling and the best I could find was one of the main developers (Bruce?? 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