sql触发器有关问题

sql触发器问题
userlist 表入下
id | username | type1_id | zhiglx
1 | username1| 1 | 派遣工
2 | username2| 1 | 正式工
3 | username3| 2 | 派遣工
4 | username4| 2 | 派遣工

type1 表如下
id | name | pq_rs_zz（派遣工数量）| zz_rs_zz(正式工人数)
1 | 部门1| 1 | 1
2 | 部门2| 2 | 0

userlist表中的type1_id和type1中的id对应，当userlist更新时候触发器能自动更新type1表中的pq_rs_zz和zz_rs_zz
效果如上面表所示

[解决办法]

SQL code

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.DDSLRKD.RKDDLS=DD2.DDLSBH  RKD.RKDDFLH=DD2.DDLSBH*/gocreate trigger tri_in_up on RKDfor insert,updateasdeclare @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&lt;=@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 endif(select count(*) from deleted)&gt;0    and (select count(*) from inserted)&gt;0--如果是更新beginselect @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&gt;@DDSL          begin           set @SSSL=@DDSL          update RKD set SSSL=@SSSL where PJLX='G'          end      end end参考着这个写写吧
[解决办法]
SQL codecreate trigger my_trig on userlist for insert ,update ,deleteasif 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 telse 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 telse   这里比较麻烦，如果是type1_id 1 --&gt; 2，则1需要减，2需要加，同样 zhiglx 由派遣工 --&gt; 正式工 ， 则。。。go--也许这样比较合适create trigger my_trig on userlist for insert ,update ,deleteasbegin   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 tend

sql触发器有关问题

热点推荐