First-in-first-out payment processing trigger

There are two tables: Table 1: Borrowing
Name Date borrow Ticket No. The amount has been paid and the balance has not been paid
Zhang San 99-1-1 000001 10000 0 10000
Li Si 99-1-2 000002 5000 0 5000
Zhang San 99-2-1 000003 6000 0 6000
Zhang San 99-3-1 000004 10000 0 15000
...................
Table 2: Repayment
Name Date Repayment Order No. repayment amount

When Table 2 inputs a document, the "amount already paid" and "remaining balance" in Table 1 changes accordingly based on the input "repayment amount" in table 2. That is:
Table 2: Repayment
Name Date Repayment Order No. repayment amount
Zhang San 99-4-3 000001 13000
 
Table 1: debit
Name Date borrow Ticket No. The amount has been paid and the balance has not been paid
Zhang San 99-1-1 000001 10000 10000 0
Li Si 99-1-2 000002 5000 0 5000
Zhang San 99-2-1 000003 6000 3000 3000
Zhang San 99-3-1 000004 10000 0 15000
...................
When table 2 is input again:
Name Date Repayment Order No. repayment amount
Zhang San 99-4-3 000001 13000
Zhang San 99-4-3 000002 8000
Table 1 is:
Table 1: debit
Name Date borrow Ticket No. The amount has been paid and the balance has not been paid
Zhang San 99-1-1 000001 10000 10000 0
Li Si 99-1-2 000002 5000 0 5000
Zhang San 99-2-1 000003 6000 6000 0
Zhang San 99-3-1 000004 15000 5000 10000
...................

 

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

-- Test

-- Test Data
Create Table 1 (name varchar (10), date datetime, Debit ticket number char (6), debit amount int, amount returned int, amount unpaid INT)
Insert table 1 select 'zhang san', '99-1-1 ', '20170901', values, 0, 000001
Union all select 'Li si', '99-1-2 ', '20170901', 000002, 0, 5000
Union all select 'zhang san', '99-2-1 ', '123', 000003, 0, 6000
Union all select 'zhang san', '99-3-1 ', '123', 15000,0, 000004 -- the original data of the landlord should be incorrect.

Create Table 2 (name varchar (10), date datetime, Repayment Order Number char (6), repayment amount INT)
Go

-- Processed trigger
Create trigger tr_insert on table 2
For insert
As
Update a set
Paid amount = case when (
Select sum (case when paid amount> 0 then unpaid balance else borrowed amount end)
From table 1
Where unpaid balance> 0 and name = A. Name and debit card number <= A. debit card number
)> A. Unpaid balance then B. repayment amount-isnull ((
Select sum (case when paid amount> 0 then unpaid balance else borrowed amount end)
From table 1
Where unpaid balance> 0 and name = A. Name and Debit ticket number), 0) + isnull (A. Paid, 0)
Else A. debit amount end
, Unpaid balance = case when (
Select sum (case when paid amount> 0 then unpaid balance else borrowed amount end)
From table 1
Where unpaid balance> 0 and name = A. Name and debit card number <= A. debit card number
)> A. Unpaid balance then A. debit amount-(B. repayment amount-isnull ((
Select sum (case when paid amount> 0 then unpaid balance else borrowed amount end)
From table 1
Where unpaid balance> 0 and name = A. Name and Debit ticket number), 0) + isnull (A. Paid, 0 ))
Else 0 end
From table 1 A, inserted B
Where a. Unpaid balance> 0 and A. Name = B. Name and isnull ((
Select sum (case when paid amount> 0 then unpaid balance else borrowed amount end)
From table 1
Where unpaid balance> 0 and name = A. Name and Debit ticket number), 0) <= B. repayment amount
Go

-- Insert Record Test
Insert table 2 select 'zhang san', '99-4-3 ', '123', 000001
-- Display the processing result
Select * from table 1
Select * from table 2

-- Insert Record Test
Insert table 2 select 'zhang san', '99-4-3 ', '123', 000002
-- Display the processing result
Select * from table 1
Select * from table 2
Go

-- Delete test
Drop Table 1, table 2

/* -- Test Result

-- The update result of the First insert record:

Name Date borrow Ticket No. The amount has been paid and the balance has not been paid
--------------------------------------------------------------------
Zhang San 00:00:00. 000 000001 10000 10000 0
Li Si 00:00:00. 000 000002 5000 0 5000
Zhang San 00:00:00. 000 000003 6000 3000 3000
Zhang San 00:00:00. 000 000004 15000 0 15000

(The number of affected rows is 4)

Name Date Repayment Order No. repayment amount
---------------------------------------------------
Zhang San 00:00:00. 000 000001 13000

(The number of affected rows is 1)

 

-- Result of the second insert:

Name Date borrow Ticket No. The amount has been paid and the balance has not been paid
-------------------------------------------------------------------
Zhang San 00:00:00. 000 000001 10000 10000 0
Li Si 00:00:00. 000 000002 5000 0 5000
Zhang San 00:00:00. 000 000003 6000 6000 0
Zhang San 00:00:00. 000 000004 15000 5000 10000

(The number of affected rows is 4)

Name Date Repayment Order No. repayment amount
---------------------------------------------------
Zhang San 00:00:00. 000 000001 13000
Zhang San 00:00:00. 000 000002 8000

(The number of affected rows is 2)

--*/