locking issue with update trigger

i have wriiten insert update and delete triggers that works all be it with a side effect of locks when activity gets high on the database! I Initially though the problem was with the insert trigger which i got resolved at

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_23475062.html

but it now seems the update is the problem!

can anyone advise on a better way to write the following code?
USE [demo]
GO
/****** Object:  Trigger [scheme].[mihaddcost_upd]    Script Date: 06/12/2008 08:42:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [scheme].[mihaddcost_upd]
 
ON [scheme].[opdetm]
 
AFTER UPDATE
 
AS
 
--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
 
 
 
 
    DECLARE @Warehouse CHAR(2)
    DECLARE @Product CHAR(20)
    DECLARE @Customer CHAR(8)
    DECLARE @OrderNo CHAR(10)
    DECLARE @OrderLineNo CHAR(4)
    DECLARE @Currency CHAR(3)
    DECLARE @CostToAdd FLOAT
	DECLARE @OrderQty FLOAT
	DECLARE @SetupCost FLOAT(8)
	DECLARE @Shelflife FLOAT(8)
	DECLARE @VAL FLOAT
	DECLARE @STGVAL FLOAT
	DECLARE @DATEENTERED DATETIME
	DECLARE @LINETYPE CHAR(1)
	DECLARE @TERRITORY CHAR(6)
    DECLARE @ALLOCATEDQTY FLOAT
	DECLARE @DESPATCHEDQTY FLOAT
	DECLARE @DETORDERQTY FLOAT
 
	SELECT @OrderNo = order_no FROM INSERTED    
 
    SELECT @OrderLineNo = order_line_no FROM INSERTED
 
	SELECT @LINETYPE = line_type FROM INSERTED
 
    SELECT @VAL = val FROM INSERTED
	
	SELECT @Warehouse = warehouse FROM INSERTED
 
    SELECT @Product = product FROM INSERTED
 
	SELECT @ALLOCATEDQTY = allocated_qty FROM INSERTED
 
	SELECT @DESPATCHEDQTY = despatched_qty FROM INSERTED
	
	SELECT @DETORDERQTY = order_qty FROM INSERTED
 
	IF @ALLOCATEDQTY > 0
	
		SELECT @OrderQty=@ALLOCATEDQTY 
 
	IF @ALLOCATEDQTY = 0 and @DESPATCHEDQTY > 0
	
		SELECT @OrderQty=@DESPATCHEDQTY
 
	IF @ALLOCATEDQTY = 0 and @DESPATCHEDQTY = 0
 
		SELECT @OrderQty=@DETORDERQTY
 
    SELECT @Customer = customer FROM [scheme].[opheadm] WITH (NOLOCK) WHERE [order_no] = @OrderNo
 
    SELECT @Currency = currency FROM [scheme].[slcustm] WITH (NOLOCK) WHERE [customer] = @Customer
	
	SELECT @SetupCost = setup_cost FROM [scheme].[stockm] WITH (NOLOCK) WHERE [product] = @Product AND [warehouse]=@Warehouse
 
	SELECT @Shelflife = shelf_life FROM [scheme].[stockm] WITH (NOLOCK) WHERE [product] = @Product AND [warehouse]=@Warehouse
	
	SELECT @DATEENTERED = date_entered FROM [scheme].[opheadm] WITH (NOLOCK) WHERE [order_no] = @OrderNo
 
	SELECT @TERRITORY = territory FROM [scheme].[opheadm] WITH (NOLOCK) WHERE [order_no] = @OrderNo
 
 
	IF RTRIM(LTRIM(@Currency)) = 'EUR'
 
		SELECT @STGVAL = (@VAL/1.27)
 
	IF RTRIM(LTRIM(@Currency)) = ''
 
		SELECT @STGVAL = @VAL
 
    IF RTRIM(LTRIM(@Currency)) = 'EUR' and (@Shelflife)>0
	
       SELECT @CostToAdd = shelf_life FROM [scheme].[stockm] WITH (NOLOCK) WHERE warehouse = @Warehouse AND product = @Product   
	
	IF RTRIM(LTRIM(@Currency)) = 'EUR' and (@Shelflife)=0
 
       SELECT @CostToAdd = standard_cost FROM [scheme].[stockm] WITH (NOLOCK) WHERE warehouse = @Warehouse AND product = @Product   
 
	IF RTRIM(LTRIM(@Currency)) = '' and (@SetupCost)=0
 
		SELECT @CostToAdd = standard_cost FROM [scheme].[stockm] WITH (NOLOCK) WHERE warehouse = @Warehouse AND product = @Product
 
	IF RTRIM(LTRIM(@Currency)) = '' and (@SetupCost)>0
 
		SELECT @CostToAdd = setup_cost FROM [scheme].[stockm] WITH (NOLOCK) WHERE warehouse = @Warehouse AND product = @Product
 
 
	UPDATE [scheme].[opdetm_cost]
    SET cost = (@CostToAdd * @OrderQty), val = @STGVAL, line_type= @LINETYPE
    WHERE order_no = @OrderNo AND order_line_no = @OrderLineNo

Open in new window

LVL 2
meteorelecAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
basically the same logic, as far as I could see:
USE [demo]
GO
/****** Object:  Trigger [scheme].[mihaddcost_upd]    Script Date: 06/12/2008 08:42:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [scheme].[mihaddcost_upd]
ON [scheme].[opdetm]
AFTER UPDATE
AS
--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
 
   UPDATE oc
      SET cost = CASE WHEN RTRIM(LTRIM(c.currency)) = 'EUR' AND  s.shelf_life > 0
                              THEN s.shelf_life 
                              WHEN RTRIM(LTRIM(c.currency)) = 'EUR' AND  s.shelf_life = 0
                              THEN s.standard_cost
                              WHEN RTRIM(LTRIM(c.Currency)) = '' AND s.setup_cost =0
                              THEN s.standard_cost
                              WHEN RTRIM(LTRIM(c.Currency)) = '' AND s.setup_cost > 0
                              THEN s.setup_cost
                              ELSE 0
                          END
           * i.order_qty
        , val = CASE RTRIM(LTRIM(c.currency)) 
                      WHEN 'EUR' THEN i.val/1.27
                      ELSE o.val END 
        , line_type= i.line_type
     FROM [scheme].[opdetm_cost] oc
     JOIN inserted i
       ON i.order_no = oc.order_no
      AND i.order_line_no = oc.order_line_no
     JOIN [scheme].[opheadm] o
       ON o.order_no = i.order_no
     JOIN [scheme].[slcustm] c
       ON c.customer = a.customer
     JOIN [scheme].[stockm] s
       ON s.[product] = i.product 
      AND [warehouse]= i.Warehouse
   

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you will need an additional index:

[scheme].[opdetm_cost]
-> 1 index on order_no + order_line_no
0
 
meteorelecAuthor Commented:
cheers again angelIII !!!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.