.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month

Home >> Articles >> DataBase >> Post New Resource Bookmark and Share   

 Subscribe to Articles

Automatic History using Triggers

Posted By:Jean Paul       Posted Date: February 05, 2011    Points: 75    Category: DataBase    URL: http://www.dotnetspark.com  

Automatic History using Triggers

I would like to take a moment to explain the following problem I am facing:

I do have a table named Equity which contains the company name and the current PE Ratio. 

The PE Ratio will be updated daily or weekly based on the wish of user through a web application in ASP.NET.  When the process runs for many years, there will be many updating happening to the same record. 


Our problem is to get the historical PE Ratio values, so that we can find out the maximum and minimum values for decision making.

High Cost Solution

One of the solutions, we can prefer is while updating the record from our web application, take a copy of the record and insert it into another table. 

This involves changing the associated business logic layer in c# or changing the stored procedure.  I would say  this is more time consuming and lot of code we have to write to write to manage the new table if schema changes are involved.

Simple Solution

We can use database triggers to achieve the same.  The solution steps are as following.  Belive me, this will take only 5 minutes to do.

1. Create a new History Table with same structure

2. Create a Trigger for UPDATE operation

As our History table is not involved in any business logic currently, we can use the Trigger to solve the problem and move off.


First, we have to create our original Equity table.  The script is given below.  The script was generated from Sql Server 2005.

CREATE TABLE [dbo].[Equity](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [PERatio] [float] NULL,
    [ModifiedOn] [datetime] NULL,
    [Id] ASC

Create the History Table

We can create the EquityHistory table using the following script.  Please note that there is an additional column HistoryId which keeps track of the History record as there will be multiple history records for each original table record.

CREATE TABLE [dbo].[EquityHistory](
    [HistoryId] [int] IDENTITY(1,1) NOT NULL,
    [Id] [int],
    [Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [PERatio] [float] NULL,
    [ModifiedOn] [datetime] NULL,
    [HistoryId] ASC

Create the Trigger

The following Trigger script will do all the magic involved.  It says that create a trigger on table Equity whenever UPDATE statement is used.  The last line says that get the record from table inserted and insert it into the EquityHistory table

CREATE TRIGGER Equity_Update_Trigger
ON Equity
INSERT INTO EquityHistory SELECT * FROM inserted

The table inserted  is a pre-defined table insided triggers.  It will give the current record structure while using with INSERT/UPDATE triggers.

Testing the Whole

You can try inserting a new record to the original Equity table.  The corresponding record will get automatically inserted in the EquityHistory table.

Now, try changing the PERatio from the first able.  You can see a new record gets added to the history table.


The attachment file contains queries to create table, insert data, select data.  You can try running the file against an sql server database.

 Subscribe to Articles


Further Readings:


No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend