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

手动测试触发器可以执行,但是在程序执行中没有反应
CREATE TRIGGER UP_CUSFWSTORE ON [dbo].[CUSFWSTORE] 
FOR INSERT, UPDATE, DELETE 
AS

declare cur_cusfw_id cursor 
for 
select cusno,svno,num,jstype,fwprice from cusfwstore where id in (select id from inserted) order by id
--从一个表里取数
open cur_cusfw_id

declare @ts_cusno char(10),@ts_svno char(10),@li_num int,@ts_jstype char(10),@d_fwprice decimal(8,2)
fetch from cur_cusfw_id into @ts_cusno,@ts_svno,@li_num,@ts_jstype,@d_fwprice
--以下测试结算方式为卡内赠送
if @ts_jstype='卡内赠送'

--定义游标读取cuskkstore中zsnum数据,并做判断
--读取该行ID并根据ID修改该行值
begin
declare cur_zsnum cursor
for
select zsnum,autoid from cuskkstore where cusno like @ts_cusno and zsfwpro like @ts_svno
declare @tmp_num int, @li_id int,@end_num int

open cur_zsnum
fetch from cur_zsnum into @tmp_num,@li_id
set @end_num = @tmp_num-@li_num
if @end_num >= 0
update cuskkstore set zsnum = @end_num where autoid=@li_id
else
while @end_num < 0
  begin
  update cuskkstore set zsnum=0 where autoid=@li_id
  fetch from cur_zsnum into @tmp_num,@li_id
  set @end_num = @tmp_num+@end_num
  if @end_num >=0 
  update cuskkstore set zsnum = @end_num where autoid=@li_id
  break
  end
deallocate cur_zsnum
--deallocate cur_cusfw_id
end

else
if @ts_jstype='卡内结算'
begin
declare cur_price cursor
for
select kkprice,id from cuskkstore where cusno = @ts_cusno 

declare @d_price decimal(8,2),
  @ii_id int,
  @tmp_price decimal(8,2),
  @end_price decimal(8,2)

open cur_price
fetch from cur_price into @d_price,@ii_id
set @end_price = @d_price-@d_fwprice
if @end_price >= 0
update cuskkstore set kkprice = @end_price where id = @ii_id

--第一行结算金额小于本次消费金额
while @end_price < 0
  begin
  update cuskkstore set kkprice= 0 where id= @ii_id
  fetch from cur_price into @d_price,@ii_id
  set @end_price=@end_price+@d_price
  if @end_price>=0
  update cuskkstore set kkprice=@end_price where id = @ii_id
  break
  end

deallocate cur_price
end

else 
return

--读取下一行
--关闭游标

我测试了卡内赠送即if中的第一个条件,采取简单的方式可以执行,采取上面的方式手动也可以执行.但是放在程序中执行无反应(前台程序为PB,我写了存储过程总是提示造型无效的说明所以改为触发器,).请高手指教上面的触发器有什么问题?








------解决方案--------------------
游标看着晕 帮顶
------解决方案--------------------
晕乎乎地帮顶
------解决方案--------------------
介与你排版 和结贴率 
我围观。。

一个下午累了