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

 虽然不是最佳办法，但也算完成了任务。

转载于:https://www.cnblogs.com/wishbay/p/3388018.html