Create proc spCompra (idprod int, idprov intm quant int, preu int)
as
Begin
if @@idprod <> 0 and @@idprov <> 0 and @@quant <> 0
Begin
Declare @@stock int
if exists (select * from producte where idprod = @@idprod) and exists (select * from proveidor where idprov = @@idprov)
Begin
select @@stock = (select quant from producte where idprod=@@idprod)
Begin transaction
insert into compra (idprod,idprov,quant,preu)
values (@@idprod,@@idprov,@@quant,@@preu)
update producte
set quant = @@stock + @@quant
where idprod = @@idprod
Commit transaction
print 'Compra registrada'
End
else
Begin
print 'Algun dels parametres no existeix'
End
End
Else
Begin
print 'No has introduit algun parametre'
End
End
CREATE TRIGGER tStock ON producte FOR UPDATE
AS
BEGIN
Declare @stock int
If UPDATE(quant)
BEGIN
Select @stock = quant From producte
If @stock <>
BEGIN
RAISERROR ('No hi ha stock suficient',16,-1)
ROLLBACK TRANSACTION
END
END
END
Create proc spVenta (idprod int, idclient int, quant int, preu int)
as
Begin
if @@idprod <> 0 and @@idclient <> 0 and @@quant <> 0
Begin
Declare @@stock int
if exists (select * from producte where idprod = @@idprod) and exists (select * from client where idclient = @@idclient)
Begin
select @@stock = (select quant from producte where idprod=@@idprod)
Begin transaction
insert into venta (idprod,idclient,quant,preu)
values (@@idprod,@@idclient,@@quant,@@preu)
update producte
set quant = @@stock - @@quant
where idprod = @@idprod
print 'Venta registrada'
Commit transaction
End
else
Begin
print 'Algun dels parametres no existeix'
End
End
Else
Begin
print 'No has introduit algun parametre'
End
