《游標和觸發(fā)器》ppt課件

1、select_statement：是定義游標結(jié)果集的規(guī)范：是定義游標結(jié)果集的規(guī)范 SELECT語句，它可以是一個完好語法和語義的語句，它可以是一個完好語法和語義的Transact-SQL的的SELECT語句。語句。在定義了在定義了 INSERT 觸發(fā)器的表上執(zhí)行觸發(fā)器的表上執(zhí)行 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觸發(fā)器動作被執(zhí)行觸發(fā)器動作被執(zhí)行OrderID105221052310524ProductID2417UnitPrice31.009.6530.00Quantity7924Discount 5 19.002 0.210523觸發(fā)器代碼：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、觸發(fā)器的表上執(zhí)行觸發(fā)器的表上執(zhí)行 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觸發(fā)器動作被執(zhí)行觸發(fā)器動作被執(zhí)行2 1 4UPDATE P SET Discontinued = 1FROM Products AS P INNER JOIN deleted AS dON P.CategoryID = d.CategoryID4Dairy Products Cheeses0 x15DELETE 語句刪除的行被記錄下來語句刪除的行被記錄下來在定義了在定