日期:2014-05-18 浏览次数:20794 次
create table RKD ( LSBH VARCHAR(50), PJLX VARCHAR(10), FLH VARCHAR(10), WLBH VARCHAR(30), SSSL VARCHAR(30),---入库单数量 RKDDLS VARCHAR(30), RKDDFLH VARCHAR(30) ) create table DD2 ( DDLSBH VARCHAR(50), DDFLH VARCHAR(10), DDWLBH VARCHAR(30), DDSL VARCHAR(30),---订单数量 ) /* 现在要做一个触发器,就是表RKD在插入或者修改记录时, 判断当RKD.PJLX='G'并且字段RKDDLS与RKDDFLH对应 DD2表中对应的DDLSBH 与DDFLH 时RKD.SSSL不能大于DD2.DDSL RKD.RKDDLS=DD2.DDLSBH RKD.RKDDFLH=DD2.DDLSBH */ go create trigger tri_in_up on RKD for insert,update as declare @LSBH VARCHAR(50), @PJLX VARCHAR(10), @FLH VARCHAR(10), @WLBH VARCHAR(30), @SSSL VARCHAR(30), @RKDDLS VARCHAR(30), @RKDDFLH VARCHAR(30) declare @DDLSBH VARCHAR(50), @DDFLH VARCHAR(10), @DDWLBH VARCHAR(30), @DDSL VARCHAR(30) if not exists(select *from deleted)--如果是新增 begin select @LSBH=LSBH,@PJLX=PJLX,@FLH=FLH, @WLBH=WLBH,@SSSL=SSSL,@RKDDLS=RKDDLS, @RKDDFLH=RKDDFLH from inserted select @DDLSBH=DDLSBH,@DDFLH=DDFLH, @DDWLBH=DDWLBH,@DDSL=DDSL from DD2 if @PJLX='G' and @RKDDLS=@DDLSBH and @RKDDFLH=@DDLSBH begin if @SSSL<=@DDSL insert into RKD values (@LSBH,@PJLX,@FLH,@WLBH,@SSSL,@RKDDLS,@RKDDFLH) else set @SSSL=@DDSL--大于的情况处理为等于 insert into RKD values (@LSBH,@PJLX,@FLH,@WLBH,@SSSL,@RKDDLS,@RKDDFLH) end end if(select count(*) from deleted)>0 and (select count(*) from inserted)>0--如果是更新 begin select @LSBH=LSBH,@PJLX=PJLX,@FLH=FLH, @WLBH=WLBH,@SSSL=SSSL,@RKDDLS=RKDDLS, @RKDDFLH=RKDDFLH from inserted select @DDLSBH=DDLSBH,@DDFLH=DDFLH, @DDWLBH=DDWLNH,@DDSL=DDSL from DD if @PJLX='G' and @RKDDLS=@DDLSBH and @RKDDFLH=@DDLSBH begin if @SSSL>@DDSL begin set @SSSL=@DDSL update RKD set SSSL=@SSSL where PJLX='G' end end end 参考着这个写写吧
------解决方案--------------------
create trigger my_trig on userlist for insert ,update ,delete as if not exists(select 1 from inserted) update type1 set pq_rs_zz = pq_rs_zz - isnull((select count(1) from deleted d where d.id = t.type1_id and d.zhiglx = '派遣工'),0), zz_rs_zz = zz_rs_zz - isnull((select count(1) from deleted d where d.id = t.type1_id and d.zhiglx = '正式工'),0) from type1 t else if not exists(select 1 from deleted) update type1 set pq_rs_zz = pq_rs_zz + isnull((select count(1) from deleted d where d.id = t.type1_id and d.zhiglx = '派遣工'),0), zz_rs_zz = zz_rs_zz + isnull((select count(1) from deleted d where d.id = t.type1_id and d.zhiglx = '正式工'),0) from type1 t else 这里比较麻烦,如果是type1_id 1 --> 2,则1需要减,2需要加,同样 zhiglx 由派遣工 --> 正式工 , 则。。。 go --也许这样比较合适 create trigger my_trig on userlist for insert ,update ,delete as begin update type1 set pq_rs_zz = pq_rs_zz - isnull((select count(1) from userlist u where u.id = t.type1_id and u.zhiglx = '派遣工'),0), zz_rs_zz = zz_rs_zz - isnull((select count(1) from userlist u where u.id = t.type1_id and u.zhiglx = '正式工'),0) from type1 t end