There are two tables: Table 1: Date of borrowing names The amount of borrowing borrowing number has not yet returned to Zhang San 99-1-1 000001 10000 0 10000 Li Si 99-1-2 000002 5000 0 5000 Zhang San 99-2-2- 1 000003 6000 0 6000 sheets 99-3-1 000004 10000 0 15000 ... ... ... .. ..... Table 2: Repayment name date Repay
When Table 2 inputs the document, Table 1 "The Over Raising" "Not Over-Balance" changes according to the input "Repayment amount" of Table 2. That is: Table 2: Repayment Name Date Repayment No. Repayment Amount Zhang 3 San 99-4-3 000001 13000 Table 1: Learning Name Date Date Borrowing Single Borrowing Substantial Borrowing The amount has not yet returned to 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 sheets 99-3-1 000004 10000 0 15000 ... ... ... .. ..... Table 2 When entering: Name Date Repayment No. Repayment amount Zhang San 99-4-3 000001 13000 Zhang 3 99-4-3 000002 8000 Table 1: Table 1: Learning Name Date Borrow The amount of borrowing has not yet returned to 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 sheets 99-3-1 000004 15000 5000 10000 ... ... ... .. .....
-------------------------------------------------- -------------------------
--test
- Test Data Create Table Table 1 (Name VARCHAR (10), DateTime, Borrowing Single Number Char (6), Borrowing INT, ODS INT, Uncommon Balance INT) Insert Table 1 Select 'Zhang San', ' 99-1-1 ',' 000001 ', 1000, 0, 10000Union All Select' Li Si ', '99 -1-2', '000002', 5000, 0, 5000UNION All Select 'Zhang San', '99 - 2-1 ',' 000003 ', 6000, 0, 6000UNION All Select' Zhang San ', '99 -3-1', '000004', 15000, 0, 15000 - The landlord originally does not have a Create Table Table 2 (Name Varchar (10), Date DateTime, Repayment No. CHAR (6), Repayment INT) GO
- Treated trigger Create Trigger TR_INSERT ON Table 2for INSERTASUPDATE A SET Already the amount = Case When (SELECT SUM (Case WHEN)> 0 THEN Non-retained balance ELSE borrowed amount END) FROM Table 1 WHERE Non-retained balance> 0 And Name = a. Name AND Borrowing Single Number <= a. Borrowing Single Number)> a. Non-retained balance THEN B. Repayment amount - INULL ((Select Sum (Case (Case WHEN) The amount END) FROM Table 1 WHERE Non-retained balance> 0 and name = a. Name And borrowing
), 0) ISNULL (a. Has been added, 0)
Else a. Borrowing amount END
No balance = Case WHEN
SELECT SUM (Case When)> 0 THEN has not returned the balance ELSE borrowing amount END)
From table 1
WHERE has not returned the balance> 0 and name = a. Name AND Borrowing number <= a. borrowing
)> a. Non-retained balance thr. Borrow amount - (b. Repayment amount - INULL ((
SELECT SUM (Case When)> 0 THEN has not returned the balance ELSE borrowing amount END)
From table 1
WHERE has not returned the balance> 0 and name = a. Name And borrowing
), 0) ISNULL (a. Has been made, 0)))
ELSE 0 END
From table 1 a, INSERTED B
WHERE A. No balance> 0 and a. Name = b. Name and isnull
SELECT SUM (Case When)> 0 THEN has not returned the balance ELSE borrowing amount END)
From table 1
WHERE has not returned the balance> 0 and name = a. Name And borrowing
), 0) <= b. Repayment amount
Go
- Insert Record Test Insert Table 2 Select 'Zhang 3', '99 -4-3 ',' 000001 ', 13000 - Display Processing Sulture Select * From Table 1Select * FROM Table 2
- Insert Record Test Insert Table 2 Select 'Zhang San', '99 -4-3 ',' 000002 ', 8000 - Display Processing Sulture Select * From Table 1Select * From Table 2GO
- Remove Test DROP TABLE Table 1, Table 2
/*--Test Results
- Insert the update result of the record:
Name Date Borrowing Single Borrowing Borrowing Amount has not yet returned ------ ------------------------------ ---------------------------- Zhang 3999-01-01 00: 0000 000001 10000 10000 0 Li 4999 -01-02 00: 00 0.000 00 0002 5000 -0三 1999-02-01 00: 00000 3000 3000 300: 00: 00.000 000004 15000 0 15000 (the affected line Number of 4 lines)
Name Date Repayment No. Repayment Amount ------------------------------------- ---------- Zhang 3999-04-03 00: 00: 00.000 0001 13000
(The number of rows affects is 1 line)
- The result of the second insertion:
Name Date Borrowing Single Borrowing The amount has not yet returned ----------------------------------- ----- ------------------------ Zhang 3999-01-01 00: 0000 000001 10000 10000 0 Li 4999- 01-02 00: 00 0.000 0002 5000 0 5000 Zhang 3999-02-01 00: 00: 00 0.00003 6000 6000 0 sheets 3999-03-01 00: 00: 000 0004 15000 5000 10000
(The number of rows affects is 4 lines)
Name Date Repayment No. Repayment Amount -------------------------------------- ---------- Zhang 3999-04-03 00: 00: 00000 0001 13000 Zhang 3999-04-03 00: 00: 00.000 000002 8000
(The number of rows affects is 2 lines)
- * /
