asked on

Locking issue when triggers are applied

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!

Guy Hengel [angelIII / a3]

please post the trigger code.
what indexes are on the 2 tables involved?

meteorelec

ASKER

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)

meteorelec

ASKER

sorry, there are indexs, but i did not create them, would these be created automatically?

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Guy Hengel [angelIII / a3]

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

Guy Hengel [angelIII / a3]

>sorry, there are indexs, but i did not create them, would these be created automatically?
yes, for primary keys and unique keys.

meteorelec

ASKER

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!

Guy Hengel [angelIII / a3]

yes, indexes are used "on the fly" ...

meteorelec

ASKER

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

thanks angel

meteorelec

ASKER

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 !

https://www.experts-exchange.com/questions/23478552/locking-issue-with-update-trigger.html