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 updateexangeon [dbo].[exam_master]after update asif(exists(select inserted.result_status,inserted.consultation_status from inserted where(inserted.result_status='2' or inserted.result_status='3') and consultation_status='y'))begindeclare id_cursor1 cursor for select inserted.exam_id from insertedopen id_cursor1declare @exam_id int--@exam_id要与游标中的字段名相同fetch next from id_cursor1 into @exam_idwhile @@fetch_status=0begininsert into [dbo].[exchange] (id,examid,mark) values(newid(),@exam_id,'q')fetch next from id_cursor1 into @exam_idendclose id_cursor1deallocate id_cursor1endif (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 insertedopen id_cursor2fetch next from id_cursor2 into @exam_idwhile @@fetch_status=0begininsert into [dbo].[exchange] (id,examid,mark) values(newid(),@exam_id,'a')fetch next from id_cursor2 into @exam_idendclose id_cursor2deallocate id_cursor2end
虽然不是最佳办法，，但也算完成了任务。
posted on