I have a problem with the trigger to reduce stock. The trigger compiles me and everything but how much I insert data into my table detalle_venta, in the product table it does not reduce the amount.
These are my tables:
create table producto
(
proId int primary key not null,
prodNombre varchar(40),
prodCant int,
prodPrecio decimal(7,2)
);
commit;
create table detalle_venta
(
dvId int primary key not null,
proId int,
foreign key(proId) references producto(proId),
venId int,
foreign key(venId) references venta(venId),
dvCantidad int,
dvPrecioart decimal(7,2),
dvTotal decimal(7,2)
);
commit;
create trigger ACTUALIZAR_STOCK_TRG
after insert or delete or update
on DETALLE_VENTA
for each row
begin
if(inserting or updating)
then
UPDATE PRODUCTO
set PRODCANT = PRODCANT - :new.dvcantidad
where PROID = :new.DVID;
end if;
if(inserting or deleting)
then
update PRODUCTO
set PRODCANT = PRODCANT + :new.dvcantidad
where PROID = :old.DVID;
end if;
end;