Solved

Locking issue when triggers are applied

Posted on 2008-06-11
10
197 Views
Last Modified: 2010-03-19
i am using sql 2005 std edition and i have applied a trigger to a table that writes of certain fields to another table as soon as they entered.
i want this second table so i can report of the back of it without the report locking- which happens quite alot on the live table.

The issue is now that due to the trigger i am experiencing quite alot of update locks - lck_m_u, and i am having to use sql manager to kill the locking proccess's !

is there a certain procedure i should have followed to ensure this does not happen?
i am a beginner here  so i would appreciate it (if possible) to explain in detail what i should be doing!

thanks in advance!
0
Comment
Question by:meteorelec
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
10 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21758152
please post the trigger code.
what indexes are on the 2 tables involved?
0
 
LVL 2

Author Comment

by:meteorelec
ID: 21758178
below is the trigger i applied, and as far as indexs go i have done nothing! which more than likely will be my  issue then? but i have no idea on best practices to create an index between the 2


USE [cs3live]
GO
/****** Object:  Trigger [scheme].[mihaddcost_ins]    Script Date: 06/11/2008 10:36:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [scheme].[mihaddcost_ins]

ON [scheme].[opdetm]

AFTER INSERT

AS


 

    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)
   
      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 @OrderQty = order_qty FROM INSERTED

    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


    INSERT INTO [scheme].[opdetm_cost]
      VALUES(@OrderNo, @OrderLineNo, (@CostToAdd * @OrderQty), @STGVAL, @DATEENTERED, @LINETYPE, @TERRITORY)
 
0
 
LVL 2

Author Comment

by:meteorelec
ID: 21758512
sorry, there are indexs, but i did not create them, would these be created automatically?
0
Business Impact of IT Communications

What are the business impacts of how well businesses communicate during an IT incident? Targeting, speed, and transparency all matter. Find out more in this infographic.

 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 21759626
trigger code revised:
you can do it with 1 single insert-select statement !
ALTER TRIGGER [scheme].[mihaddcost_ins]
ON [scheme].[opdetm]
AFTER INSERT
AS
    INSERT INTO [scheme].[opdetm_cost]
     ( OrderNo, OrderLineNo, CostTotal, STGVAL, DATEENTERED, LINETYPE, TERRITORY)   
    SELECT i.order_no
          , i.order_line_no 
          , 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 
          , CASE RTRIM(LTRIM(c.currency)) 
                      WHEN 'EUR' THEN i.val/1.27
                      ELSE o.val END AS STGVAL
          , o.date_entered
          , i.line_type  
          , o.territory 
       FROM INSERTED i
       JOIN [scheme].[opheadm] o
         ON o.order_no = i.order_no
       JOIN [scheme].[slcustm] c
         ON c.customer = o.customer
       JOIN [scheme].[stockm] s
         ON s.[product] = i.product 
        AND [warehouse]= i.Warehouse

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21759646
now, the second part is the indexes:

[scheme].[opheadm]
-> 1 index on order_no

[scheme].[slcustm]
-> 1 index on customer

[scheme].[stockm]
-> 1 index on product + warehouse

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21759677
>sorry, there are indexs, but i did not create them, would these be created automatically?
yes, for primary keys and unique keys.
0
 
LVL 2

Author Comment

by:meteorelec
ID: 21760585
angelIII


when you refer to the indexs, can i just use sql manager - create new index and input this script and that will apply the indexing?

ps

thanks very much for effort you have went to!
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21760627
yes, indexes are used "on the fly" ...
0
 
LVL 2

Author Comment

by:meteorelec
ID: 21760672
i will try this when i can get all user s offline some of these nites! and post my results then !


thanks angel
0
 
LVL 2

Author Comment

by:meteorelec
ID: 21766970
i tried this last night and it seems it is the actual update trigger that is now the main issue!


i will close this question and assign points as the question i asked was answered 100%

this is a link to the new question !

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

Featured Post

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

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

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …

729 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