Thought of Genius

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天一隻，
如果一周之內改變不了局面， 我就吃你。"

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

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


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

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


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

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