SQL SERVER触发器游标小记
今天接到个需求用触发器来实现通过条件对其他表的更新。好久没摸SQL SERVER,电脑里也没SQL SERVER安装包,同事遂发来个安装包,一看吓一跳,3.6G!!!!经过漫长等待后,开始作业。需求如下
1、 当a字段更新为2或者3,并且b字段更新为y的时候在新表Exchange插入该id、Q
2、 当a字段更新为3,且b字段更新为n的时候,在新表插入该表的id,a
代码如下
create trigger updateExange on [dbo].[EXAM_MASTER] after update as if(exists(select inserted.result_status,inserted.consultation_status from inserted where (inserted.result_status='2' or inserted.result_status='3') and consultation_status='y')) begin declare id_cursor1 cursor for select inserted.exam_id from inserted open id_cursor1 declare @exam_id int --@exam_id要与游标中的字段名相同 fetch next from id_cursor1 into @exam_id while @@FETCH_STATUS=0 begin insert into [dbo].[Exchange] (id,examid,mark) values(NEWID(),@exam_id,'Q') fetch next from id_cursor1 into @exam_id end close id_cursor1 deallocate id_cursor1 end if (exists(select inserted.result_status,inserted.consultation_status from inserted where inserted.result_status='3' and consultation_status='n')) begin declare id_cursor2 cursor for select inserted.exam_id from inserted open id_cursor2 fetch next from id_cursor2 into @exam_id while @@FETCH_STATUS=0 begin insert into [dbo].[Exchange] (id,examid,mark) values(NEWID(),@exam_id,'A') fetch next from id_cursor2 into @exam_id end close id_cursor2 deallocate id_cursor2 end
虽然不是最佳办法,但也算完成了任务。