Solved

Locking issue when triggers are applied

Posted on 2008-06-11
10
192 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
  • 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
Independent Software Vendors: 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!

 
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

Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Following an example - removing duplicate strings 4 67
Query to Add Late Tolerance 10 82
convert null in sql server 12 57
Can I skip a node in XML? 9 32
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
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…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

756 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