示例

　　在本例中，創(chuàng)建了一個(gè)德國(guó)客戶(hù)表和一個(gè)墨西哥客戶(hù)表。放置在視圖上的INSTEAD OF觸發(fā)器將把更新操作重新定向到適當(dāng)?shù)幕砩?。這時(shí)發(fā)生的插入是對(duì)CustomersGer表的插入而不是對(duì)視圖的插入。

　　創(chuàng)建兩個(gè)包含客戶(hù)數(shù)據(jù)的表：

SELECT * INTO CustomersGer FROM Customers WHERE Customers.Country = ‘Germany‘ SELECT * INTO CustomersMex FROM Customers WHERE Customers.Country = ‘Mexico‘ GO

　　在該數(shù)據(jù)上創(chuàng)建視圖：

CREATE VIEW CustomersView AS SELECT * FROM CustomersGer UNION SELECT * FROM CustomersMex GO

　　創(chuàng)建一個(gè)在上述視圖上的INSTEAD OF觸發(fā)器：

CREATE TRIGGER Customers_Update2 ON CustomersView INSTEAD OF UPDATE AS DECLARE @Country nvarchar(15) SET @Country = (SELECT Country FROM Inserted) IF @Country = ‘Germany‘ BEGIN UPDATE CustomersGer SET CustomersGer.Phone = Inserted.Phone FROM CustomersGer JOIN Inserted ON CustomersGer.CustomerID = Inserted.CustomerID END ELSE IF @Country = ‘Mexico‘ BEGIN UPDATE CustomersMex SET CustomersMex.Phone = Inserted.Phone FROM CustomersMex JOIN Inserted ON CustomersMex.CustomerID = Inserted.CustomerID END

　　通過(guò)更新視圖，測(cè)試觸發(fā)器：

UPDATE CustomersView SET Phone = ‘ 030-007xxxx‘ WHERE CustomerID = ‘ALFKI‘ SELECT CustomerID, Phone FROM CustomersView WHERE CustomerID = ‘ALFKI‘ SELECT CustomerID, Phone FROM CustomersGer WHERE CustomerID = ‘ALFKI‘

　　那么具體的講，對(duì)于多列數(shù)據(jù)，如何計(jì)算方差呢？：

CREATE TRIGGER [calT1T2T3] ON dbo.DCLB FOR INSERT,UPDATE AS update P SET /**//* 計(jì)算方差的觸發(fā)器 */ P.T1=(I.P1+I.P2+I.P3+I.P4+I.P5+I.P6), P.T2=(I.Y1+I.Y2+I.Y3+I.Y4+I.Y5+I.Y6 ), P.T3=SQRT(P.T1*P.T1+P.T2*P.T2) FROM DCLB AS P INNER JOIN Inserted AS I ON P.SID = I.SID

　　觸發(fā)器的使用很方便，而且也很簡(jiǎn)單，重要的是理解inserted過(guò)程?？蓪PDATE語(yǔ)句看成兩步操作：即捕獲數(shù)據(jù)前像(before image)的DELETE語(yǔ)句，和捕獲數(shù)據(jù)后像(after image)的INSERT語(yǔ)句。當(dāng)在定義有觸發(fā)器的表上執(zhí)行UPDATE語(yǔ)句時(shí)，原始行（前像）被移入到deleted表，更新行（后像）被移入到inserted表。觸發(fā)器檢查deleted表和inserted表以及被更新的表，來(lái)確定是否更新了多行以及如何執(zhí)行觸發(fā)器動(dòng)作。