| ||
The previous chapter discussed the occasional need to control what happens when any process or application inserts or deletes a row. The main difference when updating data is the virtual tables that are used. When updating rows, there are two tables that contain the changes: the table named deleted and the table named inserted.
Using the same Production.ProductHistory table that was used in Chapter 11, it is possible to keep a log detailing the updates in the Production table. This code is included in the sample files as \Ch12\AdvWorks\Queries\Sample07.sql.
CREATE TRIGGER Production.Product_UpdateTrigger ON Production.Product AFTER UPDATE AS BEGIN SET NOCOUNT ON; INSERT INTO [AdventureWorks].[Production].[ProductHistory] ([ProductID],[Name],[ProductNumber],[MakeFlag] ,[FinishedGoodsFlag],[Color],[SafetyStockLevel] ,[ReorderPoint],[StandardCost],[ListPrice] ,[Size],[SizeUnitMeasureCode],[WeightUnitMeasureCode] ,[Weight],[DaysToManufacture],[ProductLine] ,[Class],[Style],[ProductSubcategoryID] ,[ProductModelID],[SellStartDate],[SellEndDate] ,[DiscontinuedDate],[rowguid],[ModifiedDate] ,[Action],[UserName]) SELECT [ProductID],[Name],[ProductNumber],[MakeFlag] ,[FinishedGoodsFlag],[Color],[SafetyStockLevel] ,[ReorderPoint],[StandardCost],[ListPrice] ,[Size],[SizeUnitMeasureCode],[WeightUnitMeasureCode] ,[Weight],[DaysToManufacture],[ProductLine] ,[Class],[Style],[ProductSubcategoryID] ,[ProductModelID],[SellStartDate],[SellEndDate] ,[DiscontinuedDate],[rowguid],GetDate(),'M',USER_NAME() FROM inserted END
If you want to track both the old and new versions, you can insert two rows in the History table, with one containing the old version from the table named deleted and the other containing the new version from the table named inserted. This code is included in the sample files as \Ch12\AdvWorks\Queries\Sample08.sql.
CREATE TRIGGER Production.Product_UpdateTrigger ON Production.Product AFTER UPDATE AS BEGIN SET NOCOUNT ON; DECLARE @Stamp datetime; SET @Stamp=getdate(); -- Add the old row to the History table INSERT INTO [AdventureWorks].[Production].[ProductHistory] ([ProductID],[Name],[ProductNumber],[MakeFlag] ,[FinishedGoodsFlag],[Color],[SafetyStockLevel] ,[ReorderPoint],[StandardCost],[ListPrice] ,[Size],[SizeUnitMeasureCode],[WeightUnitMeasureCode] ,[Weight],[DaysToManufacture],[ProductLine] ,[Class],[Style],[ProductSubcategoryID] ,[ProductModelID],[SellStartDate],[SellEndDate] ,[DiscontinuedDate],[rowguid],[ModifiedDate] ,[Action],[UserName]) SELECT [ProductID],[Name],[ProductNumber],[MakeFlag] ,[FinishedGoodsFlag],[Color],[SafetyStockLevel] ,[ReorderPoint],[StandardCost],[ListPrice] ,[Size],[SizeUnitMeasureCode],[WeightUnitMeasureCode] ,[Weight],[DaysToManufacture],[ProductLine] ,[Class],[Style],[ProductSubcategoryID] ,[ProductModelID],[SellStartDate],[SellEndDate] ,[DiscontinuedDate],[rowguid],@Stamp,'O',USER_NAME() FROM deleted -- Add the new version to the History table INSERT INTO [AdventureWorks].[Production].[ProductHistory] ([ProductID],[Name],[ProductNumber],[MakeFlag] ,[FinishedGoodsFlag],[Color],[SafetyStockLevel] ,[ReorderPoint],[StandardCost],[ListPrice] ,[Size],[SizeUnitMeasureCode],[WeightUnitMeasureCode] ,[Weight],[DaysToManufacture],[ProductLine] ,[Class],[Style],[ProductSubcategoryID] ,[ProductModelID],[SellStartDate],[SellEndDate] ,[DiscontinuedDate],[rowguid],[ModifiedDate] ,[Action],[UserName]) SELECT [ProductID],[Name],[ProductNumber],[MakeFlag] ,[FinishedGoodsFlag],[Color],[SafetyStockLevel] ,[ReorderPoint],[StandardCost],[ListPrice] ,[Size],[SizeUnitMeasureCode],[WeightUnitMeasureCode] ,[Weight],[DaysToManufacture],[ProductLine] ,[Class],[Style],[ProductSubcategoryID] ,[ProductModelID],[SellStartDate],[SellEndDate] ,[DiscontinuedDate],[rowguid],@Stamp,'N',USER_NAME() FROM inserted END
| ||