麻豆av福利av久久av,99精品视频在线免费观看,国产不卡免费视频

觸發(fā)器簡析

INSTEAD OF 觸發(fā)器

　　AFTER 觸發(fā)器（也叫“FOR”觸發(fā)器）會在觸發(fā) insert、update 或是delect 動作之后執(zhí)行。例如，一個 Employees 表上的 AFTER 觸發(fā)器會在在 Employee 表上執(zhí)行一條 update 語句后激活。因此，AFTER 觸發(fā)器只有在已插入一行或是多行和所有約束已被處理且通過后才觸發(fā)。INSTEAD OF 觸發(fā)器和 AFTER 觸發(fā)器有本質(zhì)上的不同，因為 INSTEAD OF 觸發(fā)器代替觸發(fā)動作進行激發(fā)。就拿同樣的例子來說，如果在 Emplyees 表上有一個 INSTEAD OF UPDATE 觸發(fā)器和在這個表上執(zhí)行一條 UPDATE 語句，結(jié)果是這條 UPDATE 語句并不會改變 Employee 表中的任何一行。相反,這條 UPDATE 語句只有是為了踢離 INSTEAD OF UPDATE 觸發(fā)器，這個觸發(fā)器可能會，也可能不會改變 Employees 表中的數(shù)據(jù)。

　　因此，怎么決定在合適的時間和位置放置 INSTEAD OF 觸發(fā)器呢？有幾個關(guān)鍵的因素在做決定是值得考慮的。AFTER 觸發(fā)器多用在動作必須在表中數(shù)據(jù)發(fā)生改變之后才執(zhí)行后情情況。比如，AFTER 觸發(fā)器可以用于將對數(shù)據(jù)作任何變動的日志記錄在一個相對獨立的審計表中。INTEAD OF 觸發(fā)器也能做同樣的工作。但是 INSTEAD OF 觸發(fā)器在這個情況下的效率比較低，因為更新動作只能在將它發(fā)生的動作準確地記錄在審計表之后才允許執(zhí)行。

　　一般來說，只要不影響數(shù)據(jù)的修改，AFTER 觸發(fā)器比 INSTEAD OF 觸發(fā)器更有效率。在對數(shù)據(jù)進行計算或是對數(shù)據(jù)的修改作為一個整體提交或是作為一個整體回退的情況下，AFTER 觸發(fā)器也是一個很好的選擇。例如，存在這樣一條規(guī)則：對在 Products 表的產(chǎn)品價格的變動超過30％的必須回退。AFTER 觸發(fā)器能很漂亮地完成這個工作，它利用已插入同已刪除的表中的產(chǎn)品價格作比較，然后在有必要的時回滾事務(wù)。這些都是 AFTER 觸發(fā)器的理想條件，但有時 INSTEAD OF會更好些。

　　INSTEAD OF 觸發(fā)器有一個很大的特點——就是它允許你在某個表或視圖上用多個復(fù)雜的查詢操作來代替單一的查詢。跟 AFTER 觸發(fā)器只能對表起作用不同，INSTEAD OF 觸發(fā)器可以同時對表和視圖起作用。我常常被問到怎么樣去解決這種情況：有一個多表組成的視圖，如何對該視圖進行一次更新。如果視圖包含有關(guān)鍵字段和包含有基本表的某些字段，這只是簡單的更新基本表。但是，當(dāng)有視圖中包含有多個基本表示，邏輯上的更新比單單一個 UPDATE 語句會更復(fù)雜。因此，你是怎么利用什么可以替代的工具來解決這個問題的呢？其中一個方法就是將一個INSTEAD OF 觸發(fā)器放在視圖上。INSTEAD OF 觸發(fā)器可以定義在一個或多個表上.INSTEAD OF 觸發(fā)器就能轉(zhuǎn)開在多個基本表中修改的范圍.

　　例如，如果一個視圖將 Customers、Products、orders 和 OrderDteils 等表合并成一個視圖，并利用視圖通過程序在屏幕上來顯示所有的數(shù)據(jù)。更新操作便允許用來代替這個視圖，假如存在一個這個樣的視圖：它包含 Northwind 數(shù)據(jù)庫中的四個表，并且被命名為vwCustomersOrdersOrderDetailsProducts，它看起來像這樣(Figure 1)：

Figure 1 連接 Customers 及其 Order Details 的視圖

CREATE VIEW vwCustomersOrdersOrderDetailsProducts

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

　　vwCustomersOrdersOrderDetailsProducts 視圖連接著四個表，并且每個表都暴露一個取樣字段。必須記住的一點是，當(dāng)你設(shè)計一個含有 INSTEAD OF UPDATE 的觸發(fā)器時，將每個表的主關(guān)鍵字段包含在SELECT語句中是很有益的做法。即使這些字段在應(yīng)用程序不會用到，它們也以在 INSTEAD OF 觸發(fā)器中用來定位將要被修改的行，然后對基表作相應(yīng)的修改。假設(shè)你打算允許更新該視圖以便按非關(guān)鍵字過濾基表。更新代碼應(yīng)該寫在 INSTEAD OF UPDATE 觸發(fā)器中，讓觸發(fā)器去更新 Customers 表中的 CompnayName 列，Orders 表中的 OrderDate 列，Order Details 表的 UnitPrice 和 Quantity 列以及在 Products 表中的 ProductName 列。在這種情況下，使用 AFTER 觸發(fā)器就不適合了，而 INSTEAD OF 觸發(fā)器則是一個很好的選擇，參見 Figure 2： Figure 2 用 INSTEAD OF 觸發(fā)器更新視圖

CREATE TRIGGER tr_vwCustomersOrdersOrderDetailsProducts_IO_U

ON vwCustomersOrdersOrderDetailsProducts

INSTEAD OF UPDATE

— 更新 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

　　注意在 Figure 2 中的 INSTEAD OF UPDATE 觸發(fā)器包含了四個 UPDATE 語句。每個 UPDATE語句目的都是為了對其中一個基表中的非關(guān)鍵字段進行修改。在 UPDATE 語句中包含了每個表中的關(guān)鍵字段對應(yīng)于視圖中的字段。這樣就允許 UPDATE 語句在相應(yīng)的表中定位對應(yīng)的列并只對這些列作修改。下面的 UPDATE 語句將對 INSTEAD OF 觸發(fā)器進行測試： UPDATE vwCustomersOrdersOrderDetailsProducts

SET Quantity = 100,

UnitPrice = 20,

CompanyName = ''''Fake Name'''',

OrderDate = ''''11/23/2001'''',

ProductName = ''''Widget''''

WHERE OrderID = 10265

AND ProductID = 17 　　

　　如果你（通過視圖或是表自身）檢查相應(yīng)表中的值，很明顯，這些值已被更新了。當(dāng)然，對INSTEAD　OF 觸發(fā)器作一些改變會使其有不同的結(jié)果。例如，不存在寫一個觸發(fā)器去改變四個基表的需求，因此，可以將觸發(fā)器中的一個或是多個 UPDATE 語句刪去。假設(shè) INSTEAD　OF 觸發(fā)器僅僅是為了更新 Order Details 表的值，這就會僅僅更新在 Order Details 表中的字段，而忽視任何在其他基表上的修改。在這種情況下，在 Customers,Products 或是 Orders 表中不會產(chǎn)生任何錯誤同時也不會發(fā)生任何改變。當(dāng)然，如果這三個表中的某些字段發(fā)生改變的話，會發(fā)生報錯。如我呆會在這篇文章會討論的一樣，UPDATE 和 COLUMNS＿UPDATED 函數(shù)是個檢測哪些字段發(fā)生改變的理想的方法。

　　Figure 2 也演示了怎么寫一個觸發(fā)器修改多行記錄。注意到 UPDATE 語句如何按關(guān)鍵字連接被插入的表和各個基表。這就保證更新是對所有的行，這些行在視圖中被原有的 UPDATE 語句修改。通過循環(huán)被插入表的記錄行也能完成該操作。不管怎么樣，通常避免使用游標(biāo)是個好主意，尤其是在使用觸發(fā)器時更應(yīng)如此。SQL SERVER 被設(shè)計成以數(shù)據(jù)集的方式來處理數(shù)據(jù)，而游標(biāo)是為一次處理一個數(shù)據(jù)行而設(shè)計的。在觸發(fā)器中使用游標(biāo)會降低程序的性能，因此，最好能使用象 Figure 2 中那樣更有效代替方法或使用一個子查詢。

　　另一個改變 INSERT OF UPDATE 觸發(fā)器的方法就是使其在視圖的 INSERT 和 DELETE 語句中激發(fā)。這也就意味著在適當(dāng)?shù)牡胤剑|發(fā)器會實現(xiàn) INSERT 或是 DELETE 的功能。但是必須記隹的是 DELETE 可能會刪除多個記錄，這關(guān)鍵在于觸發(fā)器是怎樣寫的。因此，檢查觸發(fā)器的需求，在實現(xiàn)之前進行測試，這些做法十分重要。INSERT　OF UPDATE 觸發(fā)器可寫在視圖中，因此它可插入一個新的顧客、訂單、詳細的訂單和產(chǎn)品。這個觸發(fā)器也可以用來在插入一個新顧客之前檢查這個顧客是否是新的(對其它記錄的操作也是一樣)。當(dāng)采用的是 INSTEAD OF 觸發(fā)器時存在有許多機會，但是，當(dāng)然，觸發(fā)器是為解決相應(yīng)的需求這才是它的本質(zhì)。

　　通常，當(dāng)引用一張表的 UPDATE 語句試圖去賦值一個計算型的，恒等型的或是時間戳型的列時，會產(chǎn)生一個錯誤，因為這些列的值必須是由SQL SERVER來決定的。這些列必須被包含在UPDATE 語句中以便能滿足列不能為空的要求。但是，如果 UPDATE 語句用 INSTEAD OF 觸發(fā)器引用一個視圖，定義在觸發(fā)器中的邏輯可以旁路掉這些列來避免錯誤的發(fā)生。為了達到這個目的，觸發(fā)器決不能嘗試去更新基表中相應(yīng)列的值(讓它們遠離 UPDATE 語句的 SET 從句)。當(dāng)某一條被處理的記錄來自被插入的表時，計算型的，恒等型的或是時間戳型的列可以用一個虛假值以滿足不為空值的要求，這時，INSTEAD OF 觸發(fā)器將忽略這些值，正確的值由 SQL SERVER 設(shè)置。

更新分開的列

　　INSTEAD OF 觸發(fā)器也很普遍地用于更新基表中計算型的列。例如，假設(shè)存在有如下這樣一個叫 vwOrdersOrderDetailsProducts 的視圖： CREATE VIEW vwOrdersOrderDetailsProducts

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

　　這個視圖揭示了一個計算型的列叫ExtendedPrice，這個列不能被直接被更新，因為它不能將其自己變?yōu)楸碇歇毩⒌囊涣小ｋm然你可實現(xiàn)這樣一個生意規(guī)則，在這個規(guī)則中ExtendedPrice通過這個視圖來修改，Quantity列不應(yīng)修改，但是UnitPrice可被修改(我知道這條規(guī)則有點奇怪，但我可以忍受這點)。可以寫一個INSTEAD OF UPDATE觸發(fā)器來增強這條生意規(guī)則，其代碼如下所示： CREATE TRIGGER tr_vwOrdersOrderDetailsProducts_IO_U

ON vwOrdersOrderDetailsProducts

INSTEAD OF UPDATE

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

　　這些代碼揭示了怎樣用一個在INSTEAD　OF　觸發(fā)器中的邏輯來代替對一個計算型列的更新。假設(shè)一個產(chǎn)品在一張?zhí)囟ǖ亩▎伪碇蠶uantity為100而ExtendedPrice要更新為200，這時新的UnitPrice值就變?yōu)?。在這種情況下，在執(zhí)行一個對ExtendedPrice列進行修改的UPDATE語句時，最終的結(jié)果是UnitPrice被賦為ExtendedPrice除以Quantity的商。下面的代碼可以用來測試這種情況： UPDATE vwOrdersOrderDetailsProducts

SET ExtendedPrice = 200

WHERE OrderID = 10265

AND ProductID = 17

檢查改變

　　在INSTEAD　OF和AFTER觸發(fā)器中都有UPDATE和COLUMNS＿UPDATE功能，這二種功能允許由觸發(fā)器決定哪些字段由觸發(fā)器的語句來改變。例如，下面的觸發(fā)器阻止任何對Employees表中的lastname字段進行修改。在這里，UPDATE功能用來決定對哪些對字段的修改可以執(zhí)行。如果超出發(fā)生了改變（而又是不允許修改的）就會產(chǎn)生一個錯誤。PAISERR OR功能和事務(wù)就會回退，回退會撤消所做的任何修改。UPDATE功能都可以在AGTER觸發(fā)器和INSTEAD OF觸發(fā)器中工作，而不是在外部工作。 CREATE TRIGGER tr_Employees_U on Employees AFTER UPDATE AS

IF UPDATE(lastname)

BEGIN

RAISERROR (''''cannot change lastname'''', 16, 1)

ROLLBACK TRAN

RETURN

END

　　 UPDATE功能是為了判斷單一列是否被INSERT或是UPDATE語句修改過。UPDATE(列)是一個用來檢測更新的標(biāo)準的方法。但是當(dāng)需要用來他檢測多列是否受到INSERT或UPDATE語句的影響時就變得更低效率。而這恰恰是COLUM_UPDATE功能的一個亮點。COLUMN_UPDATE功能返回一個位掩碼來判斷特定的列是否被修改過。位掩碼是包含在被表中被修改的列中的一個比特，目的是為了在表模式中定義這些列。如果一行修改，這比特位的值就為1，否則為0。不像從右到左地讀字節(jié)的常規(guī)方法，位掩碼是從左往右讀。例如，下面的代碼提示了一個在Order Details表中的觸發(fā)器，這個觸發(fā)器是為了檢測Quantity和UnitPrice二個字段是否被修改過。 CREATE TRIGGER tr_OrderDetails ON [Order Details] AFTER UPDATE

IF (COLUMNS_UPDATED() = 12)

BEGIN

RAISERROR (''''Cannot change both UnitPrice and Quantity at the

same time'''', 16, 1)

ROLLBACK TRAN

END

　　如果這個字段都被修改了，就會產(chǎn)生一個錯誤，同時事務(wù)也將回滾。就拿Order Details表來說，COLUMN_UPDATED功能返回代表Order Details表中字段的五個字節(jié)。只要第三和第四個字段被修改，上面這種情況就會發(fā)生，它檢測這些位是不是已賦值為1.當(dāng)?shù)谌偷谒奈欢即蜷_的慶，它就如：00110。L因這個位掩碼代表2次冪，第一位表示1，第二位表示2，第三位表示4，第四位表示8，第五位表示16（是的，這是和正常二進制數(shù)相反的順序）；因此只表示UnitPrice和Quantity字段被修改位掩碼的值為00110，這個值為12（4+8）。請注意，這個觸發(fā)器只有在UnitPrice和Quantity字段被修改才會將事務(wù)回滾。如果其他字段修改的話，位掩碼就會不一樣，因此就不等于整數(shù)12了。如果觸發(fā)器被修改為禁止對這二個字段修改即使對其他字段也禁止，它就可重新編寫為如下: ALTER TRIGGER tr_OrderDetails ON [Order Details] AFTER UPDATE

IF (COLUMNS_UPDATED() >= 12)

BEGIN

RAISERROR (''''Cannot change both UnitPrice and Quantity

at the same time'''', 16, 1)

ROLLBACK TRAN

END

　　請注意　COLUMN_UPDATED　功能現(xiàn)在是怎么去檢測位掩碼的但是否小于等于12.如果你修改聯(lián)系UnitPrice,Quantity和Discount列的話，位掩碼就變?yōu)?0111，代表整數(shù)28（4+8+16）。當(dāng)在一個表中不止有8個列時，這個函數(shù)就會先返回包含了前八列的五個字節(jié)，而第從第九到第十六就會在第二個字節(jié)中，以此類推。這個功能在決定允許哪些列可以被更新比只對第列進行更新的UPDATE功能更有用。

　　如前期所描述的一樣，在潢足特定條件規(guī)則條件下，觸發(fā)器可以回滾事務(wù)，當(dāng)一個含有回滾的觸發(fā)器在SQL腳本中執(zhí)行時，整個處理將被取消。因此，被觸發(fā)動作修改的的所有數(shù)據(jù)將由ROLLBACK TRANSACION語句回滾。雖然一個回滾并不阻止觸發(fā)執(zhí)行SQL語句所有在ROLLBACK TRANSACION語句后面的語句都會被執(zhí)行。特別是當(dāng)一個觸發(fā)器繼續(xù)執(zhí)行回滾語句后面的語句時，在回滾以后所作的任何修改都不會回滾。發(fā)生這種情況是因為當(dāng)在觸發(fā)器中執(zhí)行了一個ROLLBACK TRANSACTION時，所以有的事務(wù)都被取消。因此當(dāng)一個新的查詢語句被執(zhí)行時，一個新的不同與以前事務(wù)的事務(wù)就重新開始。因此，一般情況下，建議不要在ROLLBACK TRANSACTION語句后放置任何語句。

　　像回滾不會自動退出觸發(fā)器一樣，它也不會自動產(chǎn)生錯誤。如果必須回滾且必會產(chǎn)生錯誤，PAISERR OR語句應(yīng)該放在退出觸發(fā)器代碼前，緊跟在回滾后.

結(jié)束語

　　在對同一個表的數(shù)據(jù)所作的修改會激發(fā)同樣的INSTEAD OF觸發(fā)器，這種觸發(fā)器不會遞歸調(diào)用。因此,如果在Emplyee表中有一個INSTEAD OF觸發(fā)器,P這個觸發(fā)器是用來更新Employee表的，這并不會發(fā)生調(diào)用同一個INSTEAD OF觸發(fā)器。如果允許這種遞旭的話，更新應(yīng)該被禁止。INSTEAD OF觸發(fā)器和AFTER觸發(fā)器的另一個不同在于Text,Ntext和Image列可以出現(xiàn)在被更新和刪除的表的觸發(fā)器中。這些二進制列會以如VARCAHAR數(shù)值出現(xiàn)在更新和刪除表的觸發(fā)器中，這種是可行的，但這并不是他們原始的數(shù)據(jù)類型。

　　這有一個有用的存儲過程-sp_helptrigger 系統(tǒng)存儲過程-來檢測觸發(fā)器。他返回定義在表上的觸發(fā)器類型，這個表是傳遞給存儲過程的。用這種方法，你可以看到哪些觸發(fā)器和某個表有關(guān)聯(lián)，什么操作動觸發(fā)這些觸發(fā)器和判斷觸發(fā)器是AFTER觸發(fā)器還是INSTEAD OF觸發(fā)器。

　　在最后二欄中，我已經(jīng)討論了AFTER觸發(fā)器和INSTEAD OF觸發(fā)器的多個方面。當(dāng)然，還有許多情形下他們很有用，還有許多使用時機也沒有提出來。當(dāng)一個觸發(fā)器必須查詢其他表的情況下，觸發(fā)器就會沒有什么效率了。在這些情況下，觸發(fā)器的性能和觸發(fā)動作會受到很大損害。當(dāng)使用得好時，觸發(fā)器是一個很棒的工具，但是必須保證在使用他們之前必須對你的程序作一個全面的測試。

posted on 2010-05-18 17:49 淡淡的回憶閱讀(220) 評論(0) 編輯收藏

新用戶注冊刷新評論列表


只有注冊用戶登錄后才能發(fā)表評論。




網(wǎng)站導(dǎo)航: 博客園 IT新聞 Chat2DB C++博客博問管理

gcw633

觸發(fā)器簡析

導(dǎo)航

統(tǒng)計

常用鏈接

留言簿

隨筆檔案

搜索

最新評論

閱讀排行榜

評論排行榜