sql 触发器

[_ShuMin]

use rms_db_2005
IF (OBJECT_ID('trg_SumPaid', 'TR') IS NOT NULL)
DROP TRIGGER trg_SumPaid;
GO
create trigger [trg_SumPaid] on [dbo].[bi_bill]
after insert
as

declare @单号 varchar (50)


--折扣率0.834


select @单号=BillID
from inserted
where BillID in (select BillID from bi_BillPayment where SubjectID='10102' )

if @单号 is not null

begin
update bi_billItem
set SumOfConsume =SumOfConsume,SumForDiscount=SumOfConsume*(1-0.834)
where BillID=@单号

update bi_bill
set Remark ='update SumForDiscount=SumForDiscount+SumOfConsume*(1-0.834)',
SumForDiscount=SumForDiscount+SumOfConsume*(1-0.834),
DiscountRate=0.834,DiscountName='会员折',DiscountID='99',
---------------------------------------------------------
--以下这不起作用，SumTopay，SumPaid两列值没有变化
SumTopay=round((select SumOfConsume+SumOfService-SumForDiscount from bi_bill where BillID=@单号 ),0),

SumPaid=round((select SumOfConsume+SumOfService-SumForDiscount from bi_bill where BillID=@单号 ),0)

where BillID=@单号

---------------------------------------------------------

update bi_billPayment
set Sumpaid=round((Sumpaid*0.834),0),
PaySum=round((PaySum*0.834),0)
where BillID=@单号
end

[中国风]

SELECT SumOfConsume+SumOfService-SumForDiscount
                       FROM bi_bill
                       WHERE BillID=@单号

----自己调试一下这段是否没有值或有NULL值

[吉普赛的歌]

USE rms_db_2005
GO
IF (OBJECT_ID('trg_SumPaid', 'TR') IS NOT NULL)
    DROP TRIGGER trg_SumPaid;
GO
CREATE TRIGGER [trg_SumPaid]
ON [dbo].[bi_bill] 
   AFTER INSERT
AS
	DECLARE @单号 VARCHAR(50)
	--折扣率0.834
	SELECT @单号 = BillID
	FROM   INSERTED
	WHERE  BillID IN (SELECT BillID
	                  FROM   bi_BillPayment
	                  WHERE  SubjectID = '10102')
	
	IF @单号 IS NOT NULL
	BEGIN
	    UPDATE bi_billItem
	    SET    SumOfConsume = SumOfConsume,
	           SumForDiscount = SumOfConsume * (1 -0.834)
	    WHERE  BillID = @单号  
	    
	    --------------------- 增加一段输出 --------------------------------
	    --用于帮助判断问题所在
	    SELECT 
			SumTopay	AS [SumTopay原]
			,SumPaid    AS [SumPaid原]
			,ROUND(
	               (
	                   SELECT SumOfConsume + SumOfService -SumForDiscount
	                   FROM   bi_bill
	                   WHERE  BillID = @单号
	               ),
	               0
			)          AS [SumTopay希望修改值]
			,ROUND(
	               (
	                   SELECT SumOfConsume + SumOfService -SumForDiscount
	                   FROM   bi_bill
	                   WHERE  BillID = @单号
	               ),0) AS [SumPaid希望修改值]
	    FROM bi_bill
	    WHERE  BillID = @单号 
	    --------------------------- END ----------------------------------
	    
	    UPDATE bi_bill
	    SET    Remark = 
	           'update SumForDiscount=SumForDiscount+SumOfConsume*(1-0.834)',
	           SumForDiscount = SumForDiscount + SumOfConsume * (1 -0.834),
	           DiscountRate = 0.834,
	           DiscountName = '会员折',
	           DiscountID = '99',
	           ---------------------------------------------------------
	           --以下这不起作用，SumTopay，SumPaid两列值没有变化
	           SumTopay = ROUND(
	               (
	                   SELECT SumOfConsume + SumOfService -SumForDiscount
	                   FROM   bi_bill
	                   WHERE  BillID = @单号
	               ),
	               0
	           ),
	           SumPaid = ROUND(
	               (
	                   SELECT SumOfConsume + SumOfService -SumForDiscount
	                   FROM   bi_bill
	                   WHERE  BillID = @单号
	               ),
	               0
	           )
	    WHERE  BillID = @单号
	    
	    ---------------------------------------------------------
	    
	    UPDATE bi_billPayment
	    SET    Sumpaid     = ROUND((Sumpaid * 0.834), 0),
	           PaySum      = ROUND((PaySum * 0.834), 0)
	    WHERE  BillID      = @单号
	END

毕竟是你的业务， 其它人不了解。 这个先修改了运行看一下，也许你自己很快就能发现问题的了。