USE Northwind CREATE TRIGGER OrdDet_Insert ON [Order Details] FOR INSERT AS UPDATE P SET UnitsInStock = P.UnitsInStock – I.Quantity FROM Products AS P INNER JOIN Inserted AS I ON P.ProductID = I.ProductID |
USE Northwind CREATE TRIGGER Category_Delete ON Categories FOR DELETE AS UPDATE P SET Discontinued = 1 FROM Products AS P INNER JOIN deleted AS d ON P.CategoryID = d.CategoryID |
IF UPDATE (<column_name>) |
USE Northwind GO CREATE TRIGGER Employee_Update ON Employees FOR UPDATE AS IF UPDATE (EmployeeID) BEGIN RAISERROR ('Transaction cannot be processed.\ ***** Employee ID number cannot be modified.', 10, 1) ROLLBACK TRANSACTION END |
INSTEAD OF触发器的工作过程
可以在表或视图上指定INSTEAD OF触发器。执行这种触发器就能够替代原始的触发动作。INSTEAD OF触发器扩展了视图更新的类型。对于每一种触发动作(INSERT、UPDATE或 DELETE),每一个表或视图只能有一个INSTEAD OF触发器。
INSTEAD OF触发器被用于更新那些没有办法通过正常方式更新的视图。例如,通常不能在一个基于连接的视图上进行DELETE操作。然而,可以编写一个INSTEAD OF DELETE触发器来实现删除。上述触发器可以访问那些如果视图是一个真正的表时已经被删除的数据行。将被删除的行存储在一个名为deleted的工作表中,就像AFTER触发器一样。相似地,在UPDATE INSTEAD OF触发器或者INSERT INSTEAD OF触发器中,你可以访问inserted表中的新行。
不能在带有WITH CHECK OPTION定义的视图中创建INSTEAD OF触发器。
示例
在本例中,创建了一个德国客户表和一个墨西哥客户表。放置在视图上的INSTEAD OF触发器将把更新操作重新定向到适当的基表上。这时发生的插入是对CustomersGer表的插入而不是对视图的插入。
创建两个包含客户数据的表:
SELECT * INTO CustomersGer FROM Customers WHERE Customers.Country = 'Germany' SELECT * INTO CustomersMex FROM Customers WHERE Customers.Country = 'Mexico' GO |
CREATE VIEW CustomersView AS SELECT * FROM CustomersGer UNION SELECT * FROM CustomersMex GO |
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 |
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' |
CREATE TRIGGER [calT1T2T3] ON dbo.DCLB FOR INSERT,UPDATE AS update P SET /**//* 计算方差的触发器 */ 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 |
Word教程网 | Excel教程网 | Dreamweaver教程网 | Fireworks教程网 | PPT教程网 | FLASH教程网 | PS教程网 |
HTML教程网 | DIV CSS教程网 | FLASH AS教程网 | ACCESS教程网 | SQL SERVER教程网 | C语言教程网 | JAVASCRIPT教程网 |
ASP教程网 | ASP.NET教程网 | CorelDraw教程网 |