Re: problem with sequence number using a trigger

From: MITCHELL CIFUENTES <mitchell(dot)cifuentes(at)gmail(dot)com>
> Subject: [NOVICE] problem with sequence number using a trigger
> To: pgsql-novice(at)postgresql(dot)org
> Date: Wednesday, June 17, 2009, 9:07 PM
> Hello,
>
> I hope you can help with this. I have a BEFORE trigger that
> run every time an event insert o update occurs
> on a table called stock. It is working fine inserting the
> correct data on the table if the conditions are met,
>
> and inserting nothing if not.
> The problem is when it rejects the insert statement, the
> sequence number stock_id gets incremented
> even though nothing is inserted on the table. What can i do
> to stop stock_id from incrementing??.
>
>
> Mitchell
>
> p.d. This is the code.
>
> create function serie_trigger() returns trigger AS $$
> declare
>     pro_record    record;
> begin
>     select * into pro_record from producto
>     where producto_id = new.producto_id;
>
>     if (pro_record.seriado = true)
>     then
>         if (new.serie IS NOT NULL)
>         then
>             if (new.cantidad = 1)
>             then
>                 return new; -- new se usa como
> fuente para insert
>
>             else
>                 raise notice 'ERROR:
> Candidad debe ser 1, las series se pueden insertar solo una
> a la vez';
>                 return NULL;
>             end if;
>         else
>             raise notice 'ERROR: Debe ingresar
> una serie, el producto es seriado';
>
>             return NULL;
>         end if;           
>     else
>         if (new.serie IS NULL)
>         then
>             return new; -- new se usa como fuente
> para insert
>         else
>             raise notice 'ERROR: El producto
> es no seriado, no corresponde ingresar serie.';
>
>             return NULL;
>         end if;
>     end if;
> end;
> $$ language plpgsql;
>
>
>
>

when you try to insert data to a table whit serial the system take the next value and reserve this.