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.
    Created SQL table to-hold the trigger operations as update delete and insert
    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.

Doron Farber - The Farber Consulting Group

I started to develop custom software since 1985 while using dBase III from Aston Tate. From there I moved to FoxBase and to FoxPro and ended up working with Visual FoxPro until Microsoft stopped supporting that great engine. With the Visual FoxPro, I developed the VisualRep which is Report and Query Engine. We are also a dot net development company, and one of our projects is a web scrapping from different web sites. We are Alpha AnyWhere developers, and the Avis Car Rental company trusted us with their contract management software that we developed with the Alpha Five software Engine.

Comments

Got questions about unleashing the full potential of your project?
We’ve got the answers!

Contact Us

Search