469,610 Members | 2,012 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,610 developers. It's quick & easy.

INSERT BEFORE Trigger

Anytime I create an INSERT BEFORE trigger that includes a query on the
same table as the trigger is assigned, the insert does not happen
without error. I get 'INSERT 0 0'. It is like the query loses the insert
information, is this something that can't be done?

DECLARE
checkit record;
BEGIN
SELECT INTO checkit MAX(public.tblhudunits.sort_order) AS maximum,
MIN(public.tblhudunits.sort_order) AS minimum FROM public.tblhudunits
WHERE (public.tblhudunits.hud_building_id = NEW.hud_building_id);
IF FOUND THEN
IF (NEW.sort_order >= checkit.minimum AND NEW.sort_order <=
checkit.maximum) THEN
RAISE EXCEPTION 'Sort Order cannot be between % and %',
checkit.minimum, checkit.maximum;
EXIT;
END IF;
END IF;
RETURN NULL;
END;

--
Robert
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #1
2 1795

On Sat, 19 Jun 2004, Robert Fitzpatrick wrote:
Anytime I create an INSERT BEFORE trigger that includes a query on the
same table as the trigger is assigned, the insert does not happen
without error. I get 'INSERT 0 0'. It is like the query loses the insert
information, is this something that can't be done?

DECLARE
checkit record;
BEGIN
SELECT INTO checkit MAX(public.tblhudunits.sort_order) AS maximum,
MIN(public.tblhudunits.sort_order) AS minimum FROM public.tblhudunits
WHERE (public.tblhudunits.hud_building_id = NEW.hud_building_id);
IF FOUND THEN
IF (NEW.sort_order >= checkit.minimum AND NEW.sort_order <=
checkit.maximum) THEN
RAISE EXCEPTION 'Sort Order cannot be between % and %',
checkit.minimum, checkit.maximum;
EXIT;
END IF;
END IF;
RETURN NULL;


You do not want to return NULL from a before trigger generally. Returning
NULL effectively means suppress this operation. I think you probably want
RETURN NEW;
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #2

On Sat, 19 Jun 2004, Robert Fitzpatrick wrote:
Anytime I create an INSERT BEFORE trigger that includes a query on the
same table as the trigger is assigned, the insert does not happen
without error. I get 'INSERT 0 0'. It is like the query loses the insert
information, is this something that can't be done?

DECLARE
checkit record;
BEGIN
SELECT INTO checkit MAX(public.tblhudunits.sort_order) AS maximum,
MIN(public.tblhudunits.sort_order) AS minimum FROM public.tblhudunits
WHERE (public.tblhudunits.hud_building_id = NEW.hud_building_id);
IF FOUND THEN
IF (NEW.sort_order >= checkit.minimum AND NEW.sort_order <=
checkit.maximum) THEN
RAISE EXCEPTION 'Sort Order cannot be between % and %',
checkit.minimum, checkit.maximum;
EXIT;
END IF;
END IF;
RETURN NULL;


You do not want to return NULL from a before trigger generally. Returning
NULL effectively means suppress this operation. I think you probably want
RETURN NEW;
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

9 posts views Thread by Martin | last post: by
2 posts views Thread by gimme_this_gimme_that | last post: by
2 posts views Thread by lenygold via DBMonster.com | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.