New Records Insertion in Table upon Upgrade Record Trigger

Transact-SQL
1

I have the following table with data

ID Seat_No Start_Station_No Start_Station_Name End_Station_No End_Station_Name
1 A01 1 'ABC' 10 'XYZ'

for example when someone updates this table as

Update SeatTable Set Start_Station_No=3,Start_Station_Name='CDE', End_Station_No=7, End_Station_Name='GHI' where ID=1;

Then the following two rows will be added in the same table upon update record trigger
ID Seat_No Start_Station_No Start_Station_Name End_Station_No End_Station_Name
2 A01 1 'ABC' 3 'CDE'
3 A01 7 'GHI' 10 'XYZ'

The station Names and ID's are kept in a separate table from where the system will pick Names.
Description: The seat is available from the station No 1 to Station No 10, but if someone will book the seat from station no 3 to 7, then two new seats will be created from 1 to 3 and 7 to 10. i wanted to this using triggers in database but not have enough knowledge to do this.

2

I'm not convinced that your design is supportable but let's find out.

  1. What does an entry in the table represent? An available seat? A taken seat?
  2. If, after a seat gets broken into three parts (1-3, 3-7 and 7-10), an entry is changed to make one entry a longer ride (3-7 becomes 3-8). Do you find and adjust the portion that is now overbooked (7-10 changed to 8-10)?
  3. What should be done when a ticket is returned unused? Re-combine all of the sub-parts into one entry and delete the other sub-part entries?
3
  1. The entries in table represents the available seats.
  2. after cancellation of a ticket, the seats broken are re-merged (Marked as cancelled and merged and a new complete seat for long ride is again added in the table)
  3. when a ticket is return, if the remaining part of the seat is un-booked, it is again re-combined as mentioned in point 2.
    The system is working well with .Net code, but for performance improvement i wanted to make it through SQL stored procedure / trigger.

Thanks in advance.