Cursors In triggers SOS!!! - 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

SQL Server Development (2000)

Cursors In triggers SOS!!!

Author

Topic

jch04
Starting Member

9 Posts

Posted - 2009-01-15 : 03:46:42

Hello to all community,

I need to improve the performance of the following trigger:

CREATE trigger [ATTENDANCE_INSUPDDEL] on [dbo].[ATTENDANCE]
for update,insert,delete
as
declare @r int
declare cur cursor for select distinct registration from inserted union select distinct registration from deleted
open cur
fetch next from cur into @r
while @@fetch_status=0
begin
exec sync_attendance @r
fetch next from cur into @r
end
close cur
deallocate cur

Any ideas to rewrite the trigger without cursors??

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2009-01-15 : 04:00:04

we would need to know/see what sync_attendance does

jch04
Starting Member

9 Posts

Posted - 2009-01-15 : 04:42:49

Yes you are right, below is the code of sync_attendance:

CREATE procedure [dbo].[sync_attendance] @registration int
as
declare @student int
declare @semester int
set @student=(select distinct student from registration where code=@registration)
set @semester=(select distinct semester from registration where code=@registration)
delete from _totalgrade where student=@student and semester=@semester
insert into _totalgrade select distinct * from fn_totalgrade(@student, @semester)
delete from _stud_progress where student=@student and psem>=@semester
insert into _stud_progress select distinct * from stud_progress where psem>=@semester and student=@student

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources