CREATE TABLE [dbo].[OrdersExtended]
(
[OrdersExtendedID] [int] NOT NULL IDENTITY(1, 1),
[OrderFID] [int] NOT NULL,
[CommodityDetailFID] [int] NULL,
[HaulModeFID] [int] NULL,
[WillAdvise] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CodCollectionAmount] [money] NULL,
[CurrentDriver] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OrderAuditInfoFID] [bigint] NULL,
[OrderAuditInfoManualCleanup] [bigint] NULL
) ON [PRIMARY]
GO

create trigger [dbo].[OrdersExtendedAuditFieldsChanged] on [dbo].[OrdersExtended]
after update
as
    set nocount on

    -- When we clear the OrderAuditInfoFID field, no auditing is needed.
    if( update( OrderAuditInfoFID ) and not exists ( select OrderAuditInfoFID from inserted where OrderAuditInfoFID is not null ) )
    begin
        return
    end
    else
    begin
        declare @theAuditFields table
        (
            OrderFID int,
            FieldName varchar(128),
            Value varchar(256)
        )

        insert into @theAuditFields
        (
            OrderFID,
            FieldName,
            [Value]
        )
            select inserted.OrderFID, 'CommodityDetailFID', CommodityDetail.[Description]
            from inserted
            inner join deleted on inserted.OrdersExtendedID = deleted.OrdersExtendedID and
                isnull( inserted.CommodityDetailFID, -1 ) != isnull( deleted.CommodityDetailFID, -1 )
            left outer join CommodityDetail on inserted.CommodityDetailFID = CommodityDetail.CommodityDetailID
            where update( CommodityDetailFID )
        union all
            select inserted.OrderFID, 'HaulModeFID', HaulMode.[Description]
            from inserted
            inner join deleted on inserted.OrdersExtendedID = deleted.OrdersExtendedID and
                isnull( inserted.HaulModeFID, -1 ) != isnull( deleted.HaulModeFID, -1 )
            left outer join HaulMode on inserted.HaulModeFID = HaulMode.HaulModeID
            where update( HaulModeFID )
        union all
            select inserted.OrderFID, 'WillAdvise', inserted.WillAdvise
            from inserted
            inner join deleted on inserted.OrdersExtendedID = deleted.OrdersExtendedID and
                isnull( inserted.WillAdvise, '' ) != isnull( deleted.WillAdvise, '' )
            where update( WillAdvise )
        union all
            select inserted.OrderFID, 'CodCollectionAmount', convert(varchar, inserted.CodCollectionAmount )
            from inserted
            inner join deleted on inserted.OrdersExtendedID = deleted.OrdersExtendedID and
                isnull( inserted.CodCollectionAmount, -99999 ) != isnull( deleted.CodCollectionAmount, -99999 )
            where update( CodCollectionAmount )
        union all
            select inserted.OrderFID, 'CurrentDriver', inserted.CurrentDriver
            from inserted
            inner join deleted on inserted.OrdersExtendedID = deleted.OrdersExtendedID and
                isnull( inserted.CurrentDriver, '' ) != isnull( deleted.CurrentDriver, '' )
            where update( CurrentDriver )

        declare @theChangedOn datetime
        set @theChangedOn = dbo.GetMssDateTime()

        declare @theExtAppUser int

        if( exists( select top 1 OrderAuditInfoFID from inserted where OrderAuditInfoFID is null ) )
        begin
            select @theExtAppUser = SysUser.SysUserID
            from dbo.Sysuser
            where SysUser.FIRSTNAME = 'External' and
            SysUser.LASTNAME = 'Application'
        end

        insert into OrderHistory
        (
            OrderFID,
            ChangedBy,
            ChangedOn,
            OrderHistoryFieldFID,
            ChangedTo,
            ChangedIn
        )
        select
            OrderFID = theAuditFields.OrderFID,
            ChangedBy = isnull( OrderAuditInfo.SysUserFID, @theExtAppUser ),
            ChangedOn = @theChangedOn,
            OrderHistoryFieldFID = OrderHistoryfield.OrderHistoryFieldID,
            ChangedTo = Value,
            ChangedIn = case
                when OrderAuditInfo.OrderAuditInfoID is null then 'Direct SQL'
                else OrderAuditInfo.UpdateSource
            end
        from @theAuditFields theAuditFields
        inner join OrderHistoryField on theAuditFields.FieldName = OrderHistoryField.FieldName
        inner join inserted on theAuditFields.OrderFID = inserted.OrderFID
        left outer join OrderAuditInfo on inserted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID

        -- we're done with the metadata, so clean it up. (unless a manual cleanup flag was specified, in
        -- which case its the responsibility of the updater to clean up this record ).
        if( not( update( OrderAuditInfoManualCleanup ) ) )
        begin
            update OrdersExtended set
                OrderAuditInfoFID = NULL
            from inserted
            inner join OrdersExtended on inserted.OrdersExtendedID = OrdersExtended.OrdersExtendedID

            delete OrderAuditInfo
            from inserted
            inner join OrderAuditInfo on inserted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID
        end
    end -- if update( OrderAuditInfoFID )
GO

create trigger [dbo].[OrdersExtendedAuditFieldsInitiallySet] on [dbo].[OrdersExtended]
after insert
as
    set nocount on

    declare @theAuditFields table
    (
        OrderID int,
        FieldName varchar(128),
        Value varchar(256)
    )
    insert into @theAuditFields
    (
        OrderID,
        FieldName,
        Value
    )
        select inserted.OrderFID, 'CurrentDriver', inserted.CurrentDriver
        from inserted
    union all
        select inserted.OrderFID, 'CommodityDetailFID', CommodityDetail.[Description]
        from inserted
        inner join CommodityDetail on inserted.CommodityDetailFID = CommodityDetail.CommodityDetailID
    union all
        select inserted.OrderFID, 'HaulModeFID', HaulMode.[Description]
        from inserted
        inner join HaulMode on inserted.HaulModeFID = HaulMode.HaulModeID
    union all
        select inserted.OrderFID, 'WillAdvise', inserted.WillAdvise
        from inserted
    union all
        select inserted.OrderFID, 'CodCollectionAmount', convert(varchar, inserted.CodCollectionAmount )
        from inserted

    declare @theChangedOn datetime
    set @theChangedOn = dbo.GetMssDateTime()

    declare @theExtAppUser int

    if( exists( select top 1 OrderAuditInfoFID from inserted where OrderAuditInfoFID is null ) )
    begin
        select @theExtAppUser = SysUser.SysUserID
        from dbo.Sysuser
        where SysUser.FIRSTNAME = 'External' and
        SysUser.LASTNAME = 'Application'
    end

    insert into OrderHistory
    (
        OrderFID,
        ChangedBy,
        ChangedOn,
        OrderHistoryFieldFID,
        ChangedTo,
        ChangedIn
    )
    select
        OrderFID = inserted.OrderFID,
        ChangedBy = isnull( OrderAuditInfo.SysUserFID, @theExtAppUser ),
        ChangedOn = @theChangedOn,
        OrderHistoryFieldFID = OrderHistoryfield.OrderHistoryFieldID,
        ChangedTo = theAuditFields.[Value],
        ChangedIn = case
            when OrderAuditInfo.OrderAuditInfoID is null then 'Direct SQL'
            else OrderAuditInfo.UpdateSource
        end
    from @theAuditFields theAuditFields
    inner join OrderHistoryField on theAuditFields.FieldName = OrderHistoryField.FieldName
    inner join inserted on theAuditFields.OrderID = inserted.OrderFID
    left outer join OrderAuditInfo on inserted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID
    where theAuditFields.Value is not null

    -- we're done with the metadata, so clean it up. (unless a manual cleanup flag was specified, in
    -- which case its the responsibility of the inserter to clean up this record ).
    update OrdersExtended set
        OrderAuditInfoFID = NULL
    from inserted
    inner join OrdersExtended on inserted.OrdersExtendedID = OrdersExtended.OrdersExtendedID
    where inserted.OrderAuditInfoManualCleanup is null

    delete OrderAuditInfo
    from inserted
    inner join OrderAuditInfo on inserted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID
    where inserted.OrderAuditInfoManualCleanup is null
GO
ALTER TABLE [dbo].[OrdersExtended] ADD CONSTRAINT [PK_OrdersExtended] PRIMARY KEY CLUSTERED  ([OrdersExtendedID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[OrdersExtended] ADD CONSTRAINT [IX_OrdersExtended_Order] UNIQUE NONCLUSTERED  ([OrderFID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_OrdersExtended_OrderAuditInfoFID] ON [dbo].[OrdersExtended] ([OrderAuditInfoFID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[OrdersExtended] ADD CONSTRAINT [FK_OrdersExtended_CommodityDetail] FOREIGN KEY ([CommodityDetailFID]) REFERENCES [dbo].[CommodityDetail] ([CommodityDetailID])
GO
ALTER TABLE [dbo].[OrdersExtended] ADD CONSTRAINT [FK_OrdersExtended_HaulMode] FOREIGN KEY ([HaulModeFID]) REFERENCES [dbo].[HaulMode] ([HaulModeID])
GO
ALTER TABLE [dbo].[OrdersExtended] ADD CONSTRAINT [FK_OrdersExtended_OrderAuditInfo] FOREIGN KEY ([OrderAuditInfoFID]) REFERENCES [dbo].[OrderAuditInfo] ([OrderAuditInfoID])
GO
ALTER TABLE [dbo].[OrdersExtended] ADD CONSTRAINT [FK_OrdersExtended_Order] FOREIGN KEY ([OrderFID]) REFERENCES [dbo].[Orders] ([PriKey])
GO
GRANT SELECT ON  [dbo].[OrdersExtended] TO [MssExec]
GRANT INSERT ON  [dbo].[OrdersExtended] TO [MssExec]
GRANT DELETE ON  [dbo].[OrdersExtended] TO [MssExec]
GRANT UPDATE ON  [dbo].[OrdersExtended] TO [MssExec]
GO