Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Locking issue when triggers are applied

Posted on 2008-06-11
10
Medium Priority
?
202 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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 …
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…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Loops Section Overview

971 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