Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Locking issue when triggers are applied

Posted on 2008-06-11
10
Medium Priority
?
201 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

722 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