Need help: Slow SQL Server trigger

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)

Need help: Slow SQL Server trigger

Author

Topic

PaTRiCKDRD
Yak Posting Veteran

55 Posts

Posted - 2003-11-21 : 05:52:38

Hi guys! I am having a serious problem with a SQL Server 2000 trigger that I created. I work in a telecommunications company, our database is online and I wanted to create a trigger that would update the users' credit amount left from 0.28 and less to 0.00 (That's because we charge per minute and 0.28 or less is not enough for a call). My trigger is this:

create trigger update_tac_val
on dw_users
after update
as
if update(tac_val)
begin
update dw_users
set tac_val = '0.00'
from dw_users
join inserted
on inserted.tac_key = dw_users.tac_key
where dw_users.tac_val like '0.28' or dw_users.tac_val like '0.27'or dw_users.tac_val like '0.26' or dw_users.tac_val like '0.25' or dw_users.tac_val like '0.24'
or dw_users.tac_val like '0.23' or dw_users.tac_val like '0.22' or dw_users.tac_val like '0.21' or dw_users.tac_val like '0.20' or dw_users.tac_val like '0.19'
or dw_users.tac_val like '0.18' or dw_users.tac_val like '0.17' or dw_users.tac_val like '0.16' or dw_users.tac_val like '0.15' or dw_users.tac_val like '0.14'
or dw_users.tac_val like '0.13' or dw_users.tac_val like '0.12' or dw_users.tac_val like '0.11' or dw_users.tac_val like '0.10' or dw_users.tac_val like '0.09'
or dw_users.tac_val like '0.08' or dw_users.tac_val like '0.07' or dw_users.tac_val like '0.06' or dw_users.tac_val like '0.05' or dw_users.tac_val like '0.04'
or dw_users.tac_val like '0.03' or dw_users.tac_val like '0.02' or dw_users.tac_val like '0.01'
end

tac_val is a text field that's why I have to go through each one separetely.
The problem is that, since our database is online (but only 30 users maximum can connect at any time), the trigger turns out to be very slow, causing a perfomance drag in SQL Server. What could be the solution in this issue? Maybe a stored procedure that would run at scheduled times?
I use SQL Server 2000 Service Pack 2 on a Windows 2000 Server machine.
If you have any suggestions, please let me know. I would appreciate it very much.
Thanks in advance!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-21 : 13:26:00

Duplicate thread:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=30769

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources