SQL 2005 Update Column on Insert

Question

I need some assistance with creating a stored procedure/query (doesn't really matter as long as it works) where the following happens when I insert a new row. It is a very simple table with 4 columns that relates to other tables

CREATE TABLE [#loginid_name](
[Login_ID] [int] NOT NULL,
[Login_ID_Name] [varchar](30) COLLATE Latin1_General_CI_AI NOT NULL,
[Date_Begin] [datetime] NOT NULL,
[Date_Finish] [datetime] NULL)

When I insert a new row: query checks to see if there are other records of that ID with a higher begin_date (doesn't matters if there are multiple rows with higher begin_date, I just need the one closer to the one inserted)
If there is, then set that date_finish of the new row as the date_begin of the existing row
If there isn't, set the newly inserted date_finish as NULL
Check to see if there are other records of that ID with a lower date_begin, but higher (or NULL) date_finish
If there is, update that record's date_finish to the newly inserted date_begin value.

Does that make sense?

I can put the pieces together in my mind, but can't seem a way to code this into a single query/stored procedure in SQL. I will be inserting those rows from Excel using VBA so I can hard code this into my insert query on VBA.

BTW I would like to avoid triggers as I've tried them in the past and they give me a lot of trouble. I'm quite new at this SQL thing.

Can you help me?

Thanks!

@ckuhn203 - thanks for the input. Though your problem seems much more complex than mine it definitely sheds some light and I will be having a thorough read to keep learning more about SQL. — giusepesm
– giusepesm, Commented May 29, 2014 at 13:23

GarethD · Accepted Answer · 2014-05-29 13:04:21Z

2

I would approach this differently, since the finish date is essentially a calculated field based on other records on the same table, I would keep it calculated rather than stored. So you can set up a view something like:

SELECT  Login_ID,
        Login_ID_Name,
        Date_Begin,
        (   SELECT  MIN(Date_Begin)
            FROM    [#loginid_name] AS l2
            WHERE   l2.Login_ID = l.Login_ID
            AND     l2.Date_Begin > l.Date_Begin
        ) AS Date_Finish
FROM    [#loginid_name] AS l;

This way you do not need to manage the Date_Finish column through VBA, or triggers, you can just reference this view instead of the base table and get your end date. So if your Date_Begin is updated after the insert for whatever reason, then your Date_Finish of the previous record remains in sync. This is much simpler bearing in mind that when such an update is made it could cause the date_finish of more than one record to alter.

answered May 29, 2014 at 13:04

GarethD

70k11 gold badges90 silver badges130 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

giusepesm Over a year ago

You sir are a fine gentleman. Thank you so much. Your solution gave me exactly what I needed. I will be going ahead and modifying my DB according to your suggestion.

Collectives™ on Stack Overflow

SQL 2005 Update Column on Insert

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related