Thread: 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

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;