Trigger Looping Problem

[krotha]

Hi
I am trying to loop through several records in the trigger and insert into a table. It is working fine, if i update a table using SQL Query Analyzer. But when I update through VB Application it is not inserting any records into the table,trigger is firing, but it is not going into the loop.
Any body help me what I am doing wrong?

--Here is my trigger
alter TRIGGER t_update_year
ON constants
FOR UPDATE
AS
declare @project_id numeric
declare @building_id numeric

IF UPDATE(Base_Year)
BEGIN
set @project_id = (select project_id from Inserted)
END

declare c1 cursor for
select building_id
from building
where project_id=@project_id
and building_id in(27201,272155)

open c1
fetch next from c1 into @building_id
while @@FETCH_STATUS = 0
begin
insert into test1 values(@building_id)
fetch next from c1 into @building_id
end
close c1
deallocate c1

 

[jhall156]

I would comment out the trigger and see if you are able to insert into the table with your vb code. If not, then you probably have either a problem with your vb code or a permissions issue.

JHall

 

[SQLSister]

I don't suppose anybody ever mentioned to you that cursors are an extremely poor way to update records? They are even worse in triggers! They cause very poor performance. Also your trigger will not work if you have more than one record inserted at a time. Try something like:

IF UPDATE(Base_Year)
BEGIN
insert into test1 (building_id)
select building_id
from building join inserted
on building.projectID = inserted.project_id
where building_id in(27201,272155)
END