Help with logic. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Help with logic.

Author

Topic

sandesh.ravi
Posting Yak Master

110 Posts

Posted - 2012-02-24 : 05:30:42

I have 2 tables Loads and Balance

CREATE TABLE [dbo].[Loads](
[ID] [int] IDENTITY(1,1) NOT NULL,
[CardProduct] [nvarchar](50)NOT NULL,
[LoadAmt] [decimal](18, 2) NULL,
[LoadDate] [datetime] NULL,
[LoadType] [nvarchar](50) NULL
)

CREATE TABLE [dbo].[Balance](
[CardProduct] [nvarchar](50) NOT NULL,
[AvlBalance] [decimal](18, 2) NOT NULL
) ON [PRIMARY]

GO

And it has the following data.
-- Loads
INSERT INTO [loads] VALUES('IP01',10.00,'2012-02-24','Load')
INSERT INTO [loads] VALUES('IP01',20.00,'2012-02-24','Load')
INSERT INTO [loads] VALUES('IP01',30.00,'2012-02-24','Load')
INSERT INTO [loads] VALUES('IP01',40.00,'2012-02-24','Load')
INSERT INTO [loads] VALUES('IP01',50.00,'2012-02-24','Load')
--Balance
Insert INTO [Balance] VALUES ('IP01', 800)

The data in loads table gets almost every second. approximately 30 rows per second.

1) As soon as a record is inserted in Load table it needs to reduce the LoadAmt from the AvlBalance in Balance table based on card product.

So AvlBalance was 800 for IP01
so after the rows in loads table the AvlBalance should become 650.

I am worried about the processing time since record in loads table is inserted in milliseconds frequency.

Kindly let me know the procedure to achieve this.

Thanks,
Sandesh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-24 : 10:48:38

sounds like trigger need to be setup
another way is to add logic inline in insert procedure using OUTPUT clause and restrict inserts only through the proc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-02-24 : 11:11:38

Depends a lot on your system design, be perhaps something like:


BEGIN TRY
    BEGIN TRAN

    UPDATE Loads
    SET AvlBalance = AvlBalance - LoadAmt 
    WHERE ...

    INSERT Loads
    ...

    COMMIT TRAN
END TRY
BEGIN CATCH
    ROLLBACK TRAN
END CATCH

sandesh.ravi
Posting Yak Master

110 Posts

Posted - 2012-02-27 : 07:13:24

Thank you for the inputs. I have added the logic to restrict the inserts and used the tran.

Thanks,
Sandesh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources