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

What are you trying to discover?

Theoretically, one transaction could cover hundreds of different SQL statements.

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

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.

Thanks clivew,
ok . but in my case, i have to be add more than one records to the table before commit.


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

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 ,

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):

.

thanks clivew ,

.. I will use this ..

thanks a lot