sql触发器有关问题

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