<!-- Code highlighting produced by Actipro CodeHighlighter (freeware) http://www.CodeHighlighter.com/ -->
--假设b1为主键insert b(B1,B2) select A1,A2 from inserted i wherenotexists(select1from i,b where b.b1=i.a1)
<!-- Code highlighting produced by Actipro CodeHighlighter (freeware) http://www.CodeHighlighter.com/ -->
createtable a (a1 int,a2 int,a3 int,a4 int,a5 int) createtable b (b1 int,b2 int,b3 int,b4 int,b5 int) createtrigger trigger_a on a forinsertasbegininsertinto b(b1,b2,b5) select a1,a2,a5 from inserted i wherenotexists( select1from b where b1=i.a1 ) endinsertinto a select1,2,3,4,5insertinto a select1,1,1,1,1select*from a select*from b
<!-- Code highlighting produced by Actipro CodeHighlighter (freeware) http://www.CodeHighlighter.com/ -->
CreateTrigger InsertTrigger ON A FORINSERTASSET NOCOUNT ONINSERTINTO B(B1,B2,B5) SELECT A1,A2,A5 FROM INSERTED SET NOCOUNT OFFGO--> B表不需要触发器:B表的主键去掉,改为唯一性索引,忽略重复键。
<!-- Code highlighting produced by Actipro CodeHighlighter (freeware) http://www.CodeHighlighter.com/ -->
--在A上建一个触发器就行了。。插入时判断B中是否存在,存在就不插入了。不存在才插入。createtrigger trigger_a on a forinsertasinsertinto b(b1,b2,b5) select a1,a2,a5 from inserted i wherenotexists(select1from b where b1=i.a1)
<!-- Code highlighting produced by Actipro CodeHighlighter (freeware) http://www.CodeHighlighter.com/ -->
createtrigger trigI_B on B instead ofinsertas--假设B表主键为B1ifnotexists(select*from B,inserted where b.b1=inserted.b1) insertinto b (b1,b2,b3,b4,b5) select b1,b2,b3,b4,b5 from inserted
createtriggretr_A_insert onA forinsert as begin insertintoBselect字段1,字段2,字段3frominserted ---注意,这里最好是将要插入到表B中的字段名称都写出来,有可能两个表有自增列 end
createtriggertr_B_insert onB insteadinsert as begin declare@字段1类型,@字段2类型,@字段3类型... declarecur_bcursorforselect字段1,字段2,字段3...frominserted opencur_b fetchnextfromcur_binto@字段1,@字段2,@字段3... while(@@fetch_status=0) begin declare@chongfuint set@chongfu=0 select@chongfu=count(1)fromBbwhereb.字段1=@字段1andb.字段2=@字段.. if(@chongfu>0) insertintoBselect@字段1,@字段2,@字段3... fetchnextfromcur_binto@字段1,@字段2,@字段3... end closecur_b deallocatecur_b end