asked
Insert Records in Table on Update 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.
A quick google search returned this:
https://www.mssqltips.com/sqlservertip/4055/create-a-simple-sql-server-trigger-to-build-an-audit-trail/
So.. Do I understand right if originally, the SeatTable holds one line per seat, with start and end station of a route. And to book a seat for part of that route, you want to issue an update Query, which will fire off a trigger, which will: - Delete the line containing the seat picked. - Insert two new rows - one for the stations BEFORE the picked station in the seat booking, and one for the seats AFTER the picked station in the seat booking. To me, this is a very confusing logic, for which I would much rather have used a stored procedure. To me, an UPDATE should do just that - update a row. Sometimes, an UPDATE will also make some INSERTS to other tables, or even the same table, but in those cases it's usually about invalidating a row by setting an end-date to it and adding a new row with an open end date (to create an audit trail). If the update in fact does something completely different, I would not want to use an update Correct me if I misunderstand what you want to achieve. I can help you out with the steps to achieve it, but I want to be clear about what it is you're trying to do, and possibly talk you into choosing to create a stored procedure for the sake of clearity
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.
18 People are following this question.
