Code Brew Tea

以下使用簡訊驗證碼發送為例:
僅需要在Insert 、Upade 指令中加上一行OUTPUT Inserted.* INTO [Log Table] 。但須注意Log 資料表的欄位名稱需與原資料表欄位名稱完全相同。

新增時寫入Log資料表

INSERT INTO SMSInfo
	( ITUCodeID
	, MobilePhoneNo
	, VerifyCode
	, SendDate
	, VerifyExpireDate
	, ValidedDate
	, IsVerified)
OUTPUT Inserted.* INTO SMSInfoLog --在新增時同時寫入Log資料表
VALUES
	( @ITUCodeID
	, @MobilePhoneNo
	, @VerifyCode
	, @SendDate
	, @VerifyExpireDate
	, @ValidedDate
	, @IsVerified)

更新時寫入Log資料表

UPDATE dbo.SMSInfo SET
	VerifyCode = @VerifyCode
	,SendDate = @SendDate
	,VerifyExpireDate = @VerifyExpireDate
	,IsVerified = 0
OUTPUT Inserted.* INTO SMSInfoLog --在更新時同時寫入Log資料表
WHERE
	MobilePhoneNo = @MobilePhoneNo 
	AND ITUCodeID = @ITUCodeID
	AND VerifyExpireDate < GETDATE()

 

以下為範例程式提供自行測試

Create 簡訊驗證碼資料表

USE [Northwind]
GO

/****** Object:  Table [dbo].[SMSInfo]    Script Date: 2021/10/13 下午 05:13:16 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[SMSInfo](
	[ITUCodeID] [varchar](20) NOT NULL,		--國碼
	[MobilePhoneNo] [varchar](15) NOT NULL,	--電話號碼
	[VerifyCode] [varchar](6) NOT NULL,		--驗證碼
	[SendDate] [datetime] NOT NULL,			--發送時間
	[VerifyExpireDate] [datetime] NOT NULL,	--驗證碼失效時間
	[ValidedDate] [datetime] NOT NULL,		--驗證成功時間
	[IsVerified] [bit] NOT NULL,			--驗證成功標記
 CONSTRAINT [PK_SMSInfo_1] PRIMARY KEY CLUSTERED 
(
	[ITUCodeID] ASC,
	[MobilePhoneNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Create 簡訊驗證碼Log 資料表

USE [Northwind]
GO
/****** Object:  Table [dbo].[SMSInfoLog]    Script Date: 2021/10/13 下午 05:16:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SMSInfoLog](
[PKID] [int] IDENTITY(1,1) NOT NULL, --PK 流水號
[ITUCodeID] [varchar](20) NOT NULL,  --國碼
[MobilePhoneNo] [varchar](15) NOT NULL, --電話號碼
[VerifyCode] [varchar](6) NOT NULL,  --驗證碼
[SendDate] [datetime] NOT NULL,   --發送時間
[VerifyExpireDate] [datetime] NOT NULL, --驗證碼失效時間
[ValidedDate] [datetime] NOT NULL,  --驗證成功時間
[IsVerified] [bit] NOT NULL,   --驗證成功標記
CONSTRAINT [PK_SMSInfoLog] PRIMARY KEY CLUSTERED 
(
[PKID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Create 新增預存程序

USE [Northwind]
GO
/****** Object:  StoredProcedure [dbo].[SP_InsSMSInfo]    Script Date: 2021/10/13 上午 10:15:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[SP_InsSMSInfo]
	@ITUCodeID varchar(20)
	, @MobilePhoneNo varchar(15)
	, @VerifyCode varchar(6)
	, @SendDate datetime
	, @VerifyExpireDate datetime
	, @ValidedDate datetime
	, @IsVerified bit
AS
BEGIN
	SET NOCOUNT ON		
		INSERT INTO SMSInfo
				( ITUCodeID
				, MobilePhoneNo
				, VerifyCode
				, SendDate
				, VerifyExpireDate
				, ValidedDate
				, IsVerified)
			OUTPUT Inserted.* INTO SMSInfoLog
			VALUES
				( @ITUCodeID
				, @MobilePhoneNo
				, @VerifyCode
				, @SendDate
				, @VerifyExpireDate
				, @ValidedDate
				, @IsVerified)
END

Create 更新預存程序

USE [Northwind]
GO
/****** Object:  StoredProcedure [dbo].[SP_UpdSMSInfo]    Script Date: 2021/10/13 上午 10:47:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[SP_UpdSMSInfo]
	@ITUCodeID varchar(20)
	, @MobilePhoneNo varchar(15)
	, @VerifyCode varchar(6)
	, @SendDate datetime
	, @VerifyExpireDate datetime
	, @ValidedDate datetime
	, @IsVerified bit
AS
BEGIN
	SET NOCOUNT ON

		UPDATE dbo.SMSInfo SET
			VerifyCode = @VerifyCode
			,SendDate = @SendDate
			,VerifyExpireDate = @VerifyExpireDate
			,IsVerified = 0
		OUTPUT Inserted.* INTO SMSInfoLog
		WHERE
			MobilePhoneNo = @MobilePhoneNo 
			AND ITUCodeID = @ITUCodeID
			AND VerifyExpireDate < GETDATE()

		RETURN 0;
END

執行預存程序

--Insert
USE [Northwind]
GO

DECLARE	@return_value int

EXEC	@return_value = [dbo].[SP_InsSMSInfo]
		@ITUCodeID = N'+886',
		@MobilePhoneNo = N'0987654321',
		@VerifyCode = N'123456',
		@SendDate = N'2021-10-13 00:00:00',
		@VerifyExpireDate = N'2021-10-13 00:00:05',
		@ValidedDate = N'2021-10-13 00:00:00',
		@IsVerified = 0

SELECT	'Return Value' = @return_value
GO

--Update
USE [Northwind]
GO

DECLARE	@return_value int

EXEC	@return_value = [dbo].[SP_UpdSMSInfo]
		@ITUCodeID = N'+886',
		@MobilePhoneNo = N'0987654321',
		@VerifyCode = N'123456',
		@SendDate = N'2021-10-13 00:00:00',
		@VerifyExpireDate = N'2021-10-13 00:00:05',
		@ValidedDate = N'2021-10-13 00:00:00',
		@IsVerified = 1

SELECT	'Return Value' = @return_value

GO