Post reply

New To Triggers

  • May 5, 2004 at 10:26 am

    #161318

    I have two tables [tbl_Contracts] and [rev_Contracts] the records in tbl_Contracts are updatable via a web form. Each update needs to archive the old data as an archived revision in the rev_Contracts table. I would rather not do this through the web scripting but use a trigger.

    The following duplicates ALL records from tbl_Contracts -> rev_Contracts with the "new" data. Any pointers would be much appreciated.

    CREATE TRIGGER [ConRev] ON [dbo].[tbl_Contracts]

    FOR UPDATE

    AS

     INSERT INTO

     [dbo].[rev_Contracts]

     (ContractID, ActiveBy, StartDate, StopDate, CostCenterID, ContractName, ContractFriendlyName, AllowedDollars, AllowedHours)

     SELECT ContractID, ActiveBy, StartDate, StopDate, CostCenterID, ContractName, ContractFriendlyName, AllowedDollars, AllowedHours

     FROM [dbo].[tbl_Contracts]

     WHERE ContractID = tbl_Contracts.ContractID

    TIA -scott

  • May 5, 2004 at 12:04 pm

    #505352

    You need to do some reading about the "INSERTED" and "DELETED" tables that SQL Serve provides for you when using Triggers.  Using these tabless will allow you yo copy only the row(s) which have been updated.

    - B

  • May 5, 2004 at 1:07 pm

    #505358

    Got it. Thanks for the ptr ...

    -Scott

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply