【-Ma の 筆記本-】

2012-02-26

MS SQL 利用Trigger紀錄Table異動資料

針對某些特定Table需要詳細記錄資料變更的歷史資訊，
可以利用Trigger及產生的inserted和deleted 臨時表格做紀錄！
作法可參考下列範例。

--建立測試表格
CREATE TABLE [dbo].[WIPPrd](
    [ProdNo] [varchar](16) NOT NULL,
    [PnameD] [nvarchar](100) NULL,
    [PType] [varchar](4) NULL,
    [InDay] [datetime] NULL,
    [UsrNo] [varchar](10) NULL,
    [UsrCo] [varchar](5) NULL) ON [PRIMARY]

--建立記錄檔
CREATE TABLE [dbo].[WIPPrd_Log](
    [MState] [varchar] (10) NOT NULL,
    [ProdNo] [varchar](16) NOT NULL,
    [PnameD] [nvarchar](100) NULL,
    [PType] [varchar](4) NULL,
    [InDay] [datetime] NULL,
    [UsrNo] [varchar](10) NULL,
    [UsrCo] [varchar](5) NULL )    
--建立TRIGGER-在WIPPrd表格更新、新增、刪除後觸發
CREATE TRIGGER dbo.TR_WIPPrd_Modify on dbo.WIPPrd AFTER UPDATE,INSERT,DELETE
AS
BEGIN   
    --表格異動資料時會產生暫存的inserted和deleted兩個表格
    --兩個表格格式資訊皆與原表格相同
    --inserted紀錄insert資料、update後資料
    --deleted紀錄delete資料、update前資料
   
    --依據異動方式將異動資料新增到記錄檔
    --inserted和deleted皆有資料表示為-UPDATE
    IF EXISTS (select 1 from inserted) and EXISTS (select 1 from deleted)
    BEGIN       
        insert into WIPPrd_Log select 'DELETE',* from deleted
        insert into WIPPrd_Log select 'INSERT',* from inserted
    END
    --inserted有資料deleted無資料表示為-INSERT
    ELSE IF EXISTS (select 1 from inserted) and Not EXISTS (select 1 from deleted)
        insert into WIPPrd_Log select 'INSERT',* from inserted
    --inserted無資料deleted有資料表示為-DELETE
    ELSE IF NOT EXISTS (select 1 from inserted) and EXISTS (select 1 from deleted)
        insert into WIPPrd_Log select 'DELETE',* from deleted

END
--測試
--清空資料 
delete from WIPPrd 
delete from WIPPrd_log
--新增資料
insert into WIPPrd values ('11','TEST_11','11',GETDATE(),'Dean','10001')
insert into WIPPrd values ('22','TEST_22','22',GETDATE(),'Dean','10001')
--更新資料
update WIPPrd set ProdNo='33',PnameD='TEST_33' where ProdNo='22'
--刪除資料
delete from WIPPrd
--查詢記錄檔
select * from WIPPrd_log
------------------------------------------------------------
INSERT    11    TEST_11    11    2012-02-26 14:17:47.750    Dean    10001
INSERT    22    TEST_22    22    2012-02-26 14:17:47.763    Dean    10001
DELETE    22    TEST_22    22    2012-02-26 14:17:47.763    Dean    10001
INSERT    33    TEST_33    22    2012-02-26 14:17:47.763    Dean    10001
DELETE    33    TEST_33    22    2012-02-26 14:17:47.763    Dean    10001
DELETE    11    TEST_11    11    2012-02-26 14:17:47.750    Dean    10001

參考自
~楓花雪岳~[SQL] Trigger - inserted & deleted Table
MSDN-使用 inserted 或 deleted 資料表
CSDN-Trigger中如何獲得更新前的數據

---------------------------------------------------------------------------
延伸閱讀-[MS SQL]-XML資料型態

2012-02-25

2012-02-18

Firefox portable、Chrome 暫存檔設至Ramdisk

Firefox
1.網址輸入 about:config
2.篩選條件 browser.cache.disk.parent_directory
數值 X:\TEMP(Ramdisk位置)
3.篩選條件 browser.cache.disk.capacity
數值 512000 (暫存空間大小)

若篩選條件不存在，請自行新增！

參考自明天的昨天
------------------------------------------------------

Chrome
1.建立執行檔捷徑
2.捷徑上右鍵內容-->分頁"捷徑"
3."目標(T)"欄位內，最後一個"後面空一格
4.填上--disk-cache-dir=X:\TEMP(Ramdisk位置)

參考自 [GC] 如何將Chrome的快取移動到Ramdisk

2012-02-17

MS SQL與SQLite語法使用差異

1.Top N

MS SQL  select Top 5 * from Table
SQLite  select * from Table limit 0,5

2.GETDATE()

MS SQL  select GETDATE()
SQLite  select datetime(CURRENT_TIMESTAMP,'localtime')

3.SELECT INTO

MS SQL select * into Table2 from Table
SQLite CREATE TABLE table2 AS  select * from Table

SQLite 相關網站備忘

1.SQLite官網 SQLite
2.ODBC驅動 SQLite ODBC Driver
3.語法文件 SQL As Understood By SQLite
4.DB免費管理工具 SQLiteStudio

2012-02-16

Delphi 中SQL Decimal資料型態小數位數進位問題

利用ADOQuery抓取資料，做數值運算時，
若SQL資料庫的資料型態為decimal(18,2)--2表示精準至小數2位，
小數進位方式為五捨六入，並非我們一般使用的四捨五入，
不知是否算為Delphi bug !

如35*1.777= 62.195
Delphi運算結果為62.19
正確應為62.20

var xDSubtot : Double
xDSubtot :=  Query_D.FieldByName('Qty').AsFloat * Query_D.FieldByName('UnitP').AsFloat;
Query_D.FieldByName('SubTot').AsVariant := FloatToStrF(xDSubtot,ffFixed,18,2);

Delphi 7連線SQLite資料庫

最近在研究於Delphi7中利用ODBC連線SQLite DB檔案，
將實做成功方法做個記錄。

先安裝SQLite ODBC Driver

//**在ADO內的ConnectionString直接帶入，不用連線精靈
procedure _SetSQLiteDBConnection;
var OD : TOpenDialog;
    ADOConn : TADOConnection;
    xDatabase : String;
begin
  OD := TOpenDialog.Create(NIL);

  OD.FileName := 'x:\mymoney';//**預設資料庫路徑
  OD.DefaultExt := '*.db';//**預設存檔格式
  OD.Filter := 'DB files (*.db)|*.DB';//**過濾顯示的檔案格式
  if Not OD.Execute then
  begin
    showmessage('選擇資料庫檔案失敗！');
    Abort;
  end;

  xDatabase := ';Database='+OD.FileName;
  ADOConn := TADOConnection.Create(NIL);
  ADOConn.LoginPrompt := False;
  ADOConn.ConnectionString := 'Driver={SQLite3 ODBC Driver}'+xDatabase;

  while Not ADOConn.Connected do
    ADOConn.Open;
end;

參考自网语飘飘.Net/Delphi攻坚战

2012-02-11

MS SQL Stored Procedure 初心者筆記

1.使用Exec執行動態SQL語法，若語法內有產生暫存Table，在執行結束後一併消失，故可以先在外面CREATE暫存表，再於EXEC內使用INSERT INTO

IF (select OBJECT_ID('tempdb..#TTmp')) IS NOT NULL DROP TABLE #TTmp
CREATE TABLE #TTmp (ProdNo varchar(16))
EXEC('INSERT INTO #TTmp select ProdNo from Prod')

2.WHERE IN篩選值不可以變數存放，如@Tmp = '1,2,3'，查詢資料集合不為1,2,3之結果 (解決方法)

2012-02-03

T-SQL WHERE IN 變數有多筆值查無資料錯誤

在撰寫Stored Procedure時，發現在where條件中若以宣告的變數篩選資料，
當變數為多筆值並搭配WHERE IN的時候會發生查詢不到資料的情況

DECLARE @xTmp VarCHAR(100)

SET @xTmp = 'A01,B01,B99'

select GPNa,DTNo,DTNa
from NOM
where GPNo='SO'
and DTNo in (@xTmp)

GPNa       DTNo           DTNa
---------- -------------- ----------------

(0 個資料列受到影響)

此時需自行新增一個切割字串放入暫存Table的Function來解決此問題

USE [DATABASENAME]

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FN_SplitStr]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[FN_SplitStr]

USE [DATABASENAME]
GO

CREATE FUNCTION [dbo].[FN_SplitStr](@xStr NVarchar(MAX),@xFStr varchar(1))
                RETURNS @TmpTable TABLE ( TmpStr NVarchar(MAX))
AS
BEGIN
    --@xStr 欲切割字串
    --@xFStr 切割用判定字元
    --@TmpTable 存放切割後資料

    DECLARE @xTmpStr NVarchar(MAX)--暫存字串

    SET @xStr = @xStr + @xFStr--分割字串最後加上一個分割字元
    SET @xTmpStr = ''--清空暫存字串
    WHILE CHARINDEX(@xFStr,@xStr)>0--判斷是否有分割字元存在
    BEGIN
        --SET @xTmpStr = @xTmpStr + SUBSTRING(@xStr,1,CHARINDEX(@xFStr,@xStr)-1)
        SET @xTmpStr = SUBSTRING(@xStr,1,CHARINDEX(@xFStr,@xStr)-1)
        IF Replace(@xTmpStr,' ','') <> ''--不為空值才寫入@TmpTable
            INSERT INTO @TmpTable (TmpStr) values (@xTmpStr)        SET @xStr = SUBSTRING(@xStr,CHARINDEX(@xFStr,@xStr)+1,LEN(@xStr))
    END

    RETURN
END

DECLARE @xTmp VarCHAR(100)
SET @xTmp = 'A01,B01,B99'

select GPNa,DTNo,DTNa
from NOM
where GPNo='SO'
and DTNo in (select * from dbo.FN_SplitStr(@xTmp,','))

GPNa       DTNo           DTNa
---------- -------------- ----------------
成品出入庫類別    A01     生產入庫
成品出入庫類別    B01     銷貨出庫
成品出入庫類別    B99     其他出庫

(3 個資料列受到影響)

參考自
[TSQL]Procdure 或 SqlParameter 來下SQL指令 Where In 多個值的變數解決方法
在T-SQL進行字串分割

2012-02-02

[分享]記帳小管家-免傳輸線更新PC與Android帳本資料

首先還是要感謝慕雲大提供帳務小管家這麼好用的軟體，
電腦版的記帳作了兩年多了，昨天開始連Android也一併授權了，
在這邊提供自己目前使用的配套同步方式，可以省略傳輸線的動作！

必要條件：
1.這麼讚的軟體，請先贊助
2.申請Dropbox帳號並於電腦端安裝程式
電腦玩物-Dropbox 雲端資料同步軟體能用來幹嘛？10個我最常被滿足需求
3.手機安裝 Dropsync-設定手機自動同步資料夾

想法：
利用Dropsync來自動同步dropbox內的檔案(目前Dropbox官方Android版程式無此功能)，
等於在每個有安裝dropbox的Client都有一份Android帳務的資料庫檔案，
我們在PC帳務小管家就可以很方便的指定我們剛剛設定存放資料庫的資料夾，
進行同步，而不用使用到傳輸線，同步完成後，當手機Dropsync下次自動執行同步時，
就會將同步後的資料庫檔案更新到手機內，這樣是不是方便很多呢？

訂閱：文章 (Atom)

【-Ma の筆記本-】