INSTEAD OF 觸發器 AFTER 觸發器(也叫“FOR”觸發器)會在觸發 insert、update 或是delect 動作之后執行。例如,一個 Employees 表上的 AFTER 觸發器會在在 Employee 表上執行一條 update 語句后激活。因此,AFTER 觸發器只有在已插入一行或是多行和所有約束已被處理且通過后才觸發。INSTEAD OF 觸發器和 AFTER 觸發器有本質上的不同,因為 INSTEAD OF 觸發器代替觸發動作進行激發。就拿同樣的例子來說,如果在 Emplyees 表上有一個 INSTEAD OF UPDATE 觸發器和在這個表上執行一條 UPDATE 語句,結果是這條 UPDATE 語句并不會改變 Employee 表中的任何一行。相反,這條 UPDATE 語句只有是為了踢離 INSTEAD OF UPDATE 觸發器,這個觸發器可能會,也可能不會改變 Employees 表中的數據。 因此,怎么決定在合適的時間和位置放置 INSTEAD OF 觸發器呢?有幾個關鍵的因素在做決定是值得考慮的。AFTER 觸發器多用在動作必須在表中數據發生改變之后才執行后情情況。比如,AFTER 觸發器可以用于將對數據作任何變動的日志記錄在一個相對獨立的審計表中。INTEAD OF 觸發器也能做同樣的工作。但是 INSTEAD OF 觸發器在這個情況下的效率比較低,因為更新動作只能在將它發生的動作準確地記錄在審計表之后才允許執行。 一般來說,只要不影響數據的修改,AFTER 觸發器比 INSTEAD OF 觸發器更有效率。在對數據進行計算或是對數據的修改作為一個整體提交或是作為一個整體回退的情況下,AFTER 觸發器也是一個很好的選擇。例如,存在這樣一條規則:對在 Products 表的產品價格的變動超過30%的必須回退。AFTER 觸發器能很漂亮地完成這個工作,它利用已插入同已刪除的表中的產品價格作比較,然后在有必要的時回滾事務。這些都是 AFTER 觸發器的理想條件,但有時 INSTEAD OF會更好些。 INSTEAD OF 觸發器有一個很大的特點——就是它允許你在某個表或視圖上用多個復雜的查詢操作來代替單一的查詢。跟 AFTER 觸發器只能對表起作用不同,INSTEAD OF 觸發器可以同時對表和視圖起作用。我常常被問到怎么樣去解決這種情況:有一個多表組成的視圖,如何對該視圖進行一次更新。如果視圖包含有關鍵字段和包含有基本表的某些字段,這只是簡單的更新基本表。但是,當有視圖中包含有多個基本表示,邏輯上的更新比單單一個 UPDATE 語句會更復雜。因此,你是怎么利用什么可以替代的工具來解決這個問題的呢?其中一個方法就是將一個INSTEAD OF 觸發器放在視圖上。INSTEAD OF 觸發器可以定義在一個或多個表上.INSTEAD OF 觸發器就能轉開在多個基本表中修改的范圍. 例如,如果一個視圖將 Customers、Products、orders 和 OrderDteils 等表合并成一個視圖,并利用視圖通過程序在屏幕上來顯示所有的數據。更新操作便允許用來代替這個視圖,假如存在一個這個樣的視圖:它包含 Northwind 數據庫中的四個表,并且被命名為vwCustomersOrdersOrderDetailsProducts,它看起來像這樣(Figure 1): Figure 1 連接 Customers 及其 Order Details 的視圖 CREATE VIEW vwCustomersOrdersOrderDetailsProducts AS SELECT c.CustomerID, c.CompanyName, o.OrderID, o.OrderDate, od.UnitPrice, od.Quantity, od.Discount, p.ProductID, p.ProductName FROM Customers c INNER JOIN Orders o ON c.CustomerID = o.CustomerID INNER JOIN [Order Details] od ON o.OrderID = od.OrderID INNER JOIN Products p ON od.ProductID = p.ProductID GO vwCustomersOrdersOrderDetailsProducts 視圖連接著四個表,并且每個表都暴露一個取樣字段。必須記住的一點是,當你設計一個含有 INSTEAD OF UPDATE 的觸發器時,將每個表的主關鍵字段包含在SELECT語句中是很有益的做法。即使這些字段在應用程序不會用到,它們也以在 INSTEAD OF 觸發器中用來定位將要被修改的行,然后對基表作相應的修改。假設你打算允許更新該視圖以便按非關鍵字過濾基表。更新代碼應該寫在 INSTEAD OF UPDATE 觸發器中,讓觸發器去更新 Customers 表中的 CompnayName 列,Orders 表中的 OrderDate 列,Order Details 表的 UnitPrice 和 Quantity 列以及在 Products 表中的 ProductName 列。在這種情況下,使用 AFTER 觸發器就不適合了,而 INSTEAD OF 觸發器則是一個很好的選擇,參見 Figure 2: Figure 2 用 INSTEAD OF 觸發器更新視圖 CREATE TRIGGER tr_vwCustomersOrdersOrderDetailsProducts_IO_U ON vwCustomersOrdersOrderDetailsProducts INSTEAD OF UPDATE AS — 更新 Customers UPDATE Customers SET CompanyName = i.CompanyName FROM inserted i INNER JOIN Customers c ON i.CustomerID = c.CustomerID — 更新 Orders UPDATE Orders SET OrderDate = i.OrderDate FROM inserted i INNER JOIN Orders o ON i.OrderID = o.OrderID — 更新 Order Details UPDATE [Order Details] SET UnitPrice = i.UnitPrice, Quantity = i.Quantity FROM inserted i INNER JOIN [Order Details] od ON i.OrderID = od.OrderID AND i.ProductID = od.ProductID — 更新 Products UPDATE Products SET ProductName = i.ProductName FROM inserted i INNER JOIN Products p ON i.ProductID = p.ProductID GO 注意在 Figure 2 中的 INSTEAD OF UPDATE 觸發器包含了四個 UPDATE 語句。每個 UPDATE語句目的都是為了對其中一個基表中的非關鍵字段進行修改。在 UPDATE 語句中包含了每個表中的關鍵字段對應于視圖中的字段。這樣就允許 UPDATE 語句在相應的表中定位對應的列并只對這些列作修改。下面的 UPDATE 語句將對 INSTEAD OF 觸發器進行測試: UPDATE vwCustomersOrdersOrderDetailsProducts SET Quantity =100, UnitPrice =20, CompanyName =''''Fake Name'''', OrderDate =''''11/23/2001'''', ProductName =''''Widget'''' WHERE OrderID =10265 AND ProductID =17 如果你(通過視圖或是表自身)檢查相應表中的值,很明顯,這些值已被更新了。當然,對INSTEAD OF 觸發器作一些改變會使其有不同的結果。例如,不存在寫一個觸發器去改變四個基表的需求,因此,可以將觸發器中的一個或是多個 UPDATE 語句刪去。假設 INSTEAD OF 觸發器僅僅是為了更新 Order Details 表的值,這就會僅僅更新在 Order Details 表中的字段,而忽視任何在其他基表上的修改。在這種情況下,在 Customers,Products 或是 Orders 表中不會產生任何錯誤同時也不會發生任何改變。當然,如果這三個表中的某些字段發生改變的話,會發生報錯。如我呆會在這篇文章會討論的一樣,UPDATE 和 COLUMNS_UPDATED 函數是個檢測哪些字段發生改變的理想的方法。 Figure 2 也演示了怎么寫一個觸發器修改多行記錄。注意到 UPDATE 語句如何按關鍵字連接被插入的表和各個基表。這就保證更新是對所有的行,這些行在視圖中被原有的 UPDATE 語句修改。通過循環被插入表的記錄行也能完成該操作。不管怎么樣,通常避免使用游標是個好主意,尤其是在使用觸發器時更應如此。SQL SERVER 被設計成以數據集的方式來處理數據,而游標是為一次處理一個數據行而設計的。在觸發器中使用游標會降低程序的性能,因此,最好能使用象 Figure 2 中那樣更有效代替方法或使用一個子查詢。 另一個改變 INSERT OF UPDATE 觸發器的方法就是使其在視圖的 INSERT 和 DELETE 語句中激發。這也就意味著在適當的地方,觸發器會實現 INSERT 或是 DELETE 的功能。但是必須記隹的是 DELETE 可能會刪除多個記錄,這關鍵在于觸發器是怎樣寫的。因此,檢查觸發器的需求,在實現之前進行測試,這些做法十分重要。INSERT OF UPDATE 觸發器可寫在視圖中,因此它可插入一個新的顧客、訂單、詳細的訂單和產品。這個觸發器也可以用來在插入一個新顧客之前檢查這個顧客是否是新的(對其它記錄的操作也是一樣)。當采用的是 INSTEAD OF 觸發器時存在有許多機會,但是,當然,觸發器是為解決相應的需求這才是它的本質。 通常,當引用一張表的 UPDATE 語句試圖去賦值一個計算型的,恒等型的或是時間戳型的列時,會產生一個錯誤,因為這些列的值必須是由SQL SERVER來決定的。這些列必須被包含在UPDATE 語句中以便能滿足列不能為空的要求。但是,如果 UPDATE 語句用 INSTEAD OF 觸發器引用一個視圖,定義在觸發器中的邏輯可以旁路掉這些列來避免錯誤的發生。為了達到這個目的,觸發器決不能嘗試去更新基表中相應列的值(讓它們遠離 UPDATE 語句的 SET 從句)。當某一條被處理的記錄來自被插入的表時,計算型的,恒等型的或是時間戳型的列可以用一個虛假值以滿足不為空值的要求,這時,INSTEAD OF 觸發器將忽略這些值,正確的值由 SQL SERVER 設置。 更新分開的列 INSTEAD OF 觸發器也很普遍地用于更新基表中計算型的列。例如,假設存在有如下這樣一個叫 vwOrdersOrderDetailsProducts 的視圖: CREATE VIEW vwOrdersOrderDetailsProducts AS SELECT o.OrderID, o.OrderDate, od.UnitPrice * od.Quantity AS ExtendedPrice, p.ProductID, p.ProductName FROM Orders o INNER JOIN [Order Details] od ON o.OrderID = od.OrderID INNER JOIN Products p ON od.ProductID = p.ProductID GO 這個視圖揭示了一個計算型的列叫ExtendedPrice,這個列不能被直接被更新,因為它不能將其自己變為表中獨立的一列。雖然你可實現這樣一個生意規則,在這個規則中ExtendedPrice通過這個視圖來修改,Quantity列不應修改,但是UnitPrice可被修改(我知道這條規則有點奇怪,但我可以忍受這點)??梢詫懸粋€INSTEAD OF UPDATE觸發器來增強這條生意規則,其代碼如下所示: CREATE TRIGGER tr_vwOrdersOrderDetailsProducts_IO_U ON vwOrdersOrderDetailsProducts INSTEAD OF UPDATE AS UPDATE [Order Details] SET UnitPrice = i.ExtendedPrice / Quantity FROM inserted i INNER JOIN [Order Details] od ON i.OrderID = od.OrderID AND i.ProductID = od.ProductID GO 這些代碼揭示了怎樣用一個在INSTEAD OF 觸發器中的邏輯來代替對一個計算型列的更新。假設一個產品在一張特定的定單表中Quantity為100而ExtendedPrice要更新為200,這時新的UnitPrice值就變為2。在這種情況下,在執行一個對ExtendedPrice列進行修改的UPDATE語句時,最終的結果是UnitPrice被賦為ExtendedPrice除以Quantity的商。下面的代碼可以用來測試這種情況: UPDATE vwOrdersOrderDetailsProducts SET ExtendedPrice =200 WHERE OrderID =10265 AND ProductID =17 檢查改變 在INSTEAD OF和AFTER觸發器中都有UPDATE和COLUMNS_UPDATE功能,這二種功能允許由觸發器決定哪些字段由觸發器的語句來改變。例如,下面的觸發器阻止任何對Employees表中的lastname字段進行修改。在這里,UPDATE功能用來決定對哪些對字段的修改可以執行。如果超出發生了改變(而又是不允許修改的)就會產生一個錯誤。PAISERR OR功能和事務就會回退,回退會撤消所做的任何修改。UPDATE功能都可以在AGTER觸發器和INSTEAD OF觸發器中工作,而不是在外部工作。 CREATE TRIGGER tr_Employees_U on Employees AFTER UPDATE AS IF UPDATE(lastname) BEGIN RAISERROR (''''cannot change lastname'''', 16, 1) ROLLBACK TRAN RETURN END GO UPDATE功能是為了判斷單一列是否被INSERT或是UPDATE語句修改過。UPDATE(列)是一個用來檢測更新的標準的方法。但是當需要用來他檢測多列是否受到INSERT或UPDATE語句的影響時就變得更低效率。而這恰恰是COLUM_UPDATE功能的一個亮點。COLUMN_UPDATE功能返回一個位掩碼來判斷特定的列是否被修改過。位掩碼是包含在被表中被修改的列中的一個比特,目的是為了在表模式中定義這些列。如果一行修改,這比特位的值就為1,否則為0。不像從右到左地讀字節的常規方法,位掩碼是從左往右讀。例如,下面的代碼提示了一個在Order Details表中的觸發器,這個觸發器是為了檢測Quantity和UnitPrice二個字段是否被修改過。 CREATE TRIGGER tr_OrderDetails ON [Order Details] AFTER UPDATE AS IF (COLUMNS_UPDATED() =12) BEGIN RAISERROR (''''Cannot change both UnitPrice and Quantity at the same time'''', 16, 1) ROLLBACK TRAN END GO 如果這個字段都被修改了,就會產生一個錯誤,同時事務也將回滾。就拿Order Details表來說,COLUMN_UPDATED功能返回代表Order Details表中字段的五個字節。只要第三和第四個字段被修改,上面這種情況就會發生,它檢測這些位是不是已賦值為1.當第三和第四位都打開的慶,它就如:00110。L因這個位掩碼代表2次冪,第一位表示1,第二位表示2,第三位表示4,第四位表示8,第五位表示16(是的,這是和正常二進制數相反的順序);因此只表示UnitPrice和Quantity字段被修改位掩碼的值為00110,這個值為12(4+8)。請注意,這個觸發器只有在UnitPrice和Quantity字段被修改才會將事務回滾。如果其他字段修改的話,位掩碼就會不一樣,因此就不等于整數12了。如果觸發器被修改為禁止對這二個字段修改即使對其他字段也禁止,它就可重新編寫為如下: ALTER TRIGGER tr_OrderDetails ON [Order Details] AFTER UPDATE AS IF (COLUMNS_UPDATED() >=12) BEGIN RAISERROR (''''Cannot change both UnitPrice and Quantity at the same time'''', 16, 1) ROLLBACK TRAN END GO 請注意 COLUMN_UPDATED 功能現在是怎么去檢測位掩碼的但是否小于等于12.如果你修改聯系UnitPrice,Quantity和Discount列的話,位掩碼就變為00111,代表整數28(4+8+16)。當在一個表中不止有8個列時,這個函數就會先返回包含了前八列的五個字節,而第從第九到第十六就會在第二個字節中,以此類推。這個功能在決定允許哪些列可以被更新比只對第列進行更新的UPDATE功能更有用。 如前期所描述的一樣,在潢足特定條件規則條件下,觸發器可以回滾事務,當一個含有回滾的觸發器在SQL腳本中執行時,整個處理將被取消。因此,被觸發動作修改的的所有數據將由ROLLBACK TRANSACION語句回滾。雖然一個回滾并不阻止觸發執行SQL語句所有在ROLLBACK TRANSACION語句后面的語句都會被執行 。特別是當一個觸發器繼續執行回滾語句后面的語句時,在回滾以后所作的任何修改都不會回滾。發生這種情況是因為當在觸發器中執行了一個ROLLBACK TRANSACTION時,所以有的事務都被取消。因此當一個新的查詢語句被執行時,一個新的不同與以前事務的事務就重新開始。因此,一般情況下,建議不要在ROLLBACK TRANSACTION語句后放置任何語句。 像回滾不會自動退出觸發器一樣,它也不會自動產生錯誤。如果必須回滾且必會產生錯誤,PAISERR OR語句應該放在退出觸發器代碼前,緊跟在回滾后. 結束語 在對同一個表的數據所作的修改會激發同樣的INSTEAD OF觸發器,這種觸發器不會遞歸調用。因此,如果在Emplyee表中有一個INSTEAD OF觸發器,P這個觸發器是用來更新Employee表的,這并不會發生調用同一個INSTEAD OF觸發器。如果允許這種遞旭的話,更新應該被禁止。INSTEAD OF觸發器和AFTER觸發器的另一個不同在于Text,Ntext和Image列可以出現在被更新和刪除的表的觸發器中。這些二進制列會以如VARCAHAR數值出現在更新和刪除表的觸發器中,這種是可行的,但這并不是他們原始的數據類型。 這有一個有用的存儲過程-sp_helptrigger 系統存儲過程-來檢測觸發器。他返回定義在表上的觸發器類型,這個表是傳遞給存儲過程的。用這種方法,你可以看到哪些觸發器和某個表有關聯,什么操作動觸發這些觸發器和判斷觸發器是AFTER觸發器還是INSTEAD OF觸發器。 在最后二欄中,我已經討論了AFTER觸發器和INSTEAD OF觸發器的多個方面。當然,還有許多情形下他們很有用,還有許多使用時機也沒有提出來。當一個觸發器必須查詢其他表的情況下,觸發器就會沒有什么效率了。在這些情況下,觸發器的性能和觸發動作會受到很大損害。當使用得好時,觸發器是一個很棒的工具,但是必須保證在使用他們之前必須對你的程序作一個全面的測試。
create PROCEDURE [dbo].[GetUserS] ( @empno nvarchar(32),--輸入參數 @pwd nvarchar(32)--輸入參數 ) AS BEGIN select * from [user] where empno=@empno and pwd=@pwd END GO --這個存儲過程輸出的結果是名字和密碼相符合的整一條記錄
數據訪問層中的代碼
publicint getUser(String name, String pass) { // TODO Auto-generated method stub Session session=this.getSession(); String sql1="{call GetUserS(?,?)}"; Query query=session.createSQLQuery(sql1); query.setString(0, name); query.setString(1, pass); List list =query.list(); if(list.size()>0){ return1; } return0; }