CODEWITHSUNDEEP
sqlexcelvbasql-server-2005insert
giusepesm
giusepesm

Reputation: 23

SQL 2005 Update Column on Insert

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)
  1. 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)
  2. If there is, then set that date_finish of the new row as the date_begin of the existing row
  3. If there isn't, set the newly inserted date_finish as NULL
  4. Check to see if there are other records of that ID with a lower date_begin, but higher (or NULL) date_finish
  5. 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!

Upvotes: 2

Views: 49

Answers (1)

GarethD
GarethD

Reputation: 69789

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.

Upvotes: 2

Related Questions