From: MITCHELL CIFUENTES <mitchell.cifuentes@gmail.com>
> Subject: [NOVICE] problem with sequence number using a trigger
> To: pgsql-novice@postgresql.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.