Trigger problem - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Trigger problem

Author

Topic

rushdib
Yak Posting Veteran

93 Posts

Posted - 2004-04-21 : 12:24:14

Hi,
I have an insert trigger on a table which calls up other stored procs to do specific tasks. My front end is an Access 2000 form. Everytime I add a record I have to wait about a minute to add a another record because of the trigger.
Is there any way I can run the rrigger in back of the scenes without holding up my data entry?

Thanks,

Rushdi

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-21 : 12:35:12

Post your trigger cuz it shouldn't take a minute for a trigger to execute. The trigger must be highly inefficient.

Tara

rushdib
Yak Posting Veteran

93 Posts

Posted - 2004-04-21 : 12:50:06

CREATE TRIGGER trig_Exec_Proc_Process_Suspense ON dbo.tblKNLClient FOR INSERT

AS

EXEC DW_Stage.dbo.proc_Process_Suspense_InvAdj
EXEC DW_Stage.dbo.proc_Process_Suspense_PM
EXEC DW_Stage.dbo.proc_Process_Suspense_ProdInfo
EXEC DW_Stage.dbo.proc_Process_Suspense_LocQty

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-21 : 12:53:23

Well we'll need to see the stored procedures too then. Do you really need to do this stuff inside a trigger?

Tara

rushdib
Yak Posting Veteran

93 Posts

Posted - 2004-04-21 : 13:14:51

Yes, because as soon as the user adds a client, some process must happen to move records from one table to another and do some updates and deletes. Also it has other procedure calls inside those stored procedures. The record volume is very low(from 10-20).
Now those procedures exist in a different database from the client table, and some inserts and updates live on different databases also on the same server.
I have included one procedure.

*
Input - tblInventoryAdjustments_Suspense
tblKNLClient
Output - None
Parameters - None
Description - This sp is called by a trigger trig_Exec_Proc_Process_Suspense_InvAdj (Datawarehouse database)
to move the matching clients from suspense table to stage InvAdj table. The trigger is
fired whenever a client is added to the tblKNLClient table.
If the insert is successful, then those records are deleted from the suspense table.
The inserted records are then appended to production table. If the records exists,
they are updated
Author - Rushdi basir
Date created - 2004.04.11
Date modified - 2004.04.11
Project - 5800-DataWhse - BI/DW (ST8485)

*/
ALTER PROC proc_Process_Suspense_InvAdj

AS

BEGIN
BEGIN TRAN
INSERT INTO dbo.tblInventoryAdjustments_Stage ( SystemID, BranchCode, DepartmentCode, adj, adj_desc,
adj_num, ClientCode, ProductCode, lot_stk_bk,
lot_stk_bk_Converted, qty_orig_tot, wght_orig, qty_tot,
wght_unt, dscp_resn, date_adj, date_adj_Converted,
HoldCode, qty_hold, WarehouseCode, loc, loc_co,
wght_type, amt_adj_rev, OperatorCode, UserAdd, AddDate,
UserEdit, EditDate, EditProcess, ActiveRecord )
SELECT Suspense.SystemID, Client.BranchCode, Client.DepartmentCode, adj, adj_desc, adj_num,
Suspense.ClientCode, ProductCode, lot_stk_bk, lot_stk_bk_Converted, qty_orig_tot, wght_orig, qty_tot, wght_unt, dscp_resn,
date_adj, date_adj_Converted, HoldCode, qty_hold, WarehouseCode, loc, loc_co, wght_type,
amt_adj_rev, OperatorCode, System_User, Getdate(), System_User, Getdate(),
'proc_Process_Suspense_InvAdj', 1
FROM dbo.tblInventoryAdjustments_Suspense Suspense
INNER JOIN Datawarehouse.dbo.tblKNLClient Client
ON Suspense.SystemID = Client.SystemID AND
Suspense.ClientCode = Client.ClientCode

IF @@Error <> 0
BEGIN
ROLLBACK TRAN
GOTO ON_Error
END

-- delete the inserted clients from the suspense table
DELETE Suspense
FROM tblInventoryAdjustments_Suspense Suspense, Datawarehouse.dbo.tblKNLClient Client
WHERE Suspense.SystemID = Client.SystemID AND
Suspense.ClientCode = Client.ClientCode

IF @@Error <> 0
BEGIN
ROLLBACK TRAN
GOTO ON_Error
END

-- execute the proc to append the new records from stage to production
EXECUTE dbo.proc_Apnd_Stage_To_InvAdj

IF @@Error <> 0
BEGIN
ROLLBACK TRAN
GOTO ON_Error
END

-- clear the stage table
DELETE tblInventoryAdjustments_Stage

IF @@Error <> 0
BEGIN
ROLLBACK TRAN
GOTO ON_Error
END

COMMIT TRAN

ON_Error:
END

GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-21 : 13:19:04

Nothing jumps out at me as to how to improve the performance of your stored procedure except to make sure that you have indexes in all of the right places. You need indexes on the join conditions (both the parent and child tables) for your INSERT statement. You need indexes to support the WHERE clause in your DELETE statement.

One thing that you might consider doing is using the inserted table from the trigger. I think that you are doing the same thing with your SELECT statement in the INSERT, but it would be faster to just use the inserted table from the trigger. For information about the inserted table, please see CREATE TRIGGER in BOL.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2004-04-21 : 14:02:32

How many rows are in the table,

What does SHOW PLAN show

How about you're indexws?

How do you do the Insert from Access?

Brett

8-)

rushdib
Yak Posting Veteran

93 Posts

Posted - 2004-04-22 : 09:18:25

Since that process is too complex to initiate from a trigger, we are going to execute the procedure from a button on the menu. It doesn't take long to run ( less than a minute), but it's too long when data entry is concerned.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources