Solved

locking issue with update trigger

Posted on 2008-06-12
3
161 Views
Last Modified: 2010-03-19
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

0
Comment
Question by:meteorelec
  • 2
3 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 21768092
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21768099
you will need an additional index:

[scheme].[opdetm_cost]
-> 1 index on order_no + order_line_no
0
 
LVL 2

Author Comment

by:meteorelec
ID: 21776680
cheers again angelIII !!!!!
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now