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.
Your Understanding is OK. but i am not deleting the current row. i am updating the current row and addition more rows in table. (forward seats, backward seats). I am using .Net code for this purpose. which updates the row and insert two more rows. But i wanted to do this all through SQL instead of .Net so that all can be done in a single transaction and can also be roll backed in case of any error. when a seat is cancelled, we mark it as cancelled (a bit is in the table for this purpose) and a new seat is again generated for the full journey. if the remaining part of the seats are un-booked, then the seats are also joined again. Thanks in Advance.