February 6, 2010 at 5:23 pm
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 even select statments doesnt work untill trigger totally completes its operation .
i have been using stored procedure and it was working fine
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
February 6, 2010 at 5:43 pm
1.your trigger doesn't have SET NOCOUNT ON at the beginning, which is good practice and may avert other issues receiving "1 row(s) affected" and thinking it's a result set.
2.you are using a cursor instead of a set based operation inside the trigger; that is bad practice, and it should be re-written.
3.because your cursor is doing this:
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
and then making the cursor update the same table INSIDE the cursor with this:
UPDATE Tb_Item_Cost_Ledger....
it is locking the table exclusively so nothing can touch the table until it is done. it should be using values from the INSERTED and DELETED tables instead, and not use a cursor at all.
4. it looks like you are using a static table to keep a running balance. instead you should remove the trigger and use a view to replace the static table instead; search for "Jeff Moden Running Total" here on SSC for a great article on it.
Lowell
February 7, 2010 at 11:56 am
I think Lowell's hit it on the head. The issue is that while the trigger runs, the transaction from your update continues, and all locks placed are held. Using a cursor ensures that you lock stuff while the cursor runs.
I would suggest you follow Lowell's advice and build this in a set based manner.
February 7, 2010 at 1:22 pm
Don't use "create table" in your trigger,because it will put a lock on the db system catalog !
If you need a worker object, prefer a #temp table or a @table var.
Why?
If your trigger fails, your transaction fails anyway, including your current create table. So that is pure lock overhead on your db system catalog.
Probably a set based approach will prove this table to be unneeded at all.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 7, 2010 at 1:41 pm
Lovell and alzdba thx for the replies and priceless suggestions
lovell i will be removing the cursor and will go for the set based operation but for the last suggestion of urs the one using view instead of static table .
there are many reasons y i m using static table
1) first of all my cleints demand instant results and thts y i m using static table so tht i can get the results as fast as i can . eg one of cleitns has 28000 different items and almost 2000 different racks and he wants the result as soon as he click the button so u see i have logged rack stock and total stock along with average cost in the table and i ve also created a master table to record current stock just to make it faster
2) i have created many reports based on this table changing this will cause alot of complications in my whole software
3) there are many calculation based on this table specially the average cost , fifo and lifo and others
so can u pls tell me tht if i use update query (which is very essential for static table) in the trigger along with set based operation will it still halt the program.??
and another thing i dnt understand one thing tht when ever this trigger is fired it halt's whole software along with other opertaions which are not related to this table
like if i run a select query from sale invoice table it wont run untill n unless the trigger is firred completely
February 7, 2010 at 1:52 pm
As long as the trigger is firing, any locks required by the update statement and the trigger will be held. The way you have coded this, you could be holding lots of locks on a variety of tables. No guarentee that this will run quicker and not impact other parts of your program with a set based operation, but it should run quicker.
February 7, 2010 at 1:56 pm
ok steve i will impliment and test it and will let u guys know the results
if u get any other ideas kindly let me know thx
February 7, 2010 at 2:47 pm
your trigger will suffer the use of the inline functions in the not in lists. ( Just a gut feeling, but i assume there are queries in the functions)
If you post the ddl, we may be able to help you optimize this trigger.
You'll want to support all the trigger queries with the appropriate indexes !!
Did you have a look at Jeff Moden's articles on RBAR and running totals ?
http://www.sqlservercentral.com/articles/62278/
http://www.sqlservercentral.com/articles/61716/
If you haven't, don't do it now, because you will need time to interpret them and play around with the ways of dong stuff. But it is a must to handle them on this topic !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply