trigger halt down software very urgent

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

trigger halt down software very urgent

Author

Topic

sefak169
Starting Member

6 Posts

Posted - 2010-02-06 : 04:22:22

front end :- Visual Basic 6.0
Back End :- Sql server 2005 express edition

i have created a trigger which exectutes every time stock is updated.

but when the trigger is running it halts down all the other operation on other network computer .

i have been using stored procedure and it was working fine

i understand the sql needs to lock down the table for trigger to work but i dnt understand y other tables are also locked
and y software on network computer totally halt and even select statments doesnt work untill trigger totally completes its operation .

Kristen
Test

22859 Posts

Posted - 2010-02-06 : 04:27:52

Please show us the the code for your trigger, otherwise we are just guessing.

sefak169
Starting Member

6 Posts

Posted - 2010-02-06 : 04:55:29

following is the exact code of the trigger i m using

 USE [Acme_Inv_Sys]
GO
/****** Object:  Trigger [dbo].[Tr_UpdateItemCostledger_AI]    Script Date: 02/06/2010 14:54:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[Tr_UpdateItemCostledger_AI] ON [dbo].[tb_item_cost_ledger] After insert 

AS
--Start Global Declaration
declare @mVDate datetime ,
		@mItemId numeric(18,0)
--End Global Declaration

select @mVDate = isnull(Vdate , '1900-01-01') from inserted
update Tb_Item_Cost_Ledger set Total = isnull(debit, 0)  - isnull(credit , 0) , TotalQty = isnull(DqtyIn, 0)  - isnull(DqtyOut , 0) where autoid in (select autoid from inserted)

---Create Temporary Table For Total If Not Avaialable 
IF OBJECT_ID('dbo.Tmp_AI_Item_Cost_Ledger_Item', 'U') IS NULL
begin
	CREATE TABLE [dbo].[Tmp_AI_Item_Cost_Ledger_Item](
			Id numeric(18 , 0) identity(1, 1), 
			AutoId numeric(18 , 0), 
			DQtyIn numeric( 25 , 7) ,
			DQtyOut numeric( 25 , 7) ,
			TotalQty numeric( 25 , 7) ,
			Stock numeric( 25 , 7) , 
			Debit numeric( 25 , 7) , 
			Credit numeric( 25 , 7) , 
			Total numeric( 25 , 7) ,
			Balance numeric( 25 , 7),
			average_cost numeric( 25 , 7) , 
			VMID numeric(18 , 0),
			VDID numeric(18 , 0),
			Vtype numeric(18 , 0)
			PRIMARY KEY CLUSTERED ([Id] ASC))
end 
---End Create Temporary Table If Not Avaialable 

declare @RunningBalance numeric(25 , 7) ,
		@RunningStock numeric(25 , 7) ,
		@Average_Cost numeric(25 , 7) 
-----Start Cursor Of Item
declare items_All_Cur cursor for 
	select ItemId from Inserted group by Itemid
open items_All_Cur
	fetch next from items_All_Cur into
		@mItemId
while @@fetch_status = 0 
begin

	--Delete Any Previous Entry If Available In Temporary Table
	delete from Tmp_AI_Item_Cost_Ledger_Item

			set @RunningBalance = 0 
			set @RunningStock = 0 
			set @Average_Cost = 0 
		select @RunningStock = isnull(sum(DQtyIn) - sum(DQtyOut), 0) , @RunningBalance = isnull(sum(debit) - sum(credit), 0) from Tb_Item_Cost_Ledger where vdate < @mVDate and ItemId = @mItemId 
		SELECT TOP 1 @Average_Cost = ISNULL(AVERAGE_COST, 0) from Tb_Item_Cost_Ledger where vdate < @mVDate and ItemId = @mItemId ORDER BY vdate DESC, VTYPE DESC, VMID DESC, OtherId DESC, Autoid DESC

		INSERT INTO Tmp_AI_Item_Cost_Ledger_Item
		SELECT 
			Autoid, DqtyIn , DqtyOut , TotalQty , Null , Debit , Credit , Total , Null , Null , VMID , VDID , VType
		FROM Tb_Item_Cost_Ledger where Itemid = @mItemId and vdate > = @mvdate 
		ORDER BY vdate , VTYPE , VMID , OtherId , Autoid
		--Get Running Total 
		UPDATE Tmp_AI_Item_Cost_Ledger_Item set
			@RunningBalance = Tmp_AI_Item_Cost_Ledger_Item.Balance = 
				case when a.vtype not in(dbo.fnc_sale_invoice_id(), dbo.fnc_Internal_transfer_note_id() ) then
						@RunningBalance + Tmp_AI_Item_Cost_Ledger_Item.Total
					when a.vtype in(dbo.fnc_sale_invoice_id(), dbo.fnc_Internal_transfer_note_id() ) and  a.DqtyOut> 0  then
						@RunningBalance - (@Average_Cost * Tmp_AI_Item_Cost_Ledger_Item.DqtyOut)
					when a.vtype in(dbo.fnc_sale_invoice_id(), dbo.fnc_Internal_transfer_note_id() ) and  a.DqtyIn > 0 then
						@RunningBalance + (@Average_Cost * Tmp_AI_Item_Cost_Ledger_Item.DqtyIN)

				end,
			 @RunningStock = Tmp_AI_Item_Cost_Ledger_Item.Stock = @RunningStock + Tmp_AI_Item_Cost_Ledger_Item.TotalQty 
			 ,@Average_Cost = average_cost = 
					case when (NULLIF(IsNull(@RunningBalance , 0), 0) / NULLIF(isnull(@RunningStock , 0), 0))  is null then
							@Average_Cost
						 else
							(NULLIF(IsNull(@RunningBalance , 0), 0) / NULLIF(isnull(@RunningStock , 0), 0)) 
					end
		FROM Tmp_AI_Item_Cost_Ledger_Item inner join Tmp_AI_Item_Cost_Ledger_Item a 
		ON Tmp_AI_Item_Cost_Ledger_Item.id = a.id 


		--Updating Item Cost ledger for balances
		UPDATE Tb_Item_Cost_Ledger SET 
			Stock = isnull(Tmp_AI_Item_Cost_Ledger_Item.Stock , 0),
			Balance = isnull(Tmp_AI_Item_Cost_Ledger_Item.Balance , 0),
			Average_Cost = isnull(Tmp_AI_Item_Cost_Ledger_Item.Average_Cost , 0)
			,Credit = 
				(case when Tb_Item_Cost_Ledger.vtype in(dbo.fnc_sale_invoice_id(), dbo.fnc_Internal_transfer_note_id()) and Tb_Item_Cost_Ledger.DQtyOut > 0  then
					(isnull(Tmp_AI_Item_Cost_Ledger_Item.Average_Cost , 0) * isnull(Tb_Item_Cost_Ledger.DQtyOut, 0) )
				else
					isnull(Tb_Item_Cost_Ledger.Credit, 0) 
				end )
			,Debit = 
				(case when Tb_Item_Cost_Ledger.vtype in(dbo.fnc_Internal_transfer_note_id()) and Tb_Item_Cost_Ledger.DQtyIn > 0  then
					isnull(Tmp_AI_Item_Cost_Ledger_Item.Average_Cost , 0) * isnull(Tb_Item_Cost_Ledger.DQtyIn, 0) 
				else
					isnull(Tb_Item_Cost_Ledger.Debit, 0) 
				end )

			,Total = 
				(case when Tb_Item_Cost_Ledger.vtype in(dbo.fnc_sale_invoice_id(), dbo.fnc_Internal_transfer_note_id()) then
					(isnull(Tmp_AI_Item_Cost_Ledger_Item.Average_Cost , 0) * isnull(Tb_Item_Cost_Ledger.TotalQty, 0))
				else
					isnull(Tb_Item_Cost_Ledger.Total, 0) 
				end )
		FROM Tmp_AI_Item_Cost_Ledger_Item inner join Tb_Item_Cost_Ledger
		ON Tmp_AI_Item_Cost_Ledger_Item.Autoid = Tb_Item_Cost_Ledger.Autoid

-----Final Stock Balance
declare 
	@mStock numeric(18,0),
	@mAverage_Cost numeric(25,7)

IF OBJECT_ID('dbo.Tmp_Item_Calculations', 'U') IS NULL
begin
Create Table Tmp_Item_Calculations(
	ItemId numeric (18,0),
	Stock numeric(25,7),
	Average_cost numeric(25,7)
		)
end
Delete from Tmp_Item_Calculations where itemid = @mItemId
set @mStock = 0 
set	@mAverage_Cost  = 0 
select Top 1 @mStock = isnull(Stock , 0) , @mAverage_Cost = isnull(Average_Cost, 0) From Tb_item_cost_ledger where itemid = @mItemId ORDER BY Vdate desc, VTYPE desc, VMID desc, OtherId desc, Autoid desc
INSERT INTO Tmp_Item_Calculations([ItemId], [Stock], [Average_cost]) VALUES (
	@mItemId , 
	@mStock , 
	@mAverage_Cost)

	fetch next from items_All_Cur into
			@mItemId
end
close items_All_Cur
deallocate items_All_Cur
Drop Table Tmp_AI_Item_Cost_Ledger_Item
-----End Cursor Of Item

---Create Temporary Table If Not Avaialable 
IF OBJECT_ID('dbo.Tmp_AI_Item_Cost_Ledger_LocItem', 'U') IS NULL
begin
	Create TABLE Tmp_AI_Item_Cost_Ledger_LocItem(
		Id numeric(18 , 0) identity (1, 1) , 
		AutoId numeric(18 , 0), 
		Total numeric( 25 , 7) , 
		RunningTotal numeric( 25 , 7)
		PRIMARY KEY CLUSTERED ([Id] ASC))
end 
---End Create Temporary Table If Not Avaialable 

declare 
	@mlocationId numeric(18,0),
	@mLocStock numeric(25,7),
	@RunningLocStock numeric(25 , 7) 

set @mItemId = 0 
set @mlocationId = 0 
-----Start Cursor Of Location Item
declare items_Loc_Cur cursor for 
	select ItemId , locationid from Inserted group by Itemid , locationid 
open items_Loc_Cur
	fetch next from items_Loc_Cur into
		@mItemId,
		@mlocationId
while @@fetch_status = 0 
begin
		--For Location Wise Stock
		delete from Tmp_AI_Item_Cost_Ledger_LocItem
		set @RunningLocStock = 0 
		select @RunningLocStock = isnull(sum(DQtyIn) - sum(DQtyOut), 0) from Tb_Item_Cost_Ledger where vdate < @mVDate and ItemId = @mItemId and locationid = @mlocationId
		 --insert in temporary table 
		INSERT INTO Tmp_AI_Item_Cost_Ledger_LocItem
		SELECT Autoid, TotalQty , null
		FROM Tb_Item_Cost_Ledger where Itemid = @mItemId and vdate > =   @mvdate and locationid = @mlocationId
		ORDER BY vdate , VTYPE , VMID , OtherId , Autoid
		--Get Running Total 
		UPDATE Tmp_AI_Item_Cost_Ledger_LocItem
		SET @RunningLocStock = Tmp_AI_Item_Cost_Ledger_LocItem.RunningTotal = @RunningLocStock + Tmp_AI_Item_Cost_Ledger_LocItem.total
		FROM Tmp_AI_Item_Cost_Ledger_LocItem inner join Tmp_AI_Item_Cost_Ledger_LocItem a 
		ON Tmp_AI_Item_Cost_Ledger_LocItem.id = a.id
		--Updating Item Cost ledger for balances
		UPDATE Tb_Item_Cost_Ledger
		SET locStock = isnull(Tmp_AI_Item_Cost_Ledger_LocItem.RunningTotal , 0)
		FROM Tmp_AI_Item_Cost_Ledger_LocItem inner join Tb_Item_Cost_Ledger
		ON Tmp_AI_Item_Cost_Ledger_LocItem.Autoid = Tb_Item_Cost_Ledger.Autoid

-----Start Final Location Wise Stock Balance
IF OBJECT_ID('dbo.Tmp_Item_Loc_Calculations', 'U') IS NULL
begin
Create Table Tmp_Item_Loc_Calculations(
	ItemId numeric (18,0),
	LocationId numeric (18,0),
	LocStock numeric(25,7)
		)
end
Delete from Tmp_Item_Loc_Calculations where itemid = @mItemId and LocationId  = @mlocationId
set @mLocStock = 0 
select Top 1 @mLocStock = isnull(LocStock , 0) From Tb_item_cost_ledger where itemid = @mItemId and locationid = @mlocationId ORDER BY Vdate desc, VTYPE desc, VMID desc, OtherId desc, Autoid desc

INSERT INTO Tmp_Item_Loc_Calculations([ItemId], [LocationId], [LocStock]) VALUES (
	@mItemId , 
	@mlocationId,
	@mLocStock 
	)
-----End Final Location Wise Stock Balance
	fetch next from items_Loc_Cur into
			@mItemId,
			@mlocationId
end
close items_Loc_Cur
deallocate items_Loc_Cur
drop table Tmp_AI_Item_Cost_Ledger_LocItem

sefak169
Starting Member

6 Posts

Posted - 2010-02-06 : 05:03:22

this trigger actually calculates running stock and average cost
so it very crutial for the whole software every thing is based on this trigger

Kristen
Test

22859 Posts

Posted - 2010-02-06 : 07:30:37

The trigger is, at a glance, hugely inefficient in its use of Cursors and working tables.

Furthermore the temporary tables are not shared, so two concurrent updates will block each other.

The updates on the underlying table Tb_Item_Cost_Ledger may well be escalated to whole table locks, thus blocking all other users from updating either that table

It is also possible that the various queries are not well optimised for use of indexes etc, which would further exacerbate the problem.

If you do not have good DBA skills for housekeeping of the database (index rebuilds and the like) then that too would make the situation worse.

Just to pick up on your earlier point:

"i understand the sql needs to lock down the table for trigger to work"

That should not need to be the case, but it may well be the effect given the way that this trigger is written

"but i dnt understand y other tables are also locked and y software on network computer totally halt and even select statments doesnt work untill trigger totally completes its op"

That will most probably be because the other processes need to access Tb_Item_Cost_Ledger, and are blocked by the locks, those process also need other tables, and those tables then become blocked to other users, so the net effect is to block all access.

" trigger halt down software very urgent"

If it is urgent then get a consultant in who can work onsite and sort it out for you promptly. Support from folk on the forums here is unlikely to solve this for you in a timely fashion, or taking into account all the other ramifications in your system which, in my experience, will only be discovered "bit by bit" trying to solve this via the forums.

sefak169
Starting Member

6 Posts

Posted - 2010-02-06 : 18:42:52

can please give me some hints to improve this code

Kristen
Test

22859 Posts

Posted - 2010-02-07 : 02:40:15

It would take me several hours, I don't have the time to do that on one question here.

Do you have a DBA who is trained in this sort of thing?

This is presumably not a simple application, is there no budget for skilled help?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources