今天接到个需求用触发器来实现通过条件对其他表的更新。好久没摸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
虽然不是最佳办法，但也算完成了任务。