problem with sequence number using a trigger
От | MITCHELL CIFUENTES |
---|---|
Тема | problem with sequence number using a trigger |
Дата | |
Msg-id | f3f9e90f0906171407jadf7515wbe9d72cb2e000f25@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: problem with sequence number using a trigger
|
Список | 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;
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;
В списке pgsql-novice по дате отправления: