为存储过程sql server 2008中的每个更新行插入新行 [英] insert new row for each updated rows in stored procedure sql server 2008

问题描述

你好

如何为存储过程sql server 2008中的每个更新行插入新行???

我用这个：

 更新 dbo.DeviceLogs  SET  EndDate = @ ENDDATE，Period = @ PERIOD， @ STATUS  =状态， @ DEVICEID  = DeviceNames_ID， @ AMID  = AMs_ID 
 其中 EndDate    Null  
  INSERT   INTO  dbo.DeviceLogs（[Status]，[BeginDate]，[DeviceNames_ID]，[AMs_ID]）
  VALUES （ @STATUS ，GETDATE（）， @ DEVICEID ， @ AMID ）; 

但只需插入最新更新。

请帮助我。

tnx

解决方案

我不知道你的数据结构和你的任务的确切要求，但是从给定的例子中您似乎可以使用 AFTER UPDATE触发器插入新行。它会像这样工作：当您更新dbo.DeviceLogs表中的记录时，触发器会触发并插入新记录。



以下是此类触发器的示例：

  CREATE   TRIGGER  [dbo]。[trg_DeviceLogs_AU ] 
  ON  [dbo]。[DeviceLogs] 
 AFTER  UPDATE  
  AS  
  BEGIN  
  SET   NOCOUNT   ON ; 
 
  INSERT   INTO  [dbo]。[DeviceLogs] 
（ [状态]，
 [BeginDate]，
 [DeviceNames_ID]，
 [AMs_ID]）
  SELECT  i。[状态]，
 GETDATE（），
 i。[DeviceNames_ID]，
 i。[AMs_ID] 
  FROM  INSERTED  AS  i 
  INNER   JOIN  DELETED  AS  d 
  ON  d。[ID] = i。[ID] 
  WHERE  i。[EndDate]  IS   NOT   NULL  
  AND  d。[EndDate]  IS   NULL ; 
  END  
 
  GO  

此触发器将按更新的行插入新行（如果更新行中的[EndDate]值从NULL更改为某些NOT NULL值）。



存储过程中的更新语句应如下所示：

 更新 [dbo]。[DeviceLogs] 
  SET  [EndDate] =  @ ENDDATE ，
 [期间] =  @ PERIOD ，
 [状态] =  @ STATUS ，
 [DeviceNames_ID] =  @ DEVICEID ，
 [AMs_ID] =  @ AMID  
  WHERE  [EndDate]  IS   NULL ; 

如果您需要更多帮助，请告诉我是

hello
how to insert new row for each updated rows in stored procedure sql server 2008???
I use from this:

Update dbo.DeviceLogs SET EndDate=@ENDDATE, Period=@PERIOD, @STATUS = Status, @DEVICEID = DeviceNames_ID, @AMID = AMs_ID
        Where EndDate is Null
        INSERT INTO dbo.DeviceLogs ([Status],[BeginDate],[DeviceNames_ID],[AMs_ID])
        VALUES (@STATUS, GETDATE(), @DEVICEID, @AMID) ;

but just insert For latest updates.
please help me.
tnx

解决方案

Hi,

I don't know your data structure and the exact requirements of your task, but from the given example it seems that you could use AFTER UPDATE trigger for inserting new rows. It would work like this: when you update record in dbo.DeviceLogs table, trigger fires up and inserts new record.

Here is the example of such trigger:

CREATE TRIGGER [dbo].[trg_DeviceLogs_AU]
ON [dbo].[DeviceLogs]
AFTER UPDATE
AS
  BEGIN
      SET NOCOUNT ON;

      INSERT INTO [dbo].[DeviceLogs]
                  ([Status],
                   [BeginDate],
                   [DeviceNames_ID],
                   [AMs_ID])
      SELECT i.[Status],
             GETDATE(),
             i.[DeviceNames_ID],
             i.[AMs_ID]
      FROM   INSERTED AS i
             INNER JOIN DELETED AS d
                     ON d.[ID] = i.[ID]
      WHERE  i.[EndDate] IS NOT NULL
             AND d.[EndDate] IS NULL;
  END

GO

This trigger would insert new rows by updated rows (if [EndDate] value in updated row changes from NULL to some NOT NULL value).

Update statement in your stored procedure should be like this:

UPDATE [dbo].[DeviceLogs]
SET    [EndDate] = @ENDDATE,
       [Period] = @PERIOD,
       [Status] = @STATUS,
       [DeviceNames_ID] = @DEVICEID,
       [AMs_ID] = @AMID
WHERE  [EndDate] IS NULL;

Please let me know if you need more help with this.

这篇关于为存储过程sql server 2008中的每个更新行插入新行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！