locked
How to write a History table from the given data?? RRS feed

  • Question

  • Creating History Tables for the  Database

     

    The  Database will need many history tables.  Some of them will track a single data element and others will track multiple related fields.  All history tables will contain TcId, ProgSolNbr, StartDate, EndDate, and the fields that are being tracked.  History tables will have a foreign key relationship to the main table (usually Accounts) based on the TcId.  The current record for each account will be identified by an EndDate of ‘9991231’.  Any errors that occur during the process of updating history tables will be automatically tracked in the HistLog table.  Below is a list of history tables that are needed and a sample of a stored procedure to update a history table.

    HistExtStatInd

                    ExtStatInd

    HistIntStatInd

                    IntStatInd

    HistStatus

                    Status

    HistCycle

                    Cycle

    HistCreditBureauInd

                    CreditBureauInd

    HistPhone

                    Phone   Phone2    Phone3

    HistPricingStrategy

                    PricingStrategy

    HistBal

    BalAmt  BalPrinAmt   BalFeeAmt   BalTransferAmt   BalMiscAmt   BalDisputeAmt

    HistNameAddress

                    Name   Name2   Address   Address2   City   State   Zip   Zip4  

     

    Monday, September 3, 2012 6:04 PM

Answers

All replies

  • Below is a list of history tables that are needed and a sample of a stored procedure to update a history table.


    Then create the tables and stored procedures (or trigger); what's the exact problem here? And please note, no one can guess the table design or will do your work.

    Olaf Helper
    Blog Xing

    • Proposed as answer by Iric Wen Tuesday, September 18, 2012 3:30 AM
    Monday, September 3, 2012 6:26 PM
  • It is really unclear what you are trying to achieve.

    create trigger tru_MyTable on MyTable after update
    as

    if @@ROWCOUNT = 0
        return

    insert MyAuditTable
    select
        i.ID
    ,   d.MyColumn
    ,   i.MyColumn
    from
        inserted    i
    join
        deleted    d    on    d.ID    = o.Id
    go


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    MS SQL Consultants: Improves MS SQL Database Performance

    • Marked as answer by Iric Wen Tuesday, September 18, 2012 3:29 AM
    Tuesday, September 4, 2012 5:27 AM
    Answerer