問題描述

我正在使用觸發器將行插入表中，使用 INSERT 語句，如下所示，但這樣做時 RECORD_ID 編號增加 1 位，因此插入的所有記錄都具有相同的編號..

I am using a trigger to insert rows into a table using INSERT statement as below but when doing this the RECORD_ID number increments by 1 digit so all the records inserted have the same number..

這是我用來增加觸發器記錄的方法.

This is what i'm using to increment the records from the trigger.

, ISNULL((
               SELECT MAX([PROGRESS-RECID]) FROM [DBAdmin].[dbo].[ReTncyTransStatement]
           ),0) + 1 AS [PROGRESS-RECID]

這是我用來加載數據的

;WITH TestTrans   (
       [ORG-CODE]
      ,[TNCY-SYS-REF]
      ,[TRANS-NO]
      ,[POSTING-YEAR]
      ,[POSTING-WEEK]
      ,[TRANS-YEAR]
      ,[TRANS-WEEK]
      ,[TRANS-DATE]
      ,[ACCOUNT-TYPE]
      ,[ACCOUNT-CODE]
      ,[COMMENT]
      ,[TRANS-AMT]
      ,[SOURCE]
      ,[CREATED-USER]
      ,[CREATED-DATE]
      ,[CREATED-TIME]
      ,[UPDATED-USER]
      ,[UPDATED-DATE]
      ,[UPDATED-TIME]
      ,[BATCH-NO]
      ,[BATCH-NO-TYPE]
      ,[SUSPENSE-REF]
      ,[REFERENCE]
      ,[MGT-AREA]
      ,[ANALYSIS-CODE]
      ) 
AS (SELECT   
       [ORG-CODE]
      ,[TNCY-SYS-REF]
      ,[TRANS-NO]
      ,[POSTING-YEAR]
      ,[POSTING-WEEK]
      ,[TRANS-YEAR]
      ,[TRANS-WEEK]
      ,[TRANS-DATE]
      ,[ACCOUNT-TYPE]
      ,[ACCOUNT-CODE]
      ,[COMMENT]
      ,[TRANS-AMT]
      ,[SOURCE]
      ,[CREATED-USER]
      ,[CREATED-DATE]
      ,[CREATED-TIME]
      ,[UPDATED-USER]
      ,[UPDATED-DATE]
      ,[UPDATED-TIME]
      ,[BATCH-NO]
      ,[BATCH-NO-TYPE]
      ,[SUSPENSE-REF]
      ,[REFERENCE]
      ,[MGT-AREA]
      ,[ANALYSIS-CODE] from [SQLViewsPro2Live].[dbo].[RE-TNCY-TRANS] where [TRANS-DATE] between '2019-05-16 00:00:00.000' and '2019-05-17 00:00:00.000'
      )    
INSERT INTO [SQLViewsPro2Test].[dbo].[RE-TNCY-TRANS]   
    SELECT 

     [ORG-CODE]
      ,[TNCY-SYS-REF]
      ,[TRANS-NO]
      ,[POSTING-YEAR]
      ,[POSTING-WEEK]
      ,[TRANS-YEAR]
      ,[TRANS-WEEK]
      ,[TRANS-DATE]
      ,[ACCOUNT-TYPE]
      ,[ACCOUNT-CODE]
      ,[COMMENT]
      ,[TRANS-AMT]
      ,[SOURCE]
      ,[CREATED-USER]
      ,[CREATED-DATE]
      ,[CREATED-TIME]
      ,[UPDATED-USER]
      ,[UPDATED-DATE]
      ,[UPDATED-TIME]
      ,[BATCH-NO]
      ,[BATCH-NO-TYPE]
      ,[SUSPENSE-REF]
      ,[REFERENCE]
      ,[MGT-AREA]
      ,[ANALYSIS-CODE]
   FROM TestTrans;  
GO  

感謝任何修復謝謝，

此處提供問題的完整描述:T-SQL:創建觸發器以將新列從一個表復制到另一個表并增加 no

Full description of problem available here: T-SQL : create trigger to copy new columns from one table to another and increment no

推薦答案

使 PROGRESS-RECID 成為 IDENTITY 列，它將自動遞增.

Make PROGRESS-RECID an IDENTITY column and it will auto-increment.

這篇關于使用觸發器插入記錄時遞增 RECORD_ID的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！