Thread: problem with sequence number using a trigger
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;
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;
MITCHELL CIFUENTES <mitchell.cifuentes@gmail.com> writes: > 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??. Well, what you *really* ought to do is disabuse yourself of the illusion that the stock_id sequence must be gap-free. You are not going to be able to prevent insertion failures from leaving gaps. (Or at least not without a whole lot more pain than it's probably worth --- try searching the archives for "gap-free sequence" if you insist on that.) What you could do in this particular case is not have the sequence value come from the column's default expression, but let the trigger fill it in, after it's decided everything is okay. That will fix the problem as you describe it. What it won't fix is gaps caused by insertions that fail somewhere past the trigger stage (for example, due to a unique-index violation, or an error in a later trigger, or running out of disk space). regards, tom lane
Hello Tom,
Thanks for the answer. I've learning databases and postgresql for a month.
It is good to know that there is nothing wrong with the trigger and that the gap
in the sequence is something normal.
Thanks again.
regards
Mitchell
Thanks for the answer. I've learning databases and postgresql for a month.
It is good to know that there is nothing wrong with the trigger and that the gap
in the sequence is something normal.
Thanks again.
regards
Mitchell
2009/6/17 Tom Lane <tgl@sss.pgh.pa.us>
MITCHELL CIFUENTES <mitchell.cifuentes@gmail.com> writes:Well, what you *really* ought to do is disabuse yourself of the illusion
> 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??.
that the stock_id sequence must be gap-free. You are not going to be
able to prevent insertion failures from leaving gaps. (Or at least not
without a whole lot more pain than it's probably worth --- try searching
the archives for "gap-free sequence" if you insist on that.)
What you could do in this particular case is not have the sequence value
come from the column's default expression, but let the trigger fill it
in, after it's decided everything is okay. That will fix the problem
as you describe it. What it won't fix is gaps caused by insertions that
fail somewhere past the trigger stage (for example, due to a
unique-index violation, or an error in a later trigger, or running out
of disk space).
regards, tom lane
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.