日期:2014-05-18  浏览次数:20613 次

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.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


参考着这个写写吧

------解决方案--------------------
SQL code
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