|
示例
在本例中，創(chuàng)建了一個德國客戶表和一個墨西哥客戶表。放置在視圖上的INSTEAD OF觸發(fā)器將把更新操作重新定向到適當?shù)幕砩稀＿@時發(fā)生的插入是對CustomersGer表的插入而不是對視圖的插入。
創(chuàng)建兩個包含客戶數(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)建一個在上述視圖上的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
通過更新視圖，測試觸發(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’
那么具體的講，對于多列數(shù)據(jù)，如何計算方差呢？：
CREATE TRIGGER [calT1T2T3] ON dbo.DCLB
FOR INSERT,UPDATE
AS
update P
SET
/**//*
計算方差的觸發(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ā)器的使用很方便，而且也很簡單，重要的是理解inserted過程?？蓪PDATE語句看成兩步操作：即捕獲數(shù)據(jù)前像(before image)的DELETE語句，和捕獲數(shù)據(jù)后像(after image)的INSERT語句。當在定義有觸發(fā)器的表上執(zhí)行UPDATE語句時，原始行（前像）被移入到deleted表，更新行（后像）被移入到inserted表。觸發(fā)器檢查deleted表和inserted表以及被更新的表，來確定是否更新了多行以及如何執(zhí)行觸發(fā)器動作。 |
