《游标和触发器》ppt课件

1、select_statement：是定义游标结果集的规范：是定义游标结果集的规范 SELECT语句，它可以是一个完好语法和语义的语句，它可以是一个完好语法和语义的Transact-SQL的的SELECT语句。语句。在定义了在定义了 INSERT 触发器的表上执行触发器的表上执行 INSERT 语句语句INSERT Order Details VALUES(10523, 2, 19.00, 5, 0.2)OrderID105221052310524ProductID2417UnitPrice31.009.6530.00Quantity7924Discount 5 19.002

2、 0.210523INSERT 语句插入的行被记录下来语句插入的行被记录下来10523219.0050.2触发器动作被执行触发器动作被执行OrderID105221052310524ProductID2417UnitPrice31.009.6530.00Quantity7924Discount 5 19.002 0.210523触发器代码：USE NorthwindCREATE TRIGGER OrdDet_InsertON Order DetailsFOR INSERTASUPDATE P SET UnitsInStock = (P.UnitsInStock I.Quan

3、tity)FROM Products AS P INNER JOIN Inserted AS ION P.ProductID = I.ProductIDUPDATE P SET UnitsInStock = (P.UnitsInStock I.Quantity)FROM Products AS P INNER JOIN Inserted AS ION P.ProductID = I.ProductIDProductID UnitsInStock 123415106520215INSERT 语句插入的行被记录下来语句插入的行被记录下来10523219.0050.2在定义了在定义了 DELETE

4、触发器的表上执行触发器的表上执行 DELETE 语句语句4Dairy Products Cheeses0 x15DELETE 语句删除的行被记录下来语句删除的行被记录下来CategoryID123CategoryNameBeveragesCondimentsConfectionsDescriptionSoft drinks, coffeesSweet and savory Desserts, candies, Picture0 x150 x150 x15 0 x15CheesesDairy Products4DELETE CategoriesWHERE CategoryID = 4USE No

5、rthwindCREATE TRIGGER Category_DeleteON CategoriesFOR DELETEASUPDATE P SET Discontinued = 1FROM Products AS P INNER JOIN deleted AS dON P.CategoryID = d.CategoryIDProductID Discontinued categoryid12340000触发器动作被执行触发器动作被执行2 1 4UPDATE P SET Discontinued = 1FROM Products AS P INNER JOIN deleted AS dON P.CategoryID = d.CategoryID4Dairy Products Cheeses0 x15DELETE 语句删除的行被记录下来语句删除的行被记录下来在定义了在定