Trigger so that it does not update the stock when it is 0

Question

Trigger so that it does not update the stock when it is 0

Navigation

#1 by (0 votes)
#2 by (-1 votes)

2

Hi, I want to make a trigger that when the stock of my inventory is less than or equal to 0, it does not allow me to sell more product but it still subtracts them anyway.

create trigger NoProductos0a on InventarioProducto for update 
as 
declare @cantidad int
select @cantidad = count(InventarioProducto.Cantidad) from InventarioProducto
if (@cantidad<0)
begin
print 'No se puede realizar la venta, supera la cantidad existente del producto.' 
end

The structure of the table is as follows

InventarioProducto
IdProducto int identity(1,1)
Nom_Producto varchar(50)
Precio_Uni decimal(18,2)
Cantidad int

The value of Quantity is 5 and I try that when executing this command I do not get results -1

update InventarioProducto set Cantidad-=6 where IdProducto=1

sql sql-server trigger

asked by Eduardo Noyola 10.05.2018 в 19:05

source

2 answers

-1

It is that unless you send a negative number you will continue subtracting because you tell him that if quantity is less than 0 he sends you the message, do this:

create trigger NoProductos0a on InventarioProducto for update 
as 
declare @cantidad int
select @cantidad = count(InventarioProducto.Cantidad) from InventarioProducto
if (@cantidad<=0)
begin
print 'No se puede realizar la venta, supera la cantidad existente del producto.' 
end

Change the if to less than or equal to 0

answered by 10.05.2018 в 19:18

How to consume a web service that returns a nameless jsonArray Xamarin Android "Permission Denied" when saving a bitmap

score 0 · Accepted Answer

Some observations

To effectively stop the update, it is not enough to make a print you must "issue" an error. This is achieved with RAISEERROR , in your case you should print it by: RAISERROR ('No se puede realizar la venta, supera la cantidad existente del producto.' ,10,1) , but if in addition, your application is not controlling the error and doing an explicit% ROLLBACK you would have to add a ROLLBACK TRANSACTION .
I do not understand very well this logic: select @cantidad = count(InventarioProducto.Cantidad) from InventarioProducto , Why the COUNT Are you updating one or more records ?, I recommend you also go directly to see the value in INSERTED , this is a view that you have in the triggers and that contains the records after the update, so it is better to do SELECT @cantidad = count(Cantidad) FROM INSERTED or SELECT @cantidad = Cantidad FROM INSERTED , the latter if you are only updating a row.
Finally, it is worth remembering that the triggers in SQLServer are batch, that is to say, they are executed only once independently if your UPDATE is from one or several registers, this I am telling you about that the trigger seems to assume that they will only update a single row. Keep in mind, because the current logic does not seem to be prepared for a UPDATE massive table, that we can always do by other means beyond the application, if you do not want to contemplate this, what is usually done is to verify how many rows are being updated and emit another error if they are more than the trigger logic is able to handle.
And now, the last of the last, do you know that this verification seems very simple: that a certain column does not have a value less than 0, you could eventually do it by means of a CHECK , No need to program a trigger?

Edit your trigger with the following code:

IF (SELECT Cantidad FROM INSERTED) < 0 BEGIN
    RAISERROR ('No se puede realizar la venta, supera la cantidad existente del producto.',10,1)
    ROLLBACK TRANSACTION
END