Use triggers in MS SQL to determine your clients’ transaction interval

We needed to create SQL Jobs to insert data from multiple tables into a single table from 2 different databases, and from 2 different locations. The table will be used for the shopping cart inventory control. Each database was located on a different IP address. Using 2 different databases to grab the inventory from 2 different locations, was done more so for political reasons. Simply these were the facts that I needed to find out a reasonable solution and resolve any performance issue asap. I had to make sure that all transactions are fully optimized and there are zero delays.

We did the following:

1. First, I created a table to store all operations say from the Stock table as follows:
2. Under the Stock table I created a trigger as follows: The below will store any data into St_Trigger table:
3. I created several triggers in major tables and created additional tables to support these triggers to store any transaction occurred. Here is a sample data from for the stock table transactions: If you look at record 467 to 471 there are multiple updates occurred for that duration. Record 472 starts about 2.4 min later as a Delete.
   

   

   Here is how we figured this out:
   15:14:43.2200000 - 15:12:05.3400000 is a bout 2.38 seconds. While record 472 is: 15:14:43.2200000 and record 470 is: 15:12:05.3400000
   
   The other transactions were about 5 min intervals or less in other tables. In many cases we got multiple transactions at the same time. We ran the above triggers for 2 weeks and then they were removed.
4. As mentioned above the idea was to figure out what will be the duration per each SQL Job. The first job was about every 5 min from 7:00 AM to 7:00 PM in database A and it was basically 0 seconds to execute.
   
   In database B there was another job to copy the data from database A from one table only for every 6 min. In database B the data was truncated before it was inserted as new data from 7:06AM to 7:06PM that seems to be the fastest way. It took about 8 seconds to copy the data from database A to database B.
   
   At that duration it was locked until it was completed.
5. In addition, we created an SP to Union the data from that single table into the other tables in the local database (B). It worked without any delays for any inventory control request either by the local users or from the shopping cart.

In Conclusions:
---------------------
Generally, I am not a big fan of using triggers under any table. I always tried to avoid them in any project for any database. But for this business requirements it was acceptable solution and worked pretty fast and there was a clear logic. After 2 weeks all triggers were removed and we had a clear picture about the client’s interval between each data transaction. The client is very happy.