CODEWITHSUNDEEP
sql-server-2008triggers
Muhammed Salah
Muhammed Salah

Reputation: 271

SQL Server Trigger After Update

i made an stored proc 

ALTER proc [dbo].[MakeOrder]
@barcode varchar(50),
@packs int ,
@units int,
@Eid int 
as
begin

insert into Orders (Barcode,Price,PacksQty,UnitQty)
values        (@barcode,dbo.GetOrderPackPrice(@packs,@barcode)+dbo.GetOrderUniPrice(@units,@barcode),@packs,@units)

insert into OrderDetails(Eid,Date)
values (@Eid,GETDATE())

update Product
set Stock = Stock-@packs , UnitsStock = UnitsStock-@units where BarCode=@barcode

end

and i want to make after update trigger on product table to check the value of UnitsStock Column After Update If it 0 do something else do another thing

Upvotes: 0

Views: 156

Answers (1)

David M
David M

Reputation: 72890

You don't need a trigger to do this necessarily. You can simply select the value of this column out again:

DECLARE @currentUnits int
SELECT  @currentUnits = UnitsStock FROM Product WHERE BarCode = @barcode

and then build in some conditional logic:

IF @currentUnits <= 0
BEGIN
    -- Do something
END
ELSE
BEGIN
    -- Do something else
END

Since you're not checking whether the number of units being ordered is less than the current UnitsStock, you're better off with a check for <= 0, or maybe even a separate check for < 0 to handle this differently still.

This code should go in your stored procedure, after the UPDATE statement.

Upvotes: 2

Related Questions