Thought of Genius

以往在同一個Connection當中，分別以多個DataReader搭配Command物件，或者是透過DataAdapter讀取資料時，一次只有一個命令可以執行查詢，查詢的動作也會循序地執行。我們無法一邊讀取資料，一邊利用相同的Connection，來進行其他資料的查詢、新增、修改、刪除等等動作。
現在我們可以利用SQL Server 2005所提供的功能MARS (Multiple Active Result Sets)，它允許在單一資料庫連線上執行多的批次作業，並同時操作多個查詢結果集，或同時進行資料的新增、修改、刪除作業。例如，當應用程式開啟一個DataReader而且尚未關閉的情況下，我們可以利用相同的連線，送出另一個查詢命令到資料來源。
預設MARS功能是停用的，必須在連線字串中，增加一組鍵值方可啟用。

<br /><connectionStrings><br /> <add name="DataAccess.Properties.Settings.NorthwindConStr" <br /> connectionString="Data Source=localhost;Initial Catalog=Northwind;User ID=sa; Password=P@$sW0rD; MultipleActiveResultSets=True"<br /> providerName="System.Data.SqlClient" /><br /></connectionStrings><br /><br /><br />public class MARS {<br /> private SqlConnection m_Connection = new SqlConnection(Properties.Settings.Default.NorthwindConStr);<br /> private string _ErrorStr;<br /> public string ErrorStr {<br /> get { return this._ErrorStr; }<br /> set { this._ErrorStr = value; }<br /> }<br /><br /> public bool ExecuteMars() {<br /> bool result = false;<br /> string cmdstr1 = @"SELECT ProductID, UnitPrice FROM Products";<br /> string cmdstr2 = @"UPDATE Products SET UnitPrice = @UnitPrice WHERE ProductID = @ProductID";<br /> SqlCommand cmd1 = new SqlCommand(cmdstr1, m_Connection);<br /> SqlCommand cmd2 = new SqlCommand(cmdstr2, m_Connection);<br /> cmd2.Parameters.Add("@ProductID", SqlDbType.Int);<br /> cmd2.Parameters.Add("@UnitPrice", SqlDbType.Money);<br /> try {<br /> m_Connection.Open();<br /> SqlDataReader dr = cmd1.ExecuteReader();<br /> while (dr.Read()) {<br /> cmd2.Parameters[0].Value = Convert.ToInt32(dr[0]);<br /> cmd2.Parameters[1].Value = Convert.ToDouble(dr[1])*1.2;<br /> cmd2.ExecuteNonQuery();<br /> }<br /> result = true;<br /> }<br /> catch (Exception ex) {<br /> this.ErrorStr = ex.Message.ToString();<br /> }<br /> finally {<br /> m_Connection.Close();<br /> }<br /> return result;<br /> }<br />}<br /><br />

Oracle資料庫的DML觸發程式是由DML敘述所激發，而敘述的類型決定了DML觸發程式的類型。它可以針對INSERT、UPDATE、DELETE操作定義，也可以在所有的資料列或特定的資料列起作用。敘述層次的觸發程式使用WHEN子句，來評定是否有發生特定類型的改變。在觸發程式內，你可以使用 :old 或 :new的繫結變數，來存取目前被處理的資料。PL/SQL編譯器會把它當成由類型組成的紀錄： triggering_table%ROWTYPE ，定義此觸發程式的表格。使用方式為 :old.field 或 :new.field。它也提供三個布林函數可以判定操作類型，分別為INSERTING、UPDATING、DELETING，當敘述符合時，它就是TRUE；否則就是FALSE。

<br />-- Create actual tble and trace table<br />CREATE TABLE REPORT (<br /> REPORTNO NUMBER(10) NOT NULL,<br /> REGDATE DATE NOT NULL,<br /> NOTE VARCHAR2(1024)<br />);<br />CREATE TABLE REPORT_TRACE (<br /> ACTION VARCHAR2(6) NOT NULL,<br /> ACTIONTIME DATE NOT NULL,<br /> REPORTNO NUMBER(10) NOT NULL,<br /> REGDATE DATE NOT NULL,<br /> NOTE VARCHAR2(1024)<br />);<br /><br />-- Create trigger to audit<br />CREATE OR REPLACE TRIGGER TR_REPORT<br />AFTER INSERT OR UPDATE OR DELETE ON REPORT<br />FOR EACH ROW <br />WHEN (new.REPORTNO > 1000 OR old.REPORTNO > 1000)<br />BEGIN<br /> IF INSERTING THEN<br /> INSERT INTO REPORT_TRACE VALUES('INSERT', SYSDATE, :new.REPORTNO, :new.REGDATE, :new.NOTE);<br /> ELSIF UPDATING THEN<br /> INSERT INTO REPORT_TRACE VALUES('UPDATE', SYSDATE, :new.REPORTNO, :new.REGDATE, :new.NOTE);<br /> ELSIF DELETING THEN<br /> INSERT INTO REPORT_TRACE VALUES('DELETE', SYSDATE, :old.REPORTNO, :old.REGDATE, :old.NOTE);<br /> END IF;<br />END;<br />/<br /><br />-- Input data<br />INSERT INTO REPORT VALUES(100, sysdate, 'frist record');<br />INSERT INTO REPORT VALUES(1501, sysdate, 'second record');<br />UPDATE REPORT SET NOTE = 'frist record update' WHERE REPORTNO = 100;<br />UPDATE REPORT SET NOTE = 'second record update' WHERE REPORTNO = 1501;<br />DELETE REPORT WHERE REPORTNO IN (100, 1501);<br />COMMIT;<br /><br />-- Query data<br />SELECT * FROM REPORT;<br />SELECT * FROM REPORT_TRACE;<br /><br />-- Drop table<br />DROP TABLE REPORT CASCADE CONSTRAINTS;<br />DROP TABLE REPORT_TRACE CASCADE CONSTRAINTS;<br />

假設現在我們有4個需求分別是：
1. 新增訂單之後，將庫存量減去訂單的訂購產品數量。
2. 修改產品單價的時後，不得超過原始價值的10%。
3. 如果訂單被刪除了，系統可以寄Mail通知管理人員。
4. 新增訂單時，訂單流水號能夠依照日期所產生，例如:200803270123。
我們要如何建立觸發程序，並達到我們的需求 ?

觸發程序簡易說明：
Trigger Definition：
1. 觸發程序是依附資料表或檢視存在，本身是整個交易的延伸。
2. 從應用程式進行資料處理異動作業開始到觸發程序執行完畢，都屬於同一個交易的範圍。
DML Trigger：
(1) After Trigger：在執行Insert、Update、Delete陳述式的動作之後執行。
(2) Instead of Trigger：取代原先的Insert、Update、Delete的動作。

我們先建立兩個測試用的資料表，並依照需求順序建立對應的觸發程序。
<br />-- Create table and input data<br />CREATE TABLE Product(<br /> [ProductNo] int NOT NULL,<br /> [ProductName] nvarchar(10)NOT NULL,<br /> [Price] money,<br /> [Stock] int,<br /> CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED (ProductNo)<br />);<br />CREATE TABLE OrderDetail(<br /> [OrderNo] nvarchar(12) NOT NULL,<br /> [ProductNo] int NOT NULL,<br /> [Quantity] int NOT NULL,<br /> [OrderDate] datetime,<br /> CONSTRAINT [PK_OrderDetail] PRIMARY KEY CLUSTERED (OrderNo),<br /> CONSTRAINT [FK_Product_OrderDetail] FOREIGN KEY (ProductNo) REFERENCES Product (ProductNo)<br />);<br />INSERT INTO Product VALUES (1,'A', 100, 30);<br />INSERT INTO Product VALUES (2,'B', 150, 20);<br />INSERT INTO Product VALUES (3,'C', 200, 10);<br />INSERT INTO OrderDetail VALUES (200801170001, 1, 3, '2008-01-17 12:54:46.587');<br />INSERT INTO OrderDetail VALUES (200801170002, 2, 5, '2008-01-17 14:32:19.153');<br />INSERT INTO OrderDetail VALUES (200802020001, 3, 7, '2008-02-02 13:24:35.273');<br />GO<br /><br /><br /><br />-- AFTER INSERT<br />CREATE TRIGGER Tri_AfterInsert ON OrderDetail AFTER INSERT<br />AS<br /> DECLARE @Pid int<br /> DECLARE @Qty int<br /> SELECT @Pid=ProductNo, @Qty=Quantity FROM INSERTED;<br /> UPDATE Product SET Stock=Stock-@Qty WHERE ProductNo = @Pid;<br />GO<br /> <br />-- AFTER UPDATE<br />CREATE TRIGGER Tri_AfterUpdate ON Product AFTER UPDATE<br />AS<br /> DECLARE @PriceBefore money<br /> DECLARE @PriceAfter money<br /> DECLARE @Difference money<br /> IF UPDATE(Price)<br /> BEGIN<br /> SET @PriceBefore = (SELECT Price FROM DELETED)<br /> SET @PriceAfter = (SELECT Price FROM INSERTED)<br /> SET @Difference = (@PriceBefore-@PriceAfter)/@PriceBefore<br /> IF (@Difference > 0.1) OR (@Difference < -0.1)<br /> BEGIN<br /> ROLLBACK<br /> END<br /> END<br />GO<br /><br />-- AFTER DELETE<br />CREATE TRIGGER Tri_AfterDelete ON OrderDetail AFTER DELETE<br />AS<br /> IF (SELECT COUNT(*) FROM DELETED) > 0<br /> BEGIN<br /> DECLARE @MSGsubject nvarchar(max)<br /> DECLARE @MSGbody nvarchar(max)<br /> SET @MSGsubject = (SELECT N'Order form deletion notice. Deleted by ' + SUSER_SNAME())<br /> SET @MSGbody = (SELECT * FROM DELETED FOR XML AUTO, ELEMENTS, ROOT('Data'))<br /> EXEC MSDB.dbo.sp_send_dbmail @profile_name='DBA Mail Notice', @recipients = 'eric@mail.dsic.com.tw',<br /> @subject = @MSGsubject, @body = @MSGbody, @body_format=TEXT<br /> END<br />GO<br /><br />-- INSTEAD OF INSERT<br />CREATE TRIGGER Tri_InsteadOfInsert ON OrderDetail INSTEAD OF INSERT<br />AS<br /> DECLARE @oid bigint<br /> DECLARE @today smalldatetime<br /> DECLARE @count int<br /> SELECT @today = CONVERT(datetime,CONVERT(char(10),GETDATE(),111))<br /> SELECT @count = COUNT(*)+1 FROM OrderDetail WHERE OrderDate >= @today AND OrderDate < DateAdd(DAY, 1, @today)<br /> SELECT @oid = CONVERT(bigint, CONVERT(nvarchar(10), @today,112))*10000 + @count <br /> INSERT INTO OrderDetail<br /> SELECT @oid, ProductNo, Quantity, OrderDate FROM INSERTED<br />GO<br /><br /><br /><br />-- Test trigger<br />SELECT * FROM Product<br />SELECT * FROM OrderDetail<br />INSERT INTO OrderDetail (ProductNo, Quantity, OrderDate) VALUES (1, 5,GETDATE())<br />UPDATE Product SET Price = Price*30 WHERE ProductNo = 1<br />Delete OrderDetail<br /><br /><br /><br />--Cleaning up environment<br />DROP TABLE OrderDetail<br />DROP TABLE Product<br />

一隻兔子在山洞前寫文章，一隻狼走了過來，問："兔子啊，你在幹什麼？"
兔子答曰："寫文章。
"狼問：" 什麼題目？
"兔子答曰："《淺談兔子是怎樣吃掉狼的》。"
狼哈哈大笑，表示不信，於是兔子把狼領進山洞。
過了一會，兔子獨自走出山洞，繼續寫文章。

一隻野豬走了過來，問："兔子你在寫什麼？
"兔子答："文章。"
野豬問："題目是什麼？
"兔子答："《淺談兔子是如何把野豬吃掉的》。
"野豬不信，於是同樣的事情發生。

最後，在山洞裡，
一隻獅子在一堆白骨之間，滿意的剔著牙讀著兔子交給它的文章：
題目是：《一隻動物，能力大小關鍵要看你的老闆是誰》


這隻兔子有次不小心告訴了他的一個兔子朋友，這消息逐漸在森林中傳播；
獅子知道後非常生氣，他告訴兔子："如果這個星期沒有食物進洞，我就吃你。"

於是兔子繼續在洞口寫文章。
一隻小鹿走過來，"兔子，你在幹什麼啊？"
兔子答："寫文章"
"小鹿問：什麼題目？"
"兔子答曰："《淺談兔子是怎樣吃掉狼的》。"
"哈哈，這個事情全森林都知道啊，你別胡弄我了，我是不會進洞的"
"我馬上要退休了，獅子說要找個人頂替我，
難道你不想這篇文章的兔子變成小鹿麼？"
小鹿想了想，終於忍不住誘惑，跟隨兔子走進洞裡。
過了一會，兔子獨自走出山洞，繼續寫文章。

一隻小馬走過來，同樣是事情發生了。
最後，在山洞裡，
一隻獅子在一堆白骨之間，滿意的剔著牙讀著兔子交給它的文章：
題目是：《如何發展下線動物為老闆提供食物》


隨著時間的推移，獅子越長越大，兔子的食物已遠遠不能填飽肚子。
一日，他告訴兔子：
"我的食物量要加倍，例如：原來4天一隻小鹿，現在要2天一隻，
如果一周之內改變不了局面， 我就吃你。"

於是，兔子離開洞口，跑進森林深處，他見到一隻狼。
"你相信兔子能輕鬆吃掉狼嗎"
狼哈哈大笑，表示不信，於是兔子把狼領進山洞。
過了一會，兔子獨自走出山洞，繼續進入森林深處
這回他碰到一隻野豬----"你相信兔子能輕鬆吃掉野豬嗎"
野豬不信，於是同樣的事情發生了。

原來森林深處的動物並不知道兔子和獅子的故事。
最後，在山洞裡，
一隻獅子在一堆白骨之間，滿意的剔著牙讀著兔子交給它的文章
題目是：《如何實現由坐商到行商的轉型為老闆提供更多的食物》


時間飛快，轉眼之間，兔子在森林裡的名氣越來越大。
因為大家都知道它有一個很厲害的老闆。
這只小兔開始橫行霸道，欺上欺下，沒有動物敢惹牠 ，
兔子時時想起和烏龜賽跑的羞辱。
牠找到烏龜說：" 三天之內，見我老闆！"揚長而去。
烏龜難過的哭了，
這時卻碰到了一位獵人，烏龜把這事告訴了他。
獵人哈哈大笑，
於是森林裡發生了一件重大事情，
獵人披著獅子皮和烏龜一起在吃兔子火鍋。

地下丟了半張紙片歪歪扭扭的寫著：
《山外青山樓外樓，強中還有強中手啊》！！


在很長一段時間裡森林裡恢復了往日的寧靜，
兔子吃狼的故事似乎快要被大家忘記了，
不過一隻年輕的老虎在聽說了這個故事後，被激發了靈感。
於是他抓住了一隻羚羊，對羚羊說，
如果你可以像以前的兔子那樣為我帶來食物那我就不吃你。
於是，羚羊無奈的答應了老虎，而老虎也悠然自得的進了山洞。
可是三天過去了，也沒有見羚羊領一隻動物進洞。
他實在憋不住了，想出來看看情況。

羚羊早已不在了，他異常憤怒。
正在他暴跳如雷的時候突然發現了羚羊寫的一篇文章
題目是：《想要做好老闆先要懂得怎樣留住員工》

有些事，現在不做，一輩子都不會做了 !
希望在20出頭的生命裡，做一件到80歲想起來都還會微笑的事 !
這是電影「練習曲」觸動人心的幾句話語。
年輕時要勇於製造回憶，當你年老的時候就是品嘗這些回憶的時候。

在一次上時間管理的課上，教授在桌上放了一個空罐子，
然後又從桌子下面拿出一個大約拳頭大小，正好可以從罐口放進罐子的鵝卵石，
當教授把石塊放完後，問他的學生道:"你們說這罐子是不是滿的?"

"是"所有的學生異口同聲地回答說。
"真的嗎?"教授笑著問，

然後再從桌底下拿出一袋碎石子，把碎石子從罐口倒下去搖一搖，再加一些，
於是再問他班上的學生:" 你們說，這罐子現在是不是滿的?"

這回他的學生不敢答得太快。
最後，班上有位學生怯生生地細聲答道:"也許沒有滿"，

"很好!"教授說完後，又從桌下拿出一袋沙子， 然後把沙子慢慢倒進罐子，
倒完後再問班上的學生:"現在你們告訴我，這個罐子是滿的呢、還是沒滿?"
"沒有滿"全班同學這下學乖了,大家都很有信心的回答說。

"好極了!"教授再一次稱讚這些孺子可教也的學生們。
稱讚完了後，教授從桌子底下拿出一大瓶水，
把水倒在看起來已經被鵝卵石，小碎石沙子填滿了的罐子。

當這些事都做完後,教授正色的問他班上的同學：
"我們從上面這些事情學到了什麼重要的功課?"

班上一陣沉默,然後一位自以為聰明的學生回答說:
"無論我們的工作多忙,行程排得多滿,如果要逼一下的話,還是可以多做些事的。
這位學生回答完後心中很得意地想:"這門課到底講得是時間管理啊!"

教授聽到這樣的回答後，點一點頭，微笑道:
"答案不錯，但這並不是我要告訴你們的重要訊息。"

說到這裡,這位教授故意頓住，用眼睛向全班同學掃一遍後說:
"我想告訴各位最重要的訊息是：
如果你不先將大的鵝卵石放進罐子去，你也許以後永遠沒機會把它們再放進去了。"