以下的測試範例會使用INSTEAD OF觸發程序實作出在批次新增過程,根據每一天訂單的總筆數,從【000001】開始給號,格式是【YYYYMMDD.NNNNNN】。換句話說,假設2008/06/04的第一筆訂單,序號就是從【20080604.000001】開始,後續訂單序號就會從
【20080604.000002】往下繼續編排,但是隔日到2008/06/05的時候,就會從【20080605.000001】開始,其他以此類推。
以下的訂單資料表,會有訂單編號、產品代號、數量、區域與建檔日期,其中的建檔日期資料行,就是用在識別當天已經存在多少筆資料量的重要關鍵。
|
--建立訂單資料表,訂單序號是主索引鍵不可以重複
--建檔日期始使用預設的GETDATE()值
CREATE TABLE
fruitOrderList
(orderID varchar(20) NOT NULL
primary key,
prodID
int,
qty
int ,
region
varchar(10) ,
keyinDT datetime default (getdate())
)
GO
|
完成資料表建立之後,接下來就是要建立INSERT陳述式的INSTEAD
OF觸發程序,定義內容是根據當天的日期訂單數量,產生訂單序號,過程中會使用CONVERT搭配GETDATE()函數使用【112】的參數,產生YYYYMMDD的資料,還有使用RIGHT函數將數字轉換成文字之後,自動將不足位數補【0】。最後在INSTEAD OF觸發程序再加上一段INSERT陳述式,搭配取得的序號與INSERTED快取資料表,批次將資料新增到訂單。
|
--建立INSTEAD OF觸發程序
CREATE TRIGGER
tri_Int_fruitOrderList ON fruitOrderList
INSTEAD OF INSERT
AS SET NOCOUNT ON
declare @oSN varchar(20) --產生新續號規則=日期+(總筆數+1)
SELECT
@oSN=CONVERT(varchar(10),getdate(),112)+'.'+
RIGHT('000000'+CONVERT(varchar(6),count(*)+1),6)
FROM
fruitOrderList
WHERE
convert(char(10),keyinDT,111)=
convert(char(10),getdate(),111)
--重新進行資料新增作業
INSERT INTO fruitOrderList
SELECT
@oSN,prodID,qty,region,keyinDT
FROM INSERTED
SET NOCOUNT OFF
GO
|
最後讀者可以使用在單一個批次,同時新增三筆資料,過程中請注意有關訂單序號的資料行【orderID】不需要給值,最後完成新增後INSTEAD OF觸發程序自動會給予一個唯一的日期序號。
|
--測試作業
--新增資料
注意訂單編號是自動產生
insert into fruitOrderList
values(null,3,30,'TP',GETDATE())
insert into fruitOrderList
values(null,6,10,'TC',GETDATE())
insert into fruitOrderList
values(null,9,20,'KS',GETDATE())
SELECT * FROM
fruitOrderList
GO
--結果,圖0
|
注意事項
【使用INSTEAD
OF觸發程序需要留意以下的事項】
在使用INSTEAD
OF觸發程序之前,要留意以下幾件重點。
1.
INSTEAD OF觸發程序執行時機,會在條件約束Primary
Key等之前。
2.
INSTEAD OF觸發程序執行過程,可以使用INSERTED記錄新增的資料或是修改後的資料,使用DELETED記錄刪除的資料或是修改前的資料。
3.
INSTEAD OF觸發程序定義過程中避免使用CURSOR,可以直接使用INSERTED或是DELETED快取資料表,可以實作出迴圈的效果。
關鍵字搜尋與技巧
INSTEAD OF,替代觸發程序,發生時機在條件約束之前,定義內容會取代DML陳述的作業動作。
DELETED,是觸發程序或是OUTPUT陳述式經過DML執行後,保留異動前資料。
INSERTED,是觸發程序或是OUTPUT陳述式經過DML執行後,保留異動後資料。
沒有留言:
張貼留言