FAQ
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;

Search Discussions

  • Tom Lane at Jun 17, 2009 at 10:40 pm

    MITCHELL CIFUENTES 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
  • MITCHELL CIFUENTES at Jun 18, 2009 at 2:46 am
    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

    2009/6/17 Tom Lane <tgl@sss.pgh.pa.us>
    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
  • Lennin Caro at Jun 18, 2009 at 3:11 pm
    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.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedJun 17, '09 at 9:07p
activeJun 18, '09 at 3:11p
posts4
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase