Thought of Genius

假設現在我們有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 />