!!! 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;