SQL第10章11章事务与触发器

1、 在定义了在定义了 INSERT 触发器的表上执行触发器的表上执行 INSERT 语句语句 INSERT Order Details VALUES (10523, 2, 19.00, 5, 0.2) OrderID 10522 10523 10524 ProductID 2 41 7 UnitPrice 31.00 9.65 30.00 Quantity 7 9 24 Discount 0.2 0.15 0.0 5 19.002 0.210523 INSERT 语句插入的行被记录下来语句插入的行被记录下来 10523219.0050.2 触发器动作被执行触发器动作被执行 OrderID 105

2、22 10523 10524 ProductID 2 41 7 UnitPrice 31.00 9.65 30.00 Quantity 7 9 24 Discount 0.2 0.15 0.0 5 19.002 0.210523 触发器代码： 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

3、.ProductID = I.ProductID UPDATE P SET UnitsInStock = (P.UnitsInStock I.Quantity) FROM Products AS P INNER JOIN Inserted AS I ON P.ProductID = I.ProductID ProductID UnitsInStock 1 2 3 4 15 10 65 20 215 在定义了在定义了 INSERT INSERT 触发器的表上触发器的表上 执行执行 INSERT INSERT 语句语句 INSERT INSERT 语句插入的行被记录下来语句插入的行被记录下来 触发

4、器动作被执行触发器动作被执行 INSERT 语句插入的行被记录下来语句插入的行被记录下来 10523219.0050.2 在定义了在定义了 DELETE 触发器的表上执行触发器的表上执行 DELETE 语句语句 4Dairy Products Cheeses0 x15 DELETE 语句删除的行被记录下来语句删除的行被记录下来 CategoryID 1 2 3 CategoryName Beverages Condiments Confections Description Soft drinks, coffees Sweet and savory Desserts, candies, Pic

5、ture 0 x15 0 x15 0 x15 0 x15CheesesDairy Products4 DELETE Categories WHERE CategoryID = 4 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 ProductID Discontinued categoryid 1 2 3 4 0 0 0 0 触发器动作被执行触发器动作被执行 2 1 4 UPDATE P SET Discontinued = 1 FROM Products AS P INNER JOIN deleted AS d ON P.CategoryID = d.CategoryID 4Dairy Products Cheeses0 x15 DELETE 语句删除的行被记录下来语句删除的行被记录下来在定义了