積沙成塔

【情境描述】

有網友在論壇上討論到如何在新增資料時，利用 Trigger 產生具特定規則的編號或流水號，筆者依照發問者的描述嘗試實作可能的解決方案。

【實作步驟】

您可以利用 DML Trigger 在 Insert 動作發生時，由更新資料表 t 的 roadCode 資料行，其規則是 DL 加上五位的流水號，因此筆者在 Trigger 中利用 inserted 邏輯資料表取得目前該筆資料的 id，接著利用字串函數 REPLICATE 搭配 RIGHT 來取得五位數的流水號。

use tempdb

go

if object_id('t') is not null

    drop table t

go

create table t

(

    [id] int identity,

    [roadName] char(1),

    [roadCode] nvarchar(10)

)

go

--建立ddl trigger

create trigger tr_insert_t

on t

for insert

as

    update t

    set roadCode = 'DL' + right(replicate('0',5) + convert(varchar(5),id),5)

    where id = (select id from inserted)

go

insert t([roadName]) values ('a')

insert t([roadName]) values ('b')

insert t([roadName]) values ('c')

go

select *

from t

 

上述的寫法其實有個問題，如果您使用資料表值建構函式來新增資料到資料表 t 時，這個 DML Trigger 的寫法就會出錯，因為使用資料表值建構函式新增資料時，Inserted 邏輯資料表會有多筆資料，導致子查詢回傳多個結果，因而發生錯誤（如下圖）。

因此 DML Trigger 應該修改為下列的樣子，才能避免上述問題。

create trigger tr_insert_t

on t

for insert

as

    update t

    set roadCode = 'DL' + right(replicate('0',5) + convert(varchar(5),inserted.id),5)

    from t 

    join inserted

    on t.id = inserted.id

go

如果不一定要使用 Trigger，利用 SQL Server 2012 新的 SEQUENCE 物件也可以達到相同的目的，在 roadCode 資料行的預設值中搭配 NEXT VALUE FOR 函數取得流水號之後再去組合您所要的編號，一樣可以輕鬆地完成任務。

use tempdb

go

--#region 建立Sequence物件

if object_id ('seqRoadCode') is not null

    drop sequence seqRoadCode

go

create sequence seqRoadCode

as int

start with 1

minvalue 1

maxvalue 99999

increment by 1 

go

--#endregion

if object_id('t') is not null

    drop table t

go

create table t

(

    [id] int identity,

    [roadName] char(1),

    [roadCode] nvarchar(10) 

        default 'DL' + right(replicate('0',5) + convert(varchar(5),next value for seqRoadCode),5)

)

go

insert t([roadName]) values ('a'),('b'),('c')

go

select *

from t

【參考資料】

- CREATE TRIGGER (Transact-SQL)

- REPLICATE (Transact-SQL)

- RIGHT (Transact-SQL)

- 資料表值建構函式 (Transact-SQL)

- CREATE SEQUENCE (Transact-SQL)

- 序號

- NEXT VALUE FOR (Transact-SQL)

【補充說明】

朋友 James 根據筆者上述的文章內容指出，建議應在 Trigger 處理資料前，先加上 SET NOCOUNT ON，以避免原本異動的資料筆數被 Trigger 所更新的筆數給影響，造成其他程式邏輯判斷上的錯誤。