-- 创建收款触发器,当插入记录时自动添加实收款, 删除记录时自动减实收款;
-- insert触发器;
create trigger tgr_receipts_insert on [dindanbukuan] after insert as
begin
	declare @order_number nvarchar(50)			--订单号 id;
	declare @amount_received nvarchar(50)		--实收款 money;
	declare @receipt_item nvarchar(50)			--收款项目 item;
	declare @receipt_kind nvarchar(50)			--收款类型 kind;
	declare @receipt_2_sales nvarchar(50)		--收款二销 bz;
	declare @amount_old_received NVARCHAR(50)	--旧的实收款;
	--获取值;
	select @order_number = id, @amount_received = [money], @receipt_item = item, @receipt_kind = kind, @receipt_2_sales = bz from inserted
	if ( @receipt_kind = '3' or @receipt_kind = '4')	--订单收款;
		BEGIN
			--获取订单实收款;
			SELECT @amount_old_received = amount_received from dindan where id = @order_number
			--更新订单实收款;
			update dindan set amount_received = cast(cast(@amount_old_received as int) + cast(@amount_received as int) as nvarchar) where id = @order_number
		END
	ELSE	--加挑收款;
		BEGIN
			--获取加挑实收款;
			SELECT @amount_old_received = amount_received from dindanbukuan2 WHERE id = @order_number and bz = @receipt_item
			--更新加挑实收款;
			update dindanbukuan2 set amount_received = cast(cast(@amount_old_received as int) + cast(@amount_received as int) as nvarchar) where id = @order_number and bz = @receipt_item
		END
end
go

--delete触发器;
create trigger tgr_receipts_delete on [dindanbukuan] after delete as
begin
	declare @order_number nvarchar(50)		--订单号 id;
	declare @amount_received nvarchar(50)	--实收款 money;
	declare @receipt_item nvarchar(50)		--收款项目 item;
	declare @receipt_kind nvarchar(50)		--收款类型 kind;
	declare @receipt_2_sales nvarchar(50)	--收款二销;
	declare @amount_old_received NVARCHAR(50)	--旧的实收款;
	select @order_number = id, @amount_received = [money], @receipt_item = item, @receipt_kind = kind, @receipt_2_sales = bz from deleted
	if ( @receipt_kind = '3' or @receipt_kind = '4')	--订单收款;
		BEGIN
			--获取订单实收款;
			SELECT @amount_old_received = amount_received from dindan where id = @order_number
			--更新订单实收款;
			update dindan set amount_received =cast(cast(@amount_old_received as int) - cast(@amount_received as int) as nvarchar) where id = @order_number
		END
	ELSE	--加挑收款;
		BEGIN
			--获取加挑实收款;
			SELECT @amount_old_received = amount_received from dindanbukuan2 WHERE id = @order_number and bz = @receipt_item
			--更新加挑实收款;
			update dindanbukuan2 set amount_received = cast(cast(@amount_old_received as int) - cast(@amount_received as int) as nvarchar) where id = @order_number and bz = @receipt_item
		END
end
GO

--没有更新update触发器;