Update trigger not firing properly

you are saying that a store which was not a in bigstore table. it value updated and its total quantity is less then 50,000 and still ends up in the bigstore.

that highly unlikely, because that store might be a in bigstore table 1st.


for your understanding check the following example:

USE tempdb
GO

create table instock ( storeid int, QUANTITY int)
create table bigstores ( storeid int)
create table stores ( storeid int)
GO
CREATE TRIGGER TRI_updateINSTOCK
ON instock
FOR update
AS
BEGIN
DECLARE
@storeid smallint,
@totalquantity int

SELECT @storeid= i.storeid FROM inserted i

select @totalquantity = SUM(QUANTITY)
FROM instock
WHERE storeid = @storeid

IF NOT EXISTS(SELECT storeid FROM bigstores WHERE storeid =@storeid )
BEGIN

select @totalquantity

insert into bigstores
select * from stores
where StoreId = @StoreId AND @totalquantity >= 50000
END

END
GO

insert into stores
select 1 union all
select 2 union all
select 3 union all
select 4

insert into instock
select 1, 10000
from stores

---------------------- updated the quantity added to exceed 50,000
update instock
set QUANTITY = QUANTITY + 10000
where storeid = 1

---------------------- updated the quantity drop from 50,000
update instock
set QUANTITY = QUANTITY - 10000
where storeid = 1

GO

drop table instock
drop table bigstores
drop table stores