Skip site navigation (1) Skip section navigation (2)

problem with sequence number using a trigger

From: MITCHELL CIFUENTES <mitchell(dot)cifuentes(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: problem with sequence number using a trigger
Date: 2009-06-17 21:07:54
Message-ID: f3f9e90f0906171407jadf7515wbe9d72cb2e000f25@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
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;

Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2009-06-17 22:40:43
Subject: Re: problem with sequence number using a trigger
Previous:From: John DeSoiDate: 2009-06-17 18:33:42
Subject: Re: implicit rollback?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group