Solved

locking issue with update trigger

Posted on 2008-06-12
3
160 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
Comment Utility
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]
Comment Utility
you will need an additional index:

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

Author Comment

by:meteorelec
Comment Utility
cheers again angelIII !!!!!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

762 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

12 Experts available now in Live!

Get 1:1 Help Now