Post reply

Automatically add/delete table entries based on other tables in sql 2008

  • March 15, 2010 at 12:46 pm

    #221738

    I have an SQL database called POINTS with two tables called ANALOG and STATUS. Both of the tables have primary keys called STATION and NAME which I would like to use in a new table called COMMENTS. In the COMMENTS table I would like to have three columns: STATION, NAME, and DESCRIPTION. The STATION and NAME columns should be added or deleted from ANALOG and STATUS. The STATION and NAME columns combination will NEVER be the same in either of the two existing tables so I can use them as Primary Keys in the new table. I want to use the new table to hold descriptions about these points and be able to have people look up the station and name and see the description, but that's not the problem. The problem is building the new table from data in the other two and having those two tables automatically update the new table as new STATIONs and NAMEs are added or deleted.

    Example:

    Database: POINTS

    Table (1): ANALOG

    Columns: STATION (pk), NAME (pk)

    Table (2): STATUS

    Columns: STATION (pk), NAME (pk)

    Table (3): COMMENTS

    Columns: STATION (pk), NAME (pk), DESCRIPTION

    ANALOG STATUS COMMENTS

    STATION|NAME STATION|NAME STATION|NAME |DESCRIPTION

    ----------------- ---------------- ------------------------------

    OBSV |TANKLV OBSV |TNKALM OBSV |TANKLV | Tank Level at Observation Road

    OBSV |TNKALM | Tank overflowing at Observation Road

    If I add or delete data in either of the two tables ANALOG or STATUS I want the COMMENTS table to automatically add or delete the STATION and NAME data in its table. Again, there is no way to have duplicate entries between ANALOG and STATUS so I can use them as Primary Keys in COMMENTS.

  • March 16, 2010 at 10:04 am

    #1134596

    Unless I am missing something, why not create a trigger on both the Station and Analog tables. Using a trigger in this manner will have a performance hit.

  • March 16, 2010 at 10:33 am

    #1134622

    I've never done anything with triggers, but I will do some research and check that out. There won't be very much activity on this at all so I don't worry too much about the performace hit.

    Is this something that will not make any modifications to the other two tables? I cannot change those tables at all.

    I have a test database that I can work with so it shouldn't be too bad to try it out.

    Thanks for the advice!

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

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