Как вставить строку в таблицу и обновить последнюю строку перед вставкой новой строки в SQL Server

Вот структура моей таблицы CustInfo

 Cm_ID | Cust_ID  | StartDate   |   EndDate        | Status 
   1   |   1020   |  05/09/2013 |   20/09/2013     |    Off
   2   |   1027   |  16/09/2013 |   **31/12/2099** |    **On**
   3   |   1020   |  21/09/2013 |   31/12/2099     |    On

Я хочу делать это всякий раз, когда я буду вставлять новую строку для Cust_ID=1027. Сначала следует обновить [EndDate] до новой [StartDate], т.е. сегодня, и установить [Status] = 'Off'.

После вставки новой строки это должно выглядеть так

 Cm_ID | Cust_ID  | StartDate   |   EndDate        | Status 
   1   |   1020   |  05/09/2013 |   20/09/2013     |    Off
   2   |   1027   |  16/09/2013 |   **30/09/2013** |    **Off**
   3   |   1020   |  21/09/2013 |   31/12/2099     |    On
   4   |   1027   |  01/10/2013 |   31/12/2099     |    On

Я сделал это, используя 2 разных запроса следующим образом.

Update CustInfo SET EndDate = '30/09/2013' ,Status='Off'
WHERE Cm_ID=(SELECT MAX(Cm_ID) FROM CustInfo WHERE EndDate='12/31/2099' AND Cust_ID=1027)

INSERT INTO CustInfo ([Cust_ID], [StartDate], [EndDate], [CurrentStatus])
VALUES(1027,'01/10/2013','12/31/2099','On')

Теперь я хочу сделать это, используя один запрос или хранимую процедуру. Но я не знаю, как это сделать?


sql sql-server stored-procedures insert-update
person Vimdav    schedule 09.10.2013    source источник

Ответы (1)


arrow_upward
1
arrow_downward

Вам, вероятно, лучше использовать триггер, таким образом логика применяется ко всем INSERT, а не полагаться на людей, использующих SP. Также было бы лучше выполнить UPDATE после INSERT, то есть только тогда, когда INSERT завершится успешно.

CREATE TRIGGER trgCustInfo_Status
ON CustInfo
AFTER INSERT
AS
BEGIN
  DECLARE @id INT

  SELECT @id = Id 
  FROM (
    SELECT Id, ROW_NUMBER() OVER (ORDER BY Id DESC) rn 
    FROM CustInfo
  ) t
  WHERE rn=2

  UPDATE CustInfo
  SET EndDate = CAST(GETDATE() AS DATE),
      Status = 'OFF'
  WHERE Id = @id
END
person T I    schedule 09.10.2013