Triggering Actions Automatically when Updating Data

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 


Solid Quality Learning, Microsoft Corporation Staff - Microsoft SQL Server 2005 Database Essentials Step by Step
Solid Quality Learning, Microsoft Corporation Staff - Microsoft SQL Server 2005 Database Essentials Step by Step
ISBN: N/A
EAN: N/A
Year: 2006
Pages: 130

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net