如何在三个表中创建计算列，如果我们改变一个，则自动进行另一个更改 [英] How to make computed column in three table and, if we change in one , another change automatically

问题描述

我有3张表

Collectoin | WorckForm | WorckFormData



1）Collecton：total_amount

2）WorckForm：total_amount，receive_amount，due_amount

3）WorckFormData ：total_amount，receive_amount，due_amount





1）我在收集表中插入总金额。

2 ）当我在每个交易中的数据到期时插入在worckformdata中。

3）我想要最终计算列并保留在worckform表中插入的金额



Ex：



收藏：

total_amount = 5000



worckformdata：

totalamount = 5000

receive_amount = 2000

due_amount = 3000



totalamount = 2000

receive_amount = 500

due_amount = 1500



worckform：

totalamount = 2000

receive_amount = 500

due_amount = 1500





如何我在这些列之间的关系或计算。

解决方

尝试触发器 - SQL Server [ ^ ]

你可以使用计算栏。



在这里你可以参考这个



create-computed-column-using-data-from-another-table

!!!嘿

让我们用事务写一个存储过程，这会影响两个表的付款栏

和WorkData表的到期金额。我建议你为你做另一个程序。

 CREATE TABLE [Collection] 
（
 ID int， 
 Name varchar（10），
 Payment int 
）; 
 
 
 CREATE TABLE WorkData 
（
 WDID int，
 Payment int，
 DueAmount int 
）; 
 
 INSERT INTO [Collection] VALUES（1，'A'，200）
 INSERT INTO [Collection] VALUES（2，'B'，500）
 INSERT INTO [Collection ] VALUES（3，'C'，300）
 
 
 INSERT INTO WorkData VALUES（1,200,100）
 INSERT INTO WorkData VALUES（2,500,150）
 INSERT INTO WorkData VALUES（3,300,200）
 
 select * from [Collection]; 
从WorkData中选择*; 
 
 
创建程序spUpdateDueAmount 
 @ID int，
 @DueAmount int 
 as 
 begin 
 ----检查到期金额
声明@duecheck int 
从WorkData选择@duecheck = DueAmount 
，其中WDID = @ID 
 
 ----如果到期超过那么错误消息生成
 if（@duecheck< @DueAmount）
 begin 
 print'错误：插入的金额超过到期金额'
结束
 ----如果足够的到期金额
 else 
 begin 
 begin tran 
 ----减少WrokData表的到期金额
更新工作数据集DueAmount =（DueAmount  -  @DueAmount）其中WDID = @ID 
更新工作数据集付款=（付款+ @DueAmount）其中WDID = @ID 
 
 ----更新收款表
更新[收款]设置付款=（付款+ @ DueAmount）其中ID = @ID 
 
 ----如果没有使用事务，那么集合tbl将更新
 ----但WorkData不会，我们不想要到
 commit tr 
打印'表格更新'
结束
 
结束; 
 
 
 ---- !!!我们试试吧 （*_*） ！！！ 
 
 ----它应该生成错误
 exec spUpdateDueAmount 1,5000; 
 
 ----它应该影响表
 exec spUpdateDueAmount 1,50; 

I have 3 Table
Collectoin | WorckForm | WorckFormData

1)Collecton: total_amount
2)WorckForm: total_amount, receive_amount, due_amount
3)WorckFormData:total_amount, receive_amount, due_amount


1) i insert in collection table, total amount.
2) when i due amount from data every transaction insert in worckformdata.
3) i want finally computed column and remain amount inserted in worckform table

Ex:

Collection:
total_amount=5000

worckformdata:
totalamount=5000
receive_amount=2000
due_amount=3000

totalamount=2000
receive_amount=500
due_amount=1500

worckform:
totalamount=2000
receive_amount=500
due_amount=1500


How i relation OR computed between these column.

解决方案

Try Triggers -- SQL Server[^]

You can use Computed column.

Here you may refer to this

create-computed-column-using-data-from-another-table

!!! Hey
Lets write a stored procedure with transaction, this will affect the payment column
of the two table and due amount of WorkData table. I recommend you to make an another procedure for you.

CREATE TABLE [Collection]
(
	ID		int,
    Name varchar(10),
    Payment int
);


CREATE TABLE WorkData
(
	WDID		int,
    Payment		int,
    DueAmount	int
);

INSERT INTO [Collection] VALUES(1, 'A', 200)
INSERT INTO [Collection] VALUES(2, 'B', 500)
INSERT INTO [Collection] VALUES(3, 'C', 300)


INSERT INTO WorkData VALUES(1, 200, 100)
INSERT INTO WorkData VALUES(2, 500, 150)
INSERT INTO WorkData VALUES(3, 300, 200)

select * from [Collection];
select * from WorkData;


create procedure spUpdateDueAmount
@ID int, 
@DueAmount int
as
begin
	---- Check the due amount
	declare @duecheck int
	select @duecheck = DueAmount 
	from WorkData where WDID = @ID
	
	---- If due exceeds then error message generation
	if (@duecheck < @DueAmount)
	begin
	print 'Error: Inserted amount exceeds due amount'
	end 
	---- If enough due amount
	else
		begin
		begin tran
		---- reduce the due amount from WrokData table
		Update WorkData set DueAmount = (DueAmount - @DueAmount) where WDID = @ID
		Update WorkData set Payment = (Payment + @DueAmount) where WDID = @ID
		
		---- Update the Collection table 
		Update [Collection] set Payment = (Payment + @DueAmount) where ID = @ID
		
		---- If transaction is not used then then collection tbl will be updated
		---- But WorkData will not, which we don't want to
		commit tran
		print 'table updated'
		end
	
end; 


---- !!! Lets Try (*_*) !!!

---- It should generate error 
exec spUpdateDueAmount 1,5000;

---- It should affect the tables
exec spUpdateDueAmount 1,50;

这篇关于如何在三个表中创建计算列，如果我们改变一个，则自动进行另一个更改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！