I have this trigger, but I want to make it count before adding to see if the data already exists in the table so I do not add it twice.
AS
begin
if (new.cantidad <= new.catidad_minima ) then
insert into comprar (nombre_producto ,cantidad,idcategoria)
values (old.nombre_producto,new.cantidad,NEW.id_categoria);
if (new.cantidad >= new.catidad_minima) then
delete from comprar a
where a.nombre_producto = new.nombre_producto;
end
In this case, you would define a PRIMARY KEY (PK) or a SINGLE INDEX (NDX) by one or several fields and let the Database perform checking.
That way you increase security and speed when inserting.
The Database itself will return an error when inserting if there is a duplicate, which you can capture and process from your application.
Greetings.
You should tell us what you want to tell. For this example I guess you want to know if there is a record before inserting the data in the buy table.
as
declare variable contador integer;
begin
if (new.cantidad <= new.catidad_minima) then
begin
/* Cuento cuantos registros hay en la tabla con ese producto */
contador = 0;
select count(*)
from comprar
where
nombre_producto = new.nombre_producto;
into :contador;
/* Si no hay ninguno inserto */
/* Si hay 1 lo sumo a la cantidad que hay */
if (contador < 1) then
insert into comprar (
nombre_producto, cantidad, idcategoria)
values (
old.nombre_producto, new.cantidad, new.id_categoria);
else
update comprar
set
cantidad = cantidad + new.cantidad
where
nombre_producto = old.nombre_producto;
end
if (new.cantidad >= new.catidad_minima) then
delete from comprar a
where
a.nombre_producto = new.nombre_producto;
end