MS SQL server 筆記#3 SQL-ALTER CONSTRAINT

/ *
  * ALTER TABLE AND ADD A CONSTRAINT
  * 改變資料表結構:
  * * 改變欄位資料類型
  * * 加入限制(CONSTRAINT)
*/


-- 變更一個欄位(COLUMN)的資料類型為 datetime。
ALTER TABLE [history_data] ALTER COLUMN [create_date] [datetime]

-- 加入限制(CONSTRAINT)名為col_a_def；
-- Default在insert資料時，欄位create_date自動填入系統時間。
ALTER TABLE [history_data] ADD CONSTRAINT col_a_def DEFAULT sysdatetime() for [create_date];

DEFAULT@W3C、DEFAULT@MSDN

-- 刪除限制。
ALTER TABLE [history_data] DROP CONSTRAINT [col_a_def]




// end

MS SQL server 筆記#2 SQL-trigger for insert

/ *
  * Trigger for history
  * 情況:
  * 大量資料須記錄做歷史紀錄，在sever端指定insert進某一張表後觸發trigger，
  * 能依照當前月份分配至該月份的表；或動態建立當月份的表。
  */

先對一個table(叫table_name)建立trigger::
-- begin sql
use db_name
go
create trigger trigger_name on table_name
instead of insert as
-- instead of 代表有insert動作進入這張表時(table_name)，做下面的動作取代掉insert。
-- 也可使用 for/after 則本來那筆資料一樣會插入表內(table_name)，並做下面的動作。
-- 也可以針對其他如 delete/update 動作發生時寫個trigger。

-- 宣告與給值::
DECLARE @table_variable_name nvarchar(25);
DECLARE @year nvarchar(4);
DECLARE @mon nvarchar(2);
DECLARE @equip_id int;
DECLARE @string_variable nvarchar(13);
SET @year = convert(nvarchar,DATEPART (year, GETDATE()));
SET @mon = convert(nvarchar,DATEPART (month, GETDATE()));
-- DATEPART 函數取得想要的時間單位，回傳的是整數，因為要做成字串再使用convert轉型別。
SET @table_variable_name = concat('history',@year, '_', @mon);
-- 組成字串 Eg: history2014_12 當做table的名稱(會隨著年份月份不同)。 concat

select @equip_id = equip_id from inserted;
select @string_variable = a_string from inserted;
-- 從暫存的inserted表取得原先要插入table_name表的值，指定給兩個變數。 inserted & deleted

-- 檢查201412月份的歷史資料表有沒有存在，沒有就建立。
IF NOT EXISTS (select * from db_name.sys.objects where name = @table_variable_name)
-- 使用Exec函數執行語法，可以解決直接使用語法時不能用字串變數做為table名稱(必須宣告table變數)
begin
Exec('CREATE TABLE ' + @table_variable_name +'( [ID] [int] IDENTITY(1,1) PRIMARY KEY,
[col_1] [int] NULL,
[col_2] [nvarchar](MAX) NULL,
[create_time] [datetime] NOT NULL DEFAULT GETDATE() ) ')
end
-- 改插入資料到該月份的表也用exec函數執行
Exec('insert into ' + @table_variable_name + '(col_1, col_2) values (' + @equip_id + ', ''' + @string_variable + ''')');
-- 變數是字串的值要再加個單引號，要再用一個單引號加上本來要的那個單引號前；要加入 ' 要用> ''
go
-- end sql

當在server端使用上面語法::
insert equip_id, a_string into table_name values(1234, 'how are you');
會觸發上面已建立的trigger，將原先要插入表table_name的值改插入目前年月份的別張表。

當然在server端也可以做檢查該年月份表存不存在、建立、插入的動作；
或乾脆一次建立好每個月的表，在server端判斷年月份就可以；
使用這個方法是讓server端不做這件事，統一插入某張表後，就交給資料庫自己處理，也減少去access資料庫的時間。


// note: delete trigger:: drop trigger trigger_name
// end

MS SQL server 筆記#1 SQL語法錯誤

常常被MS的SQL語法搞得很怒火呀... 筆記一下犯過的錯誤吧

/ *
  * 查詢語法小錯誤，要查詢資料類型為 nvarchar
  */

原本錯誤的語法:

SELECT c1, c2 FROM table1
WHERE step_no = "0A"

錯誤訊息:
無法將step_no裡的資料類型轉換為int

正確語法:

SELECT c1, c2 FROM table1
WHERE step_no = '0A'


只是 " 與 ' 之差異阿!!!!



//end