| sp_helptext tu_retail
查询出来结果复制如下
CREATE TRIGGER [Tu_retail] ON dbo.d_retail
FOR UPDATE
AS
/*--//零售单*/
begin
declare
@numrows int,
@numnull int,
@errno int,
@errmsg varchar(255),
@inser int,
@dele int,
@inser1 int,
@dele1 int,
@retailid varchar(22),
@oldretailid varchar(22),
@suredate varchar(10),
@chkdate varchar(10),
@xsdate varchar(10),
@dzcnt int,
@depotid varchar(8),
@djsums decimal(14,2),
@sure int,
@retaildjid varchar(22),
@tmpdjsums DECIMAL(14,2)
select @numrows = @@rowcount
if @numrows = 0
return
/*2003-07-24 财务审核qdy*/
if update(cwchk) or update(cwpz) or update(js_sure) OR UPDATE(vipcode)
return
select @depotid=depotid,@retailid=retailid,@oldretailid=oldretailid from inserted
/*审核*/
if update(chk_sure)
begin
if update(retailid) or update(retail) or update(depotid) or update(vipcode)
or update(sure) or update(setdate) or update(daystat) or update(discount)
or update(s_sums) or update(z_sums) or update(s_name) or update(x_name)
or update(chk_name) or update(chk_date) or update(sure_date) or update(sure_name)
or update(cr_date) or update(comment) or update(crdate) or update(cr_name)
begin
raiserror 130100 '不可以同时修改审核和其他内容!'
rollback transaction
return
end
/*by odin 2010-03-07 审核日期也控制跟销售日期一致 ERP：7810*/
/*登帐日期必须与销售日期一致*/
select @chkdate=convert(char(10),i1.chk_date,126),@xsdate=convert(char(10),i1.setdate,126)
from inserted i1 inner join deleted d1 on i1.retailid=d1.retailid where i1.chk_sure<>d1.chk_sure and i1.chk_sure=1
if @chkdate<>@xsdate
begin
raiserror 130100 '审核日期必须与销售日期一致！'
rollback transaction
return
end
/*--//添加表中没有的款式记录*/
set nocount on
insert into j_stock (depotid,clothingid,styleid,colorid,sizeid)
select DISTINCT i1.depotid,t1.clothingid,t1.styleid,t1.colorid,t1.sizeid
from d_retailsub t1 INNER JOIN inserted i1 on t1.retailid=i1.retailid
left join j_stock c on i1.depotid=c.depotid and t1.clothingid=c.clothingid
where isnull(t1.clothingid,'')<>'' and c.depotid is null
/* 如果是没有收款记录的，插入一条默认的现金记录 sam on 2009-05-19 23:38:55 */
if exists(select 1 from inserted i where i.chk_sure=1)
begin
/*update a set a.sums=s.Fssums from pos_skfs a,inserted i,*/
/* (select m.retailid,sum(m.s_price*m.nums) as Fssums from d_retailsub m, inserted n */
/* where m.retailid=n.retailid group by m.retailid) s */
/* where a.retailid=i.retailid and i.retailid=s.retailid and i.chk_sure>0 and a.comment='自动'*/
insert into pos_skfs(retailid, retype, sums, crtype, comment)
select i.retailid, '现金', sum(s.s_price*s.nums), '人民币', '自动'
from d_retailsub s inner join inserted i on i.retailid=s.retailid
where i.chk_sure>0
and not exists(select 1 from pos_skfs p where p.retailid=i.retailid)
group by i.retailid
end
set nocount off
return
end
/*--//登账的单据不可以修改*/
set @numrows=(select count(*) from deleted where isnull(sure,0)>0)
if @numrows>0
if (((substring(COLUMNS_UPDATED(),1,1)>0)
and (substring(COLUMNS_UPDATED(),1,1)<>power(2,(5-1))))
or (substring(COLUMNS_UPDATED(),2,1)>0))
begin
raiserror 130100 '登账的单据不可以修改!'
rollback transaction
return
end
if update(Sure)
begin
/*只允许单张单登帐*/
select @dzcnt=count(retailid) from inserted
if @dzcnt>1
begin
raiserror 130100 '只允许单张零售单登帐!'
rollback transaction
return
end
/*登帐日期*/
if exists (select 1 from inserted where isnull(sure_date,'')='')
begin
raiserror 130100 '登帐日期不可以为空值！'
rollback transaction
return
end
if not exists(select 1 from d_retailsub where [email=retailid=@retailid]retailid=@retailid[/email])
begin
raiserror 130100 '零售单没有明细，登帐失败！'
rollback transaction
return
end
/*登帐日期必须与销售日期一致*/
select @suredate=convert(char(10),i1.sure_date,126),@xsdate=convert(char(10),i1.setdate,126)
from inserted i1 inner join deleted d1
on i1.retailid=d1.retailid where i1.sure<>d1.sure and i1.sure=1
if @suredate<>@xsdate
begin
raiserror 130100 '登帐日期必须与销售日期一致！'
rollback transaction
return
end
/*2004-05-15 qdy 锁帐*/
if (dbo.t18_getzmrq((select min(Sure_Date) from deleted ))=0) or
exists (select 1 from j_depot where @suredate<=zmrqdate and [email=depotid=@depotid]depotid=@depotid[/email])
begin
raiserror 130100 '帐目已经锁定，不可以进行此操作！'
rollback transaction
return
end
/*判断最小登帐日期之后是否有盘点单 by odin 2011-11-23 盘点审核后即判断*/
set nocount on
declare @cnt int,@minsuredate varchar(12)
declare @a1 table (depotid varchar(8),mindate datetime)
declare @a2 table (depotid varchar(8),mindate datetime)
insert into @a1
select depotid,min(sure_date) as mindate from inserted group by depotid
insert into @a2
select a.depotid,max(a.chk_date) as mindate
from d_inventory a inner join @a1 b on a.depotid=b.depotid
where isnull(a.chk_sure,0)>0
--and substring(a.inventoryid,1,2)='PD'
and a.inventoryid like 'PD%'
group by a.depotid
select @cnt=count(a.depotid) from @a1 a inner join @a2 b on a.depotid=b.depotid
where convert(char(10),a.mindate,126)<=convert(char(10),b.mindate,126)
set nocount off
if @cnt>0
begin
raiserror 130100 '登帐日期之前已经有盘点单审核了，所以不能在这天登帐或反登帐。或者取消已经在这天登帐的盘点单!'
rollback transaction
return
end
set @inser=(select count(*) from inserted where (isnull(cardid,'')<>'' or isnull(bank_recodes,'')<>'') and isnull(sure,0)<1)
set @dele=(select count(*) from deleted where (isnull(cardid,'')<>'' or isnull(bank_recodes,'')<>'') and isnull(sure,0)>0)
if (@inser>0) and (@dele>0)
begin
raiserror 130100 '刷(充值卡\银行卡)后不可反登帐!'
rollback transaction
return
end
/*优化了抵值券或现金券后不可反登帐检查*/
set @inser1=(select count(*) from inserted i inner join pos_skfs p on i.retailid=p.retailid
where isnull(i.sure,0)<1 and isnull(p.sums,0)<>0 and (isnull(retype,'')='抵值券' or isnull(retype,'')='现金券'))
set @dele1=(select count(*) from deleted i inner join pos_skfs p on i.retailid=p.retailid
where isnull(p.sums,0)<>0 and isnull(i.sure,0)>0 and (isnull(retype,'')='抵值券' or isnull(retype,'')='现金券'))
if (@inser1>0) and (@dele1>0)
begin
raiserror 130100 '使用抵值券或现金券后不可反登帐!'
rollback transaction
return
end
--订金
select @djsums=sums,@retaildjid=cardid from pos_skfs where [email=retailid=@retailid]retailid=@retailid[/email] and retype='订金支付'
SELECT @tmpdjsums = SUM(usesums) FROM dbo.d_retaildjuse WHERE retailid [email==@retailid]=@retailid[/email]
IF isnull(@djsums,0)<> isnull(@tmpdjsums,0)
BEGIN
raiserror 130100 '订金金额记录表与收款方式金额不一致，请重试或重新做单!'
rollback transaction
return
END
/*判断是否有订金*/
if isnull(@djsums,0)<> 0
begin
set nocount on
select @sure=isnull(sure,0) from inserted
if @sure=1
begin
--update d_retaildj
--set usesums=isnull(usesums,0)+isnull(b.sums,0),
--ov_sure=case when a.djprice-isnull(usesums,0)-isnull(b.sums,0)=0 then 1 else 0 end,
--autosure=case when a.djprice-isnull(usesums,0)-isnull(b.sums,0)=0 then 1 else 0 end
--from d_retaildj a
--inner join pos_skfs b on a.retaildjid=b.cardid
--where [email=b.retailid=@retailid]b.retailid=@retailid[/email]
--and b.retype='订金支付'
UPDATE a SET
a.usesums=isnull(a.usesums,0)+isnull(b.usesums,0),
a.ov_sure=case when a.djprice-isnull(a.usesums,0)-isnull(b.usesums,0)=0 then 1 else 0 end,
a.autosure=case when a.djprice-isnull(a.usesums,0)-isnull(b.usesums,0)=0 then 1 else 0 end
FROM d_retaildj a INNER JOIN dbo.d_retaildjUse b ON a.retaildjid=b.retaildjid
WHERE b.retailid = @retailid
end
else
begin
--update d_retaildj
--set usesums=isnull(usesums,0)-isnull(b.sums,0),
--ov_sure=case when a.djprice-isnull(usesums,0)+isnull(b.sums,0)>0 then 0 else 1 end,
--autosure=case when a.djprice-isnull(usesums,0)+isnull(b.sums,0)>0 then 0 else 1 end
--from d_retaildj a
--inner join pos_skfs b on a.retaildjid=b.cardid
--where [email=b.retailid=@retailid]b.retailid=@retailid[/email]
--and b.retype='订金支付'
UPDATE a SET
a.usesums=isnull(a.usesums,0)-isnull(b.usesums,0),
a.ov_sure=case when a.djprice-isnull(a.usesums,0)+isnull(b.usesums,0)>0 then 0 else 1 end,
a.autosure=case when a.djprice-isnull(a.usesums,0)+isnull(b.usesums,0)>0 then 0 else 1 end
FROM d_retaildj a INNER JOIN dbo.d_retaildjUse b ON a.retaildjid=b.retaildjid
WHERE b.retailid = @retailid
end
--if exists(select 1 from d_retaildj where [email=retaildjid=@retaildjid]retaildjid=@retaildjid[/email] and isnull(usesums,0)>isnull(djprice,0))
if exists( SELECT TOP 1 1 from d_retaildj a INNER JOIN d_retaildjUse b ON a.retaildjid=b.retaildjid
where [email=b.retailid=@retailid]b.retailid=@retailid[/email] and isnull(a.usesums,0)>isnull(a.djprice,0))
begin
raiserror 130100 '订金支付不能大于剩余订金!'
rollback transaction
return
end
end
/*计算库存*/
/*登帐*/
set nocount on
declare @curinsert table (depotid varchar(8),clothingid varchar(32),sure int,nums int)
insert into @curinsert
select i1.depotid,t1.clothingid,i1.sure,sum(isnull(t1.nums,0)) as nums
from d_retailsub t1 inner join inserted i1 on t1.retailid=i1.retailid
inner join deleted d1 on i1.retailid=d1.retailid
where isnull(i1.sure,0)<>isnull(d1.sure,0)
group by i1.depotid,t1.clothingid,i1.sure
update u1 set k_num=isnull(k_num,0) - nums,p_num=isnull(p_num,0) - nums,ls_nums=isnull(ls_nums,0) + nums,lastdate=getdate()
from j_stock u1 inner join @curinsert i1 on u1.clothingid=i1.clothingid and u1.depotid=i1.depotid where isnull(i1.sure,0)=1
update u1 set k_num=isnull(k_num,0) + nums,p_num=isnull(p_num,0) + nums,ls_nums=isnull(ls_nums,0) - nums,lastdate=getdate()
from j_stock u1 inner join @curinsert i1 on u1.clothingid=i1.clothingid and u1.depotid=i1.depotid where isnull(i1.sure,0)=0
/*如果有单退货 处理 原单退货已退数量*/
if isnull(@oldretailid,'')<>''
begin
declare @retinsert table (clothingid varchar(32),sure int,nums int)
insert into @retinsert
select t1.clothingid,i1.sure,sum(isnull(t1.nums,0)) as nums
from d_retailsub t1 inner join inserted i1 on t1.retailid=i1.retailid
inner join deleted d1 on i1.retailid=d1.retailid
where isnull(i1.sure,0)<>isnull(d1.sure,0)
group by t1.clothingid,i1.sure
update ul
set ret_nums=isnull(ret_nums,0)-i1.nums
from d_retailsub ul inner join @retinsert i1
on ul.clothingid=i1.clothingid
and [email=ul.retailid=@oldretailid]ul.retailid=@oldretailid[/email]
where isnull(i1.sure,0)=1
update ul
set ret_nums=isnull(ret_nums,0)+i1.nums
from d_retailsub ul inner join @retinsert i1
on ul.clothingid=i1.clothingid
and [email=ul.retailid=@oldretailid]ul.retailid=@oldretailid[/email]
where isnull(i1.sure,0)=0
end
/*设置商场扣点*/
update u1 set u1.scale=dpt.scale
from d_retailsub u1 inner join inserted i1 on u1.retailid=i1.retailid
inner join j_depot dpt on i1.depotid=dpt.depotid
update u1 set u1.rate=dpt.scale
from inserted i1 inner join pos_skfs u1 on u1.retailid=i1.retailid
inner join j_depot dpt on i1.depotid=dpt.depotid
/*计算余额*/
/**应收帐的可用余额**/
declare @curzySur11 table (depotid varchar(8),sure int,sums numeric(12,2))
insert into @curzySur11
select i1.depotid ,i1.sure,sum(isnull(t1.s_price*t1.nums,0)) as sums
from d_retailsub t1 inner join inserted i1 on t1.retailId =i1.retailId
inner join deleted d1 on i1.retailId = d1.retailId
where (isnull(i1.sure,0) != isnull(d1.sure,0))
group by i1.depotid,i1.sure
update j_depot set sums = isnull(u1.Sums,0) + isnull(t1.sums,0)
from @Curzysur11 t1 inner join j_depot u1 on u1.depotId =t1.depotId where isnull(t1.sure,0)=1
update j_depot set sums = isnull(u1.Sums,0) + isnull(t1.sums,0)
from @Curzysur11 t1 inner join j_depot u1 on u1.depotId =t1.depotId where isnull(t1.sure,0)=0
update s1 set s1.Sure_datesub=i1.Sure_date from d_retailsub s1 inner join inserted i1
on s1.retailId=i1.retailId
update sys_system set comment=convert(char(10),@suredate,126) where systemid=1005 and convert(char(10),@suredate,126)<comment
--更新最小销售日期
if exists (select 1 from sysobjects where id=object_id('sys_SynDate') and type='U')
update sys_SynDate set [email=MinDate=@suredate]MinDate=@suredate[/email] where Dtype=0 and cast(@suredate as date)<MinDate
set nocount off
end
end
修改后 而是先drop 然后create |
