CREATE TABLE [dbo].[OrderAddress]
(
[OrderAddressID] [int] NOT NULL IDENTITY(1, 1),
[OrderFID] [int] NOT NULL,
[AddressTypeFID] [int] NOT NULL,
[Address1] [dbo].[Address] NULL,
[Address2] [dbo].[Address] NULL,
[Address3] [dbo].[Address] NULL,
[City] [dbo].[AddressCity] NULL,
[State] [dbo].[AddressState] NULL,
[PostalCode] [dbo].[AddressPostalCode] NULL,
[CountryCodeStandardFID] [int] NOT NULL,
[AddressLocationTypeFID] [int] NULL,
[SPLC] [int] NULL,
[OrderAuditInfoFID] [bigint] NULL,
[OrderAuditInfoManualCleanup] [bigint] NULL
) ON [PRIMARY]
GO

CREATE trigger [dbo].[OrderAddressAuditFieldsChanged] on [dbo].[OrderAddress]
after update
as
set nocount on
    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
        (
            OrderAddressFID int,
            FieldName varchar(128),
            Value varchar(256)
        )


        insert into @theAuditFields
        (
            OrderAddressFID,
            FieldName,
            Value
        )
        select inserted.OrderAddressID, 'CountryCodeStandardFID', CountryCodeStandard.CountryName
        from inserted
        left outer join CountryCodeStandard on inserted.CountryCodeStandardFID = CountryCodeStandard.CountryCodeStandardID
        inner join deleted on inserted.OrderAddressID = deleted.OrderAddressID and
            isnull( inserted.CountryCodeStandardFID, -1 ) != isnull( deleted.CountryCodeStandardFID, -1 )
        where update( CountryCodeStandardFID )
        union all
        select inserted.OrderAddressID, 'Address1', inserted.Address1
        from inserted
        inner join deleted on inserted.OrderAddressID = deleted.OrderAddressID and
            isnull( inserted.Address1, '' ) != isnull( deleted.Address1, '' )
        where update( Address1 )
        union all
        select inserted.OrderAddressID, 'Address2', inserted.Address2
        from inserted
        inner join deleted on inserted.OrderAddressID = deleted.OrderAddressID and
            isnull( inserted.Address2, '' ) != isnull( deleted.Address2, '' )
        where update( Address2 )
        union all
        select inserted.OrderAddressID, 'Address3', inserted.Address3
        from inserted
        inner join deleted on inserted.OrderAddressID = deleted.OrderAddressID and
            isnull( inserted.Address3, '' ) != isnull( deleted.Address3, '' )
        where update( Address3 )
        union all
        select inserted.OrderAddressID, 'City', inserted.City
        from inserted
        inner join deleted on inserted.OrderAddressID = deleted.OrderAddressID and
            isnull( inserted.City, '' ) != isnull( deleted.City, '' )
        where update( City )
        union all
        select inserted.OrderAddressID, 'State', inserted.State
        from inserted
        inner join deleted on inserted.OrderAddressID = deleted.OrderAddressID and
            isnull( inserted.State, '' ) != isnull( deleted.State, '' )
        where update( State )
        union all
        select inserted.OrderAddressID, 'PostalCode', inserted.PostalCode
        from inserted
        inner join deleted on inserted.OrderAddressID = deleted.OrderAddressID and
            isnull( inserted.PostalCode, '' ) != isnull( deleted.PostalCode, '' )
        where update( PostalCode )
        union all
        select inserted.OrderAddressID, 'AddressLocationTypeFID', AddressLocationType.Description
        from inserted
        left outer join AddressLocationType on inserted.AddressLocationTypeFID = AddressLocationType.AddressLocationTypeID
        inner join deleted on inserted.OrderAddressID = deleted.OrderAddressID and
        isnull( inserted.AddressLocationTypeFID, -1 ) != isnull( deleted.AddressLocationTypeFID, -1 )

        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 = Value,
        ChangedIn =     case
                            when OrderAuditInfo.OrderAuditInfoID is null then 'Direct SQL'
                            else OrderAuditInfo.UpdateSource
                        end
        from @theAuditFields theAuditFields
        inner join inserted on theAuditFields.OrderAddressFID = inserted.OrderAddressID
        left outer join OrderAuditInfo on inserted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID
        inner join AddressType on inserted.AddressTypeFID = AddressType.AddressTypeID
        inner join OrderHistoryField on theAuditFields.FieldName = OrderHistoryField.FieldName and
            AddressType.TypeName = OrderHistoryField.TableType

        -- 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 OrderAddress
            set OrderAuditInfoFID = NULL
            from inserted
            inner join OrderAddress on inserted.OrderAddressID = OrderAddress.OrderAddressID

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

CREATE trigger [dbo].[OrderAddressAuditFieldsInitiallyInserted] on [dbo].[OrderAddress]
after insert
as
set nocount on
    declare @theAuditFields table
    (
        OrderAddressFID int,
        FieldName varchar(128),
        Value varchar(256)
    )

    insert into @theAuditFields
    (
        OrderAddressFID,
        FieldName,
        Value
    )
    select inserted.OrderAddressID, 'CountryCodeStandardFID', CountryCodeStandard.CountryName
    from inserted
    inner join CountryCodeStandard on inserted.CountryCodeStandardFID = CountryCodeStandard.CountryCodeStandardID
    where update( CountryCodeStandardFID )
    union all
    select inserted.OrderAddressID, 'Address1', inserted.Address1
    from inserted
    where update( Address1 )
    union all
    select inserted.OrderAddressID, 'Address2', inserted.Address2
    from inserted
    where update( Address2 )
    union all
    select inserted.OrderAddressID, 'Address3', inserted.Address3
    from inserted
    where update( Address3 )
    union all
    select inserted.OrderAddressID, 'City', inserted.City
    from inserted
    where update( City )
    union all
    select inserted.OrderAddressID, 'State', inserted.State
    from inserted
    where update( State )
    union all
    select inserted.OrderAddressID, 'PostalCode', inserted.PostalCode
    from inserted
    where update( PostalCode )
    union all
    select inserted.OrderAddressID, 'AddressLocationTypeFID', AddressLocationType.Description
    from inserted
    inner join AddressLocationType on inserted.AddressLocationTypeFID = AddressLocationType.AddressLocationTypeID
    where update( AddressLocationTypeFID )
    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 = Value,
    ChangedIn =     case
                            when OrderAuditInfo.OrderAuditInfoID is null then 'Direct SQL'
                            else OrderAuditInfo.UpdateSource
                    end
    from @theAuditFields theAuditFields
    inner join inserted on theAuditFields.OrderAddressFID = inserted.OrderAddressID
    left outer join OrderAuditInfo on inserted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID
    inner join AddressType on inserted.AddressTypeFID = AddressType.AddressTypeID
    inner join OrderHistoryField on theAuditFields.FieldName = OrderHistoryField.FieldName and
        AddressType.TypeName = OrderHistoryField.TableType
    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 OrderAddress
    set OrderAuditInfoFID = NULL
    from inserted
    inner join OrderAddress on inserted.OrderAddressID = OrderAddress.OrderAddressID
    where inserted.OrderAuditInfoManualCleanup is null

    delete OrderAuditInfo
    from inserted
    inner join OrderAuditInfo on inserted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID
    where inserted.OrderAuditInfoManualCleanup is null
GO

-- Create a trigger that records audit information when an OrderAddress record is deleted.
-- Only records audit information for fields that contained data before the record was deleted.
CREATE trigger [dbo].[OrderAddressAuditRecordDeleted] on [dbo].[OrderAddress]
after delete
as
    set nocount on
    
    declare @theAuditFields table
    (
        OrderAddressFID int,
        FieldName varchar(128),
        Value varchar(256)
    )

    insert into @theAuditFields
    (
        OrderAddressFID,
        FieldName,
        Value
    )
    select deleted.OrderAddressID, 'CountryCodeStandardFID', null
    from deleted
    where isnull( deleted.CountryCodeStandardFID, -1 ) != -1
    union all
    select deleted.OrderAddressID, 'Address1', null
    from deleted
    where isnull( deleted.Address1, '' ) != ''
    union all
    select deleted.OrderAddressID, 'Address2', null
    from deleted
    where isnull( deleted.Address2, '' ) != ''
    union all
    select deleted.OrderAddressID, 'Address3', null
    from deleted
    where isnull( deleted.Address3, '' ) != ''
    union all
    select deleted.OrderAddressID, 'City', null
    from deleted
    where isnull( deleted.City, '' ) != ''
    union all
    select deleted.OrderAddressID, 'State', null
    from deleted
    where isnull( deleted.State, '' ) != ''
    union all
    select deleted.OrderAddressID, 'PostalCode', null
    from deleted
    where isnull( deleted.PostalCode, '' ) != ''
    union all
    select deleted.OrderAddressID, 'AddressLocationTypeFID', null
    from deleted
    where isnull( deleted.AddressLocationTypeFID, -1 ) != -1

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

    declare @theExtAppUser int

    if exists( select top 1 OrderAuditInfoFID from deleted 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 = deleted.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 deleted on theAuditFields.OrderAddressFID = deleted.OrderAddressID
        -- we need to filter by the existence of the parent order, in order to play nice with the OrderAddress table's "ON DELETE CASCADE"
    inner join Orders on deleted.OrderFID = Orders.PriKey
    left outer join OrderAuditInfo on deleted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID
    inner join AddressType on deleted.AddressTypeFID = AddressType.AddressTypeID
    inner join OrderHistoryField on theAuditFields.FieldName = OrderHistoryField.FieldName and  
        AddressType.TypeName = OrderHistoryField.TableType

    -- we're done with the metadata, so clean it up.
    delete OrderAuditInfo
    from deleted
    inner join OrderAuditInfo on deleted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID
GO
ALTER TABLE [dbo].[OrderAddress] ADD CONSTRAINT [CK_OrderAddress_AddressType] CHECK (([dbo].[GetAddressTypeName]([AddressTypeFID])='MovingTo' OR [dbo].[GetAddressTypeName]([AddressTypeFID])='Destination' OR [dbo].[GetAddressTypeName]([AddressTypeFID])='Origin' OR [dbo].[GetAddressTypeName]([AddressTypeFID])='Main'))
GO
ALTER TABLE [dbo].[OrderAddress] ADD CONSTRAINT [PK_OrderAddress] PRIMARY KEY NONCLUSTERED  ([OrderAddressID]) WITH (FILLFACTOR=80) ON [PRIMARY]
GO
ALTER TABLE [dbo].[OrderAddress] ADD CONSTRAINT [IX_OrderAddress_AddressType] UNIQUE NONCLUSTERED  ([OrderFID], [AddressTypeFID]) WITH (FILLFACTOR=80) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_OrderAddress_OrderAuditInfoFID] ON [dbo].[OrderAddress] ([OrderAuditInfoFID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[OrderAddress] ADD CONSTRAINT [FK_OrderAddress_AddressLocationType] FOREIGN KEY ([AddressLocationTypeFID]) REFERENCES [dbo].[AddressLocationType] ([AddressLocationTypeID])
GO
ALTER TABLE [dbo].[OrderAddress] ADD CONSTRAINT [FK_OrderAddress_AddressType] FOREIGN KEY ([AddressTypeFID]) REFERENCES [dbo].[AddressType] ([AddressTypeID])
GO
ALTER TABLE [dbo].[OrderAddress] ADD CONSTRAINT [FK_OrderAddress_CountryCodeStandard] FOREIGN KEY ([CountryCodeStandardFID]) REFERENCES [dbo].[CountryCodeStandard] ([CountryCodeStandardID])
GO
ALTER TABLE [dbo].[OrderAddress] ADD CONSTRAINT [FK_OrderAddress_OrderAuditInfo] FOREIGN KEY ([OrderAuditInfoFID]) REFERENCES [dbo].[OrderAuditInfo] ([OrderAuditInfoID])
GO
ALTER TABLE [dbo].[OrderAddress] ADD CONSTRAINT [FK_OrderAddress_Order] FOREIGN KEY ([OrderFID]) REFERENCES [dbo].[Orders] ([PriKey]) ON DELETE CASCADE
GO
GRANT SELECT ON  [dbo].[OrderAddress] TO [MssExec]
GRANT INSERT ON  [dbo].[OrderAddress] TO [MssExec]
GRANT DELETE ON  [dbo].[OrderAddress] TO [MssExec]
GRANT UPDATE ON  [dbo].[OrderAddress] TO [MssExec]
GO