SQL Activity manager- I have a process that is running 2 transaction and is being blocked by another process at awaiting command, i have a trigger running and i beleive it may be whats causing the issue as i when i disabled the trigger and the issue has stopped. the problem is is that i cannot recreate the blocking issue consistently, how can i get to the bottom of this?
Attached is the trigger code.
/****** Object: Trigger [scheme].[mihaddcost_ins] Script Date: 06/17/2008 22:25:58 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
ALTER TRIGGER [scheme].[mihaddcost_ins]
--Trigger used to modify the driver field in opdetm with costs stored in stockm.
--The value entered depends on the currency of the customer.
--stockm.setup cost holds the sterling value
--stockm.shelf_life holds the euro value
INSERT INTO [scheme].[opdetm_cost]
, CASE WHEN RTRIM(LTRIM(c.currency)) = 'EUR' AND s.shelf_life > 0
WHEN RTRIM(LTRIM(c.currency)) = 'EUR' AND s.shelf_life = 0
WHEN RTRIM(LTRIM(c.currency)) = '' AND s.setup_cost =0
WHEN RTRIM(LTRIM(c.currency)) = '' AND s.setup_cost > 0
* CASE WHEN i.order_line_status ='B'
, CASE RTRIM(LTRIM(c.currency))
WHEN 'EUR' THEN i.val/1.27
ELSE i.val END AS STGVAL
FROM INSERTED i
JOIN [scheme].[opheadm] o
ON o.order_no = i.order_no
JOIN [scheme].[slcustm] c
ON c.customer = o.customer
LEFT JOIN [scheme].[stockm] s
ON s.[product] = i.product
AND s.[warehouse]= i.warehouse