CSON_CSON大侠,谢谢您上次的回复,经测试很好使。
我又给hkb加了一个sj(水价),出了点问题,帮我好么?我改hkb的结构加上了sj(水价),然后就出现了以下问题:
当我发出以下命令时
delete xlb where zcm='100001' and xlrq='2008-03-22 00:00:000'
显示以下出错信息
插入错误: 列名或所提供值的数目与表定义不匹配。现在xlb和ssb都有sj这列(我忘了之前您写代码时是不是这样)。
希望您的触发器,在向hkb中插入行时,这回带上sj(sj取xlb中的,不取ssb中的,尽管可能它们总是相等的,我想以xlb为准)
我把您最后一次发的代码放下面:
--1.zt=' '的才参加触发器处理 zt <>' '的被忽略不计(引号中是空格一个)
--2.xlb中yx='1'的优先被还,ssb中yx='1'的优先用来还
-----------------
--收款表触发器
-----------------
CREATE trigger t_ssb on dbo.ssb for insert,delete,update
as
begin
--删除还款表中需要删除的。
delete a from hkb a,deleted b where a.zcm = b.zcm and a.skrq = b.skrq
--重新计算
--1、生成需要变更的客户
select distinct zcm into #zcm from (select zcm from inserted union select zcm from deleted) a --2、生成需要重新计算的xlb
select a.yx,a.zcm,a.xlrq,xlsl-isnull(hksl,0) xlsl into #xlb from
(select a.yx,a.zcm,xlrq,xlsl from xlb a,#zcm b where a.zcm = b.zcm and a.zt = ' ') a left join
(select a.zcm,xlrq,sum(hksl) hksl from hkb a,#zcm b where a.zcm = b.zcm group by a.zcm,xlrq) b
on a.zcm = b.zcm and a.xlrq = b.xlrq where xlsl-isnull(hksl,0)>0
--3、生成需要重新计算的ssb
select a.yx,a.zcm,a.skrq,sssl-isnull(hksl,0) sssl into #ssb from
(select a.yx,a.zcm,skrq,sssl from ssb a,#zcm b where a.zcm = b.zcm and a.zt =' ') a left join
(select a.zcm,skrq,sum(hksl) hksl from hkb a,#zcm b where a.zcm = b.zcm group by a.zcm,skrq) b
on a.zcm = b.zcm and a.skrq = b.skrq where sssl-isnull(hksl,0)>0
--按照还款规则插入还款表
insert hkb select zcm,xlrq,skrq,hksl from( select a.zcm,xlrq,skrq,hksl=case when (zxlsl-xlsl) <=(zsssl-sssl)
then case when zxlsl <=zsssl then zxlsl-(zsssl-sssl) else sssl end
else case when zxlsl <=zsssl then xlsl else zsssl-(zxlsl-xlsl) end end from
(select zcm,xlrq,xlsl,zxlsl=(select sum(xlsl) from #xlb where zcm = a.zcm
and ((yx = a.yx and xlrq <=a.xlrq) or (yx='1' and a.yx <>'1')))
from #xlb a)a,
(select zcm,skrq,sssl,zsssl=(select sum(sssl) from #ssb where zcm = a.zcm
and ((yx = a.yx and skrq <=a.skrq) or (yx='1' and a.yx <>'1')))
from #ssb a)b
where a.zcm = b.zcm and ((zxlsl>zsssl and zxlsl-zsssl <xlsl) or (zsssl>=zxlsl and zsssl-zxlsl <sssl)))c
end
我又给hkb加了一个sj(水价),出了点问题,帮我好么?我改hkb的结构加上了sj(水价),然后就出现了以下问题:
当我发出以下命令时
delete xlb where zcm='100001' and xlrq='2008-03-22 00:00:000'
显示以下出错信息
插入错误: 列名或所提供值的数目与表定义不匹配。现在xlb和ssb都有sj这列(我忘了之前您写代码时是不是这样)。
希望您的触发器,在向hkb中插入行时,这回带上sj(sj取xlb中的,不取ssb中的,尽管可能它们总是相等的,我想以xlb为准)
我把您最后一次发的代码放下面:
--1.zt=' '的才参加触发器处理 zt <>' '的被忽略不计(引号中是空格一个)
--2.xlb中yx='1'的优先被还,ssb中yx='1'的优先用来还
-----------------
--收款表触发器
-----------------
CREATE trigger t_ssb on dbo.ssb for insert,delete,update
as
begin
--删除还款表中需要删除的。
delete a from hkb a,deleted b where a.zcm = b.zcm and a.skrq = b.skrq
--重新计算
--1、生成需要变更的客户
select distinct zcm into #zcm from (select zcm from inserted union select zcm from deleted) a --2、生成需要重新计算的xlb
select a.yx,a.zcm,a.xlrq,xlsl-isnull(hksl,0) xlsl into #xlb from
(select a.yx,a.zcm,xlrq,xlsl from xlb a,#zcm b where a.zcm = b.zcm and a.zt = ' ') a left join
(select a.zcm,xlrq,sum(hksl) hksl from hkb a,#zcm b where a.zcm = b.zcm group by a.zcm,xlrq) b
on a.zcm = b.zcm and a.xlrq = b.xlrq where xlsl-isnull(hksl,0)>0
--3、生成需要重新计算的ssb
select a.yx,a.zcm,a.skrq,sssl-isnull(hksl,0) sssl into #ssb from
(select a.yx,a.zcm,skrq,sssl from ssb a,#zcm b where a.zcm = b.zcm and a.zt =' ') a left join
(select a.zcm,skrq,sum(hksl) hksl from hkb a,#zcm b where a.zcm = b.zcm group by a.zcm,skrq) b
on a.zcm = b.zcm and a.skrq = b.skrq where sssl-isnull(hksl,0)>0
--按照还款规则插入还款表
insert hkb select zcm,xlrq,skrq,hksl from( select a.zcm,xlrq,skrq,hksl=case when (zxlsl-xlsl) <=(zsssl-sssl)
then case when zxlsl <=zsssl then zxlsl-(zsssl-sssl) else sssl end
else case when zxlsl <=zsssl then xlsl else zsssl-(zxlsl-xlsl) end end from
(select zcm,xlrq,xlsl,zxlsl=(select sum(xlsl) from #xlb where zcm = a.zcm
and ((yx = a.yx and xlrq <=a.xlrq) or (yx='1' and a.yx <>'1')))
from #xlb a)a,
(select zcm,skrq,sssl,zsssl=(select sum(sssl) from #ssb where zcm = a.zcm
and ((yx = a.yx and skrq <=a.skrq) or (yx='1' and a.yx <>'1')))
from #ssb a)b
where a.zcm = b.zcm and ((zxlsl>zsssl and zxlsl-zsssl <xlsl) or (zsssl>=zxlsl and zsssl-zxlsl <sssl)))c
end
as
begin
--删除还款表中需要删除的。
delete a from hkb a,deleted b where a.zcm = b.zcm and a.skrq = b.skrq
--重新计算
--1、生成需要变更的客户
select distinct zcm into #zcm from (select zcm from inserted union select zcm from deleted) a --2、生成需要重新计算的xlb
select a.yx,a.zcm,a.xlrq,xlsl-isnull(hksl,0) xlsl, sj into #xlb from
(select a.yx,a.zcm,xlrq,xlsl, a.sj from xlb a,#zcm b where a.zcm = b.zcm and a.zt = ' ') a left join
(select a.zcm,xlrq,sum(hksl) hksl from hkb a,#zcm b where a.zcm = b.zcm group by a.zcm,xlrq) b
on a.zcm = b.zcm and a.xlrq = b.xlrq where xlsl-isnull(hksl,0)>0
--3、生成需要重新计算的ssb
select a.yx,a.zcm,a.skrq,sssl-isnull(hksl,0) sssl into #ssb from
(select a.yx,a.zcm,skrq,sssl from ssb a,#zcm b where a.zcm = b.zcm and a.zt =' ') a left join
(select a.zcm,skrq,sum(hksl) hksl from hkb a,#zcm b where a.zcm = b.zcm group by a.zcm,skrq) b
on a.zcm = b.zcm and a.skrq = b.skrq where sssl-isnull(hksl,0)>0
--按照还款规则插入还款表
insert hkb select zcm,xlrq,skrq,hksl,sj from( select a.zcm,xlrq,skrq,hksl=case when (zxlsl-xlsl) <=(zsssl-sssl)
then case when zxlsl <=zsssl then zxlsl-(zsssl-sssl) else sssl end
else case when zxlsl <=zsssl then xlsl else zsssl-(zxlsl-xlsl) end end,a.sj from
(select zcm,xlrq,xlsl,sj,zxlsl=(select sum(xlsl) from #xlb where zcm = a.zcm
and ((yx = a.yx and xlrq <=a.xlrq) or (yx='1' and a.yx <>'1')))
from #xlb a)a,
(select zcm,skrq,sssl,zsssl=(select sum(sssl) from #ssb where zcm = a.zcm
and ((yx = a.yx and skrq <=a.skrq) or (yx='1' and a.yx <>'1')))
from #ssb a)b
where a.zcm = b.zcm and ((zxlsl>zsssl and zxlsl-zsssl <xlsl) or (zsssl>=zxlsl and zsssl-zxlsl <sssl)))c
end
从你这几次提问来看,每次都只是增加一点内容,让人难以得到全貌,我想问题不外乎如下:
1、你对需求的把握不准确或者客户的需求不明确,我想应该把这些确定后再动手,这样在数据库设计和代码规划方面应该更优化一些。
2、需求是很明确的,但您没有把问题进行归纳,而是碰到一个问一个,没有自己思考和分析需求,我想您应该先思考一个解决方法,至少自己对问题的全貌要清楚。
3、你对需求非常明确,但希望一个问题一个问题解决,每次尝试自己解决,这样很好,不过注意尺度。
从您说的情况来看,您至少承担了软件设计和程序编写的工作,估计需求分析也可能是您在做,而前面两点也是作为需求分析和软件设计人员的大忌。
再次提醒,提问的方式可能还是需要注意,毕竟人的耐心是有限的,而且事隔几天又要查看原来的代码,这种事情毕竟不是让人轻松的事情,当然,我只是说明一下一般情况,至少现在我还是很有耐心帮您试着解决这些问题。两个地方要改:
1、
--2、生成需要重新计算的xlb
select a.sj,a.yx,a.zcm,a.xlrq,xlsl-isnull(hksl,0) xlsl into #xlb from
(select a.sj,a.yx,a.zcm,xlrq,xlsl from xlb a,#zcm b where a.zcm = b.zcm and a.zt = ' ') a left join 2、
--按照还款规则插入还款表
insert hkb(给出字段列表,注意顺序) select sj,zcm,xlrq,skrq,hksl from( select a.zcm,xlrq,skrq,hksl=case when (zxlsl-xlsl) <=(zsssl-sssl)
then case when zxlsl <=zsssl then zxlsl-(zsssl-sssl) else sssl end
else case when zxlsl <=zsssl then xlsl else zsssl-(zxlsl-xlsl) end end from
(select sj,zcm,xlrq,xlsl,zxlsl=(select sum(xlsl) from #xlb where zcm = a.zcm
insert hkb select zcm,xlrq,skrq,hksl from( select a.zcm,xlrq,skrq,hksl=case when (zxlsl-xlsl) <=(zsssl-sssl)
then case when zxlsl <=zsssl then zxlsl-(zsssl-sssl) else sssl end
else case when zxlsl <=zsssl then xlsl else zsssl-(zxlsl-xlsl) end end from
(select zcm,xlrq,xlsl,zxlsl=(select sum(xlsl) from #xlb where zcm = a.zcm
and ((yx = a.yx and xlrq <=a.xlrq) or (yx='1' and a.yx <>'1')))
from #xlb a)a,
(select zcm,skrq,sssl,zsssl=(select sum(sssl) from #ssb where zcm = a.zcm
and ((yx = a.yx and skrq <=a.skrq) or (yx='1' and a.yx <>'1')))
from #ssb a)b
where a.zcm = b.zcm and ((zxlsl>zsssl and zxlsl-zsssl <xlsl) or (zsssl>=zxlsl and zsssl-zxlsl <sssl)))c
end
由于hkb表的结构变化了,并且新增的列设为不能为Null,所以按照原来的插入会报上述错误。
(1)改sj为可为Null值;
(2)修改这段代码,重新设置Insert语句。
谢谢CSON_CSON的建议和回复
列出的三点对我很有指导意义
正如CSON_CSON所说的,我是一个人什么都干,我对于抓需求很不得法,我开发过的几个程序几乎都是编完了运行了才渐渐地吃透了需求,这其中既有客户自已也不知道自己想要什么的因素,也有我调查需求没有章法的因素。我将在另帖向兄弟们请教把握需求的办法。
我今后会更好地把握问题。