New Records Insertion in Table upon Upgrade Record Trigger

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.

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?
  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.