履歴テーブルの検討中


自動付番では制御できなさそうなのでuniqueidにしたが、結局シーケンスに。
とりあえずマージがなければ親たぐり子たぐり可能で最新を追っていけるか。

CREATE SEQUENCE testSeq AS INT
    START WITH 1
    INCREMENT BY 1 ;
GO

CREATE TABLE testTab (
  --myid UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
  myid INT PRIMARY KEY DEFAULT NEXT VALUE FOR testSeq,
  hid HIERARCHYID,
  from_date DATETIME DEFAULT '1970/1/1',
  to_date DATETIME DEFAULT '9999/12/31',
  txt NVARCHAR(MAX)
);
GO

INSERT INTO testTab (txt) VALUES (');
GO

SELECT * FROM testTab;
GO

CREATE TRIGGER testTab_update ON testTab 
  FOR UPDATE
AS
  DECLARE @nowDt AS DATETIME = GETDATE();
  DECLARE @myId AS INT =  NEXT VALUE FOR testSeq;

  UPDATE dst SET
	dst.myid = @myId,
    dst.from_date = @nowDt,
	dst.hid = (CASE WHEN src.hid IS NULL
	   THEN
	    CONVERT(HIERARCHYID,'/' + CONVERT(VARCHAR(38),src.myid) + '/' 
		 + CONVERT(VARCHAR(38),@myId) + '/')
       ELSE 
	    CONVERT(HIERARCHYID,src.hid.ToString()
	            + CONVERT(VARCHAR(38),@myId) + '/')
     END)
  FROM
    testTab dst
  INNER JOIN
    deleted src
  ON
    dst.myid = src.myid;

  INSERT INTO testTab
    ( myid ,hid, to_date, txt )
  SELECT
    myid, 
    (CASE WHEN hid IS NULL
	   THEN
	     CONVERT(HIERARCHYID,'/' + CONVERT(VARCHAR(38),myid) + '/')
       ELSE 
	     hid
    END)
  , @nowDt, txt from deleted;
GO

CREATE TRIGGER testTab_delete ON testTab 
  FOR DELETE
AS
  DECLARE @nowDt AS DATETIME = GETDATE();

  INSERT INTO testTab
   ( hid, from_date, to_date ,txt )
  SELECT  
    (CASE WHEN hid IS NULL
	   THEN
	     CONVERT(HIERARCHYID,'/' + CONVERT(VARCHAR(38),myid) + '/')
       ELSE 
	     hid
    END)
  , from_date, @nowDt ,txt from deleted;
GO

UPDATE testTab
 SET
  txt = N'   
 WHERE
  txt = N';
GO

SELECT hid.ToString(),* FROM testTab;
GO
カテゴリー: 開発, 設計 タグ: パーマリンク