April 24, 2012 at 12:53 am
HI
These are the 3 tables
•AgencyAccount (AgencyID,AccountBalance)
•AgentAccount (AgentID,AgentBalance)
•Booking (BookingID,PNRNo,BookingAmount,AgentID,BookingDate)
Scenario
1)When Agent “ABC1” of Agency “ABC” booked a ticket for a customer the booking amount should be deducted from the Agent(ABC1) account and the corresponding Agency(ABC) account.
Solution:- I am thinking of handling this in BookingInsert Procedure, while inserting in the booking table the corresponding account of Agent and Agency will be updated.
2)When Agent “ABC1” and “ABC2” of Agency “ABC” booked a ticket for their customer the booking amount should be deducted from the Agent(ABC1) and Agent(ABC2) account and the corresponding Agency(ABC) account.
If I am using the above solution, there will be miscalculation in AgencyAccount.
For eg;
ABC Account Balance(Agency) = 100
ABC1Account(Agents) = 50
ABC2Account(Agents) = 50
If ABC1 do the first booking of bookingamount =20, he will select the amount from AgencyAccount Which is 100 and deduct 20 from the Agency Account.
If at the same time ABC2 do the booking of amount 30 it should deduct from agency account 80 not 100.The AgencyAccount should show 80-
In this Scenario I am thinking of using trigger on booking table wilth a while loop in that to handle multiple booking insert, so that it will update the account of Agent and Agency correctly. This solution may be very slow.
I don’t want to handle this in trigger, is there any solution which can be done
In procedure.
Thanks
April 24, 2012 at 2:16 am
You most likely do not need to use loop.
Please follow the link at the bottom of my signature to find out what we need to help you.
April 24, 2012 at 3:38 am
April 30, 2012 at 9:25 am
This is the script
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AgencyAccount](
[AgencyID] [int] NOT NULL,
[AgencyBalance] [numeric](19, 5) NOT NULL,
CONSTRAINT [PK_AgencyAccount] PRIMARY KEY CLUSTERED
(
[AgencyID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO dbo.AgencyAccount(AgencyID, AgencyBalance)
SELECT 1,20000
UNION ALL
SELECT 2,50000
GO
/********************************************************************/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AgentAccount](
[AgentID] [int] NOT NULL,
[AgencyID] [int] NOT NULL,
[AgentBalance] [numeric](19, 5) NOT NULL,
CONSTRAINT [PK_AgentAccount] PRIMARY KEY CLUSTERED
(
[AgentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO dbo.AgentAccount(AgentID,AgencyID,AgentBalance)
SELECT 1,1,10000
UNION ALL
SELECT 2,1,10000
UNION ALL
SELECT 3,2,20000
UNION ALL
SELECT 4,2,20000
UNION ALL
SELECT 5,2,10000
GO
/***************************************************************/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AccountTransaction](
[AccountTransactionID] [int] IDENTITY(1,1) NOT NULL,
[BookingID] [int] NULL,
[CreditDepositID] [int] NULL,
[AgentID] [int] NOT NULL,
[AgencyID] [int] NOT NULL,
[Amount] [numeric](19, 5) NOT NULL,
[AgentCurrentBalanceAmount] [numeric](19, 5) NOT NULL,
[AgencyCurrentBalanceAmount] [numeric](19, 5) NOT NULL,
[Date] [date] NOT NULL,
CONSTRAINT [PK_AccountTransaction] PRIMARY KEY CLUSTERED
(
[AccountTransactionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[AccountTransactionInsert]
@BookingID int,
@CreditDepositID int,
@AgentID int,
@AgencyID int,
@Amount numeric(19,5),
@AgentCurrentBalanceAmount numeric(19,5),
@AgencyCurrentBalanceAmount numeric(19,5),
@Date date
AS
BEGIN
INSERT INTO [dbo].[AccountTransaction]
([BookingID]
,[CreditDepositID]
,[AgentID]
,[AgencyID]
,[Amount]
,[AgentCurrentBalanceAmount]
,[AgencyCurrentBalanceAmount]
,[Date])
VALUES
(
@BookingID ,
@CreditDepositID ,
@AgentID,
@AgencyID ,
@Amount ,
@AgentCurrentBalanceAmount ,
@AgencyCurrentBalanceAmount ,
@Date
)
END
GO
/**************************************************************************/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Booking](
[BookingID] [int] IDENTITY(1,1) NOT NULL,
[AgencyID] [int] NULL,
[PNRNo] [varchar](50) NOT NULL,
[BookingAmount] [numeric](19, 5) NOT NULL,
[AgentID] [int] NOT NULL,
[BookedDate] [date] NOT NULL,
CONSTRAINT [PK_Booking] PRIMARY KEY CLUSTERED
(
[BookingID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[BookingInsert]
@AgencyID int,
@PNRNo varchar(50),
@BookingAmount numeric(19,5),
@AgentID int,
@BookedDate date
AS
BEGIN
--Getting Agency Balance
DECLARE @AgencyBalance numeric(19,5)
SELECT
@AgencyBalance = AgencyBalance
FROM dbo.AgencyAccount
WHERE AgencyID = @AgencyID
--Getting Agent Balance
DECLARE @AgentBalance numeric(19,5)
SELECT
@AgentBalance = AgentBalance
FROM dbo.AgentAccount
WHERE AgentID = @AgentID
DECLARE @BookingID int
INSERT INTO [dbo].[Booking]
(
[AgencyID]
,[PNRNo]
,[BookingAmount]
,[AgentID]
,[BookedDate]
)
VALUES
(
@AgencyID
,@PNRNo
,@BookingAmount
,@AgentID
,@BookedDate
)
SELECT @BookingID = SCOPE_IDENTITY()
BEGIN -- Deducting the amount From the Agency and Agent Account
UPDATE dbo.AgentAccount
SET
AgentBalance = @AgentBalance - @BookingAmount
WHERE AgentID = @AgentID
UPDATE dbo.AgencyAccount
SET
AgencyBalance = @AgencyBalance - @BookingAmount
WHERE AgencyID = @AgencyID
END
--Getting Agency updated Balance
DECLARE @AgencyBalanceupd numeric(19,5)
SELECT
@AgencyBalanceupd = AgencyBalance
FROM dbo.AgencyAccount
WHERE AgencyID = @AgencyID
--Getting Agent updated Balance
DECLARE @AgentBalanceupd numeric(19,5)
SELECT
@AgentBalanceupd = AgentBalance
FROM dbo.AgentAccount
WHERE AgentID = @AgentID
DECLARE @AccountDate date
SELECT @AccountDate = CAST(GETDATE() AS date)
BEGIN
EXEC AccountTransactionInsert @BookingID,NULL,@AgentID,@AgencyID,@BookingAmount,@AgentBalanceupd,@AgencyBalanceupd,@AccountDate
END
END
GO
/**********************************************************************************/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CreditDeposit](
[CreditDepositID] [int] IDENTITY(1,1) NOT NULL,
[AgencyID] [int] NOT NULL,
[AgentID] [int] NOT NULL,
[IsCredit] [bit] NOT NULL,
[Amount] [numeric](19, 5) NOT NULL,
[AgencyCurrentBalance] [numeric](19, 5) NOT NULL,
[AgentCurrentBalance] [numeric](19, 5) NOT NULL,
[Remarks] [varchar](50) NOT NULL,
CONSTRAINT [PK_CreditDeposit] PRIMARY KEY CLUSTERED
(
[CreditDepositID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[usp_CreditDepositInsert]
@AgencyID int,
@AgentID int,
@IsCredit bit,
@Amount numeric(19,5),
@Remarks varchar(50)
AS
BEGIN
SET NOCOUNT ON;
BEGIN
--Getting Agency Balance
DECLARE @AgencyBalance numeric(19,5)
SELECT
@AgencyBalance = AgencyBalance
FROM dbo.AgencyAccount
WHERE AgencyID = @AgencyID
--Getting Agent Balance
DECLARE @AgentBalance numeric(19,5)
SELECT
@AgentBalance = AgentBalance
FROM dbo.AgentAccount
WHERE AgentID = @AgentID
DECLARE @CreditDepositID int
INSERT INTO CreditDeposit
(AgencyID, AgentID, IsCredit, Amount, AgencyCurrentBalance, AgentCurrentBalance, Remarks)
VALUES
(@AgencyID, @AgentID, @IsCredit, @Amount, @AgencyBalance, @AgentBalance, @Remarks)
SET @CreditDepositID = SCOPE_IDENTITY()
BEGIN
IF @IsCredit = 0
BEGIN-- Deduct the amount from Agency and Agents account
UPDATE dbo.AgentAccount
SET
AgentBalance = @AgentBalance - @Amount
WHERE AgentID = @AgentID
UPDATE dbo.AgencyAccount
SET
AgencyBalance = @AgencyBalance - @Amount
WHERE AgencyID = @AgencyID
END
ELSE
BEGIN--Increment the amount from Agency and Agents account, it is for depositing
UPDATE dbo.AgentAccount
SET
AgentBalance = @AgentBalance + @Amount
WHERE AgentID = @AgentID
UPDATE dbo.AgencyAccount
SET
AgencyBalance = @AgencyBalance + @Amount
WHERE AgencyID = @AgencyID
END
END
BEGIN
--Getting Agency Balance after updation
DECLARE @AgencyBalance1 numeric(19,5)
SELECT
@AgencyBalance1 = AgencyBalance
FROM dbo.AgencyAccount
WHERE AgencyID = @AgencyID
--Getting Agent Balance after updation
DECLARE @AgentBalance1 numeric(19,5)
SELECT
@AgentBalance1 = AgentBalance
FROM dbo.AgentAccount
WHERE AgentID = @AgentID
DECLARE @CreditDepositDate date
SELECT @CreditDepositDate = CAST(GETDATE() AS date)
BEGIN
EXEC AccountTransactionInsert NULL,@CreditDepositID,@AgentID,@AgencyID,@Amount,@AgentBalance1,@AgencyBalance1, @CreditDepositDate
END
END
END
END
GO
/*******************************************************************************/
These are the tables and their description
1.AgencyAccount -- to store Agency account details
2.AgentAccount -- to store Agent account details
3.Booking - to store booking detail
4.CreditDeposit -- to store User or Agency Credit or debit details, here user can deposit or withdraw amount from the Agency Account or Agent Account
5. AccountTransaction -- this table is for reporting purpose, all the accoun transaction will be stored here
The problem there is Miscalculation in Agency account and Agent account. Is there any better solution.
Problem scenario is described in the previous post
May 1, 2012 at 5:22 am
The issue you are encountering is known as the 'lost update' problem. Alexander Kuznetsov has an excellent article that covers this topic well: http://www.simple-talk.com/sql/t-sql-programming/developing-modifications-that-survive-concurrency/
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 4, 2012 at 3:38 am
SQL Kiwi (5/1/2012)
The issue you are encountering is known as the 'lost update' problem. Alexander Kuznetsov has an excellent article that covers this topic well: http://www.simple-talk.com/sql/t-sql-programming/developing-modifications-that-survive-concurrency/
thanks paul for the reply
I had seen your reply in this forum
http://www.sqlservercentral.com/Forums/Topic1021232-391-1.aspx#bm1021874
Here you mentioned about READPAST options
I am thinking of using this option in AgentAccount and AgencyAccount table
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply