SQL Server Magazine

Logging Third-Party Applications' Transactions
Most DBAs in small to midsized companies are also responsible for maintaining third-party applications, such as those for accounting and human resources (HR). These applications are usually database-oriented client/server applications. But most people maintaining these applications have a hard time tracking when the application executes a transaction and what tables in the database are affected, how they're affected (update, delete, or insert), and in what sequence. Unfortunately, the applications' manuals usually don't cover these details, so you must buy support services from the vendor.

But with the power of triggers, you can track exactly what happens when a third-party application executes a transaction. My company has an application that uses SQL Server 6.5 with more than 300 tables in two databases: A1 and A2. Here's how we set up a system that uses triggers to track database changes. (Note: To be more secure, we do all processes in the testing environment, in which we establish the same databases as those in our production environment.)

1. Generate the scripts that create all tables within these two databases.
   From Enterprise Manager, expand A1 and A2, then from Objects/Tables, right-click
   any table and choose Generate SQL scripts. From the resulting pop-up window,
   choose to generate scripts for all tables. When you're finished, you'll have two scripts.
   
   
2. Create two more databases—A1_BK and A2_BK—and run the appropriate script that you generated in Step 1 against each database to create duplicates
   of databases A1 and A2.
   
   
3. Create another database, called DBG_DB, and create a table named dbg_info
   in this database by using the following command:

Create table dbg_info
(  db_name varchar(30),
   tbl_name varchar(50),
   act_flag  char(1),
   act_time datetime default getdate()
)

This dbg_info table records all actions and tables involved in transactions that changed any tables. Db_name is the database affected by a transaction, tbl_name is the table affected by a transaction, act_flag is the action that the transaction performed (U for update, D for delete, or I for insert), and act_time is the time the system performed the transaction.

USE A1 (or A2)
Go
Exec crt_dbg_trg

Test your application. Remember to switch to the testing environment from the production environment. After you execute a transaction, you can use DBG_DB..dbg_info to find which tables were affected:

Select * from dbg_db..dbg_info

You can then open the affected tables in the A1_BK and A2_BK database to see which records have been logged.

You need to be aware of a limitation with this technique. If any tables have TIMESTAMP or IDENTITY columns, whose values SQL Server automatically generates, you can't use the following statement in your triggers:

Insert into A1_BK.dbo.table_name select * from A1.dbo.table_name

To use this statement, when you generate the SQL scripts in Step 1, you need to use an editor to find and replace all timestamp columns with the binary(8) data type and delete all identity keywords.

This technique for tracking third-party applications' transactions will help you understand and maintain the applications by letting you see the logic behind and the result of every transaction. And with all this information logged, you can create a Visual Basic (VB) program, for example, to show transaction details in an easy-to-read format.

—Jeffrey Yao
jyao@fastlane.com

End of Article

Prev. page     1 [2]     next page -->

You must log on before posting a comment.

If you don't have a username & password, please register now.