Trigger fires but missing data?

[MissyEd]

I have an AFTER trigger for INSERT, DELETE, UPDATE as below. Using profiler, I can see that the trigger has fired but the data is missing from the target table. This occurs ocassionally, I'm at a loss to explain why.

The trigger should rank all rows with a matching code and clear then re-insert into a ranking table.

SET NOCOUNT ON

DECLARE @ssr TABLE(
	rank INT IDENTITY(1,1)
	,code DECIMAL(10,0)
	,supplier VARCHAR(7)
	,order_code VARCHAR(15)
	,price MONEY
	,current_stock INT
	,date_updated DATETIME)

IF (SELECT COUNT(Code) FROM Inserted) > 0
BEGIN
	DELETE ssr
	FROM dbo.Stock_Supplier_Ranked ssr WITH (ROWLOCK)
	WHERE EXISTS (SELECT 1 FROM Inserted i WHERE i.Code = ssr.Code)	

	INSERT into @ssr(code, supplier, order_code, price, current_stock, date_updated)
	SELECT 	DISTINCT ss.code
			,ss.supplier
			,ss.order_code
			,ss.price
			,ss.current_stock 
			,ss.date_updated
	FROM dbo.stock_supplier ss with (nolock)
	WHERE EXISTS (SELECT 1 FROM Inserted i WHERE i.Code = ss.Code)	
	ORDER BY ss.code
		,ss.price
		,ss.order_code
		,ss.current_stock desc
		,ss.supplier
		,ss.date_updated desc
		
	INSERT INTO dbo.Stock_Supplier_Ranked WITH (ROWLOCK) (rank, code, supplier, order_code, price, current_stock, date_updated)
	SELECT DISTINCT rank, code, supplier, order_code, price, current_stock, date_updated
	FROM   @ssr
END
ELSE
BEGIN
	DELETE ssr
	FROM dbo.Stock_Supplier_Ranked ssr WITH (ROWLOCK)
	WHERE EXISTS (SELECT 1 FROM Deleted d WHERE d.Code = ssr.Code)
END

"There are only two lasting bequests we can hope to give our children; one of these is roots, the other wings" - Hodding Carter

 

[MissyEd]

Post coffee, I've worked out a flaw in my plan and amended it as shown below. It still isnt firing all the time but I think Im getting a little closer

SET NOCOUNT ON

DECLARE @ssr TABLE(
	rank INT IDENTITY(1,1)
	,code DECIMAL(10,0)
	,supplier VARCHAR(7)
	,order_code VARCHAR(15)
	,price MONEY
	,current_stock INT
	,date_updated DATETIME)

DELETE ssr
FROM dbo.Stock_Supplier_Ranked ssr WITH (ROWLOCK)
WHERE EXISTS (SELECT 1 FROM Inserted i WHERE i.Code = ssr.Code)	
OR    EXISTS (SELECT 1 FROM Deleted d WHERE d.Code = ssr.Code)

INSERT into @ssr(code, supplier, order_code, price, current_stock, date_updated)
SELECT 	DISTINCT ss.code
		,ss.supplier
		,ss.order_code
		,ss.price
		,ss.current_stock 
		,ss.date_updated
FROM dbo.stock_supplier ss with (nolock)
WHERE EXISTS (SELECT 1 FROM Inserted i WHERE i.Code = ss.Code)	
OR    EXISTS (SELECT 1 FROM Deleted d WHERE d.Code = ss.Code)
ORDER BY ss.code
	,ss.price
	,ss.order_code
	,ss.current_stock desc
	,ss.supplier
	,ss.date_updated desc
	
INSERT INTO dbo.Stock_Supplier_Ranked WITH (ROWLOCK) (rank, code, supplier, order_code, price, current_stock, date_updated)
SELECT DISTINCT rank, code, supplier, order_code, price, current_stock, date_updated
FROM   @ssr

"There are only two lasting bequests we can hope to give our children; one of these is roots, the other wings" - Hodding Carter