Comments by TechJosh (Top 5 by date)

I just want to reiterate what everyone else here has said, you shouldn't be using per-row variables in a table trigger as you don't know that only one row will ever be deleted...

That said, the sql code itself is faulty as well. For instance, this is like a tiger chasing its own tail:
select @wineid = wineid
from instock
where wineid = @wineid

Since @wineid starts off null the where clause specifies that the instock.wineid must be null, which if the database is properly designed, is probably not possible.

It might be possible to write just a DELETE statement as your trigger that uses the deleted table in a subquery to find all StoreIds/wineIds that should be deleted. Perhaps something like this:
DELETE FROM bigstores bs
INNER JOIN deleted d
ON bs.StoreId = d.StoreId
CROSS APPLY (
SELECT SUM (Quantity) as Quantity
FROM instock (NOLOCK)
WHERE instock.StoreId = d.StoreId
) AS Calc
WHERE
Calc.Quantity < 50000

However, bear in mind that this query only handles DELETE statements. If someone were to UPDATE the instock table your trigger wouldn't fire as it is written. You would need to add UPDATE to the list of operations the trigger fires on. You would probably also want a similar trigger on INSERT.

I don't have a clear enough view of your database and use cases. But in this case it seems that you may not actually need the BigStores table anyways and perhaps it could be replaced with a view that only returned StoreIds which met the "bigStores" criteria. Then you wouldn't be caching data which could go stale on you, and you wouldn't need triggers to maintain the table.

CREATE VIEW bigstores
AS
SELECT
StoreId,
SUM(QUANTITY) AS TotalQuantity
FROM
InStock WITH (NOLOCK)
GROUP BY
StoreId