#1
  1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2008
    Posts
    75
    Rep Power
    10

    How to know the Records affected in a Transaction


    Hi All,

    Is there any way to determine the tables & rows affected in a transactions in the DATABASE TRANSACTION COMMIT TRIGGER?


    Thanks in advance

    Bij
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,057
    Rep Power
    383
    What are you trying to discover?

    Theoretically, one transaction could cover hundreds of different SQL statements.
  4. #3
  5. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2008
    Posts
    75
    Rep Power
    10

    Thanks Clivew


    Thanks Clivew,

    I have a table of accounts details with field Debit,Credit.
    The sum of the debit ,Credit in this table must be equal.

    So i want to check whether the debit , credit total is the same on committing the transaction.

    thanks
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,057
    Rep Power
    383
    Well, run a query to check that they match BEFORE you COMMIT.
    If they do not match issue a ROLLBACK instead and issue some appropriate warning.

    Naturally you will need to make sure that you do not have any sort of "Commit On Post" option set and handle your own COMMIT and ROLLBACK instructions.
  8. #5
  9. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2008
    Posts
    75
    Rep Power
    10
    Thanks clivew,
    ok . but in my case, i have to be add more than one records to the table before commit.
    Originally Posted by clivew
    Well, run a query to check that they match BEFORE you COMMIT.
    If they do not match issue a ROLLBACK instead and issue some appropriate warning.
    I think running a query to sum all the records( may have millions) for every insert will slow down my insert process, Instead if i get the affected records in the current transaction , it will will be performance gain .

    regards
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,057
    Rep Power
    383
    I think you need to show us your code / SQL to get a better understanding of what you are doing.

    If your logic is watertight and you do all the debits and credits inside a single transaction
    there should be no problem if no exceptions are raised.
    If any exceptions are raised rollback the entire transaction and address the problem.
    There should be no need to do any counting or summing.
  12. #7
  13. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2008
    Posts
    75
    Rep Power
    10
    Originally Posted by clivew
    I think you need to show us your code / SQL to get a better understanding of what you are doing.

    If your logic is watertight and you do all the debits and credits inside a single transaction
    there should be no problem if no exceptions are raised.
    If any exceptions are raised rollback the entire transaction and address the problem.
    There should be no need to do any counting or summing.
    Well, i will explain the things in detail (Kindly forgive my bad english)
    I have a account DB to store vouches
    I am using two tables Acc_head and ACC_detail tables for that purpose. Following is the basic structure

    Acc_head
    ID int (primaryKey)
    VocDate (date)
    Narration varchar(100)

    ACC_detail
    ID int (primaryKey)
    Header_ID int (foreign Key ->Acc_head)
    LedgerID int ( foreign Key ->Acc_Ledger)
    Debit Numeric,
    Credit Numeric

    Each voucher may have multiple credit or multiple debit , but the sum of the debit and credit in the vouchers will be the same.. so called tallied

    All my other flow is designed based on the assumption that all the vouchers are tallied. So i need to ensure that the sum(Debit) , sum(credit) in the ACC_detail will always tallied.

    The user may insert the data through the UI ( here we can check the data validity at client side) or sometimes using the SQL ...

    When the user inputs data using SQL, i am not able to validated the data. since the user inputs the sql one another as follows

    begin transaction
    insert into Acc_head (fid....) Values(.....) returning ID into HID
    insert into ACC_detail (Header_id,debit,credit) Values(HID,n,n1)
    insert into ACC_detail (Header_id,debit,credit) Values(HID,n2,n3)
    insert into ACC_detail (Header_id,debit,credit) Values(HID,n4,n5)

    Commit


    At this point how can i check the sum(debit),Sum(Credit) in the current transaction is tallied.

    If i have the details about the rows affected in the current transaction (In TRANSACTION COMMIT TRIGGER) , then i can write a rule there. So that the validity of the data can be checked inside the database itself.

    hope you got some idea
    thanks ,
    Last edited by biju_ps; January 10th, 2011 at 03:10 PM.
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,057
    Rep Power
    383
    Isn't a TRANSACTION COMMIT TRIGGER a DATABASE trigger?
    i.e. I do not believe you can apply it to a designated transaction; but I could be wrong.

    What is wrong with (I am using pseudo code):

    SQL Code:
    BEGIN TRANSACTION
     
        INSERT INTO Acc_head (fid....) VALUES(.....) returning ID INTO HID
        INSERT INTO ACC_detail (Header_id,debit,credit) VALUES(HID,n,n1)
        INSERT INTO ACC_detail (Header_id,debit,credit) VALUES(HID,n2,n3)
        INSERT INTO ACC_detail (Header_id,debit,credit) VALUES(HID,n4,n5) 
     
       SELECT (SUM(debit) - SUM(credit)) 
      FROM ACC_detail
      WHERE (Header_id = HID);
     
      IF the RESULT IS zero
         Commit
      ELSE
         ROLLBACK AND do your fixes
    .
  16. #9
  17. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2008
    Posts
    75
    Rep Power
    10
    thanks clivew ,

    .. I will use this ..

    thanks a lot

IMN logo majestic logo threadwatch logo seochat tools logo