>CREATE TABLE [dbo].[HistoryLog] ( HistoryPK int identity Primary key, TableName [varchar](120) , [PrimaryKey] [int] , [columnname] [varchar](150) , > [oldvalue] [varchar](120), [newvalue] [varchar](120) , [DateTimeChanged] [datetime] , [username] [varchar](120) ) >>
> >CREATE TRIGGER [Purchasing].[PurchaseOrderHeader_UPDATE] ON > [Purchasing].[PurchaseOrderHeader] > AFTER UPDATE >AS >BEGIN > SET NOCOUNT ON; > > -- set Modified date on changed rows, unless the application did it already on the original update > > update Purchasing.PurchaseOrderHeader set ModifiedDate= getdate() > from inserted > join Purchasing.PurchaseOrderHeader ON Inserted.PurchaseOrderID = PurchaseOrderHeader.PurchaseOrderID > join Deleted on Inserted.PurchaseOrderID = Deleted.PurchaseOrderID > where inserted.Freight <> deleted.Freight or inserted.shipdate <> deleted.shipdate > > > -- Note...to check if something has been updated, and if the value > -- has truly changed, don't use the UPDATED statement!....It only > -- checks if the column was part of an UPDATE statement...not if it actually changed > > insert into HistoryLog -- all 1 statement > SELECT 'Orders', Inserted.PurchaseOrderID, > 'Freight', Deleted.Freight, Inserted.Freight, GETDATE(), SYSTEM_USER > FROM Inserted > JOIN Deleted ON Inserted.PurchaseOrderID = Deleted.PurchaseOrderID > WHERE Inserted.Freight <> Deleted.Freight > > > insert into HistoryLog > SELECT 'Orders', Inserted.PurchaseOrderID, > 'ShipDate', Deleted.ShipDate, Inserted.ShipDate, GETDATE(), SYSTEM_USER > FROM Inserted > JOIN Deleted ON Inserted.PurchaseOrderID= Deleted.PurchaseOrderID > WHERE Inserted.ShipDate <> Deleted.ShipDate > >END >go >