CREATE TABLE [dbo].[SITJointInfo]
(
[SITJointInfoID] [int] NOT NULL IDENTITY(1, 1),
[OrdersFID] [int] NOT NULL,
[SITDiscount] [decimal] (10, 4) NULL,
[OrderAuditInfoFID] [bigint] NULL,
[OrderAuditInfoManualCleanup] [bigint] NULL
) ON [PRIMARY]
GO

-- Create a trigger that records audit information when a SITJointInfo record is deleted.
-- Only records audit information for fields that contained data before the record was deleted.
create trigger [dbo].[SITJointInfoDeletedAudit] on [dbo].[SITJointInfo] after delete
as
    set nocount on

    declare @theAuditFields table
    (
        SITJointInfoID int,
        FieldName varchar(128),
        [Value] varchar(256)
    )

    insert into @theAuditFields
    (
        SITJointInfoID,
        FieldName,
        [Value]
    )
    select deleted.SITJointInfoID, 'SITDiscount', null
    from deleted
    where isnull( deleted.SITDiscount, 0.0 ) <> 0.0


    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.OrdersFID,
        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 deleted on theAuditFields.SITJointInfoID = deleted.SITJointInfoID
    left outer join OrderAuditInfo on deleted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID
    inner join OrderHistoryField on theAuditFields.FieldName = OrderHistoryField.FieldName

    -- we're done with the metadata, so clean it up.
    delete OrderAuditInfo
    from deleted
    inner join OrderAuditInfo on deleted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID
GO

-- Create a trigger that records audit information when a SITJointInfo record is inserted.
-- Only records audit information for fields that have a value.
create trigger [dbo].[SITJointInfoInsertedAudit] on [dbo].[SITJointInfo] after insert
as
set nocount on
    declare @theAuditFields table
    (
        SITJointInfoID int,
        FieldName varchar(128),
        [Value] varchar(256)
    )

    insert into @theAuditFields
    (
        SITJointInfoID,
        FieldName,
        [Value]
    )

    select inserted.SITJointInfoID, 'SITDiscount', convert(varchar,inserted.SITDiscount)
    from inserted
    where update( SITDiscount )


    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.OrdersFID,
    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 inserted on theAuditFields.SITJointInfoID = inserted.SITJointInfoID
    left outer join OrderAuditInfo on inserted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID
    inner join OrderHistoryField on theAuditFields.FieldName = OrderHistoryField.FieldName
    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 SITJointInfo
    set OrderAuditInfoFID = NULL
    from inserted
    inner join SITJointInfo on inserted.SITJointInfoID = SITJointInfo.SITJointInfoID
    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 a SITJointInfo record is updated.
-- Only records audit information for fields that were updated.
create trigger [dbo].[SITJointInfoUpdatedAudit] on [dbo].[SITJointInfo] 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
        delete OrderAuditInfo
        from deleted
        inner join OrderAuditInfo on deleted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID
        return  
    end
    else
    begin
        declare @theAuditFields table
        (
            SITJointInfoID int,
            FieldName varchar(128),
            [Value] varchar(256)
        )

        declare @theMinDate datetime
        SELECT @theMinDate = '1900-01-01'
        insert into @theAuditFields
        (
            SITJointInfoID,
            FieldName,
            [Value]
        )

        select inserted.SITJointInfoID, 'SITDiscount', convert(varchar,inserted.SITDiscount)
        from inserted
        inner join deleted on inserted.SITJointInfoID = deleted.SITJointInfoID and
            isnull( inserted.SITDiscount, 0.0 ) <> isnull( deleted.SITDiscount, 0.0 )
        where update( SITDiscount )
        

        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.OrdersFID,
        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 inserted on theAuditFields.SITJointInfoID = inserted.SITJointInfoID
        left outer join OrderAuditInfo on inserted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID
        inner join OrderHistoryField on theAuditFields.FieldName = OrderHistoryField.FieldName

        -- 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 dbo.SITJointInfo
            set OrderAuditInfoFID = NULL
            from inserted
            inner join dbo.SITJointInfo on inserted.SITJointInfoID = SITJointInfo.SITJointInfoID

            delete OrderAuditInfo
            from inserted
            inner join OrderAuditInfo on inserted.OrderAuditInfoFID = OrderAuditInfo.OrderAuditInfoID
        end
    end -- if update( OrderAuditInfoFID )
GO
ALTER TABLE [dbo].[SITJointInfo] ADD CONSTRAINT [PK_SITJointInfo] PRIMARY KEY NONCLUSTERED  ([SITJointInfoID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SITJointInfo] ADD CONSTRAINT [IX_SITJointInfo] UNIQUE NONCLUSTERED  ([OrdersFID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_SITJointInfo_OrderAuditInfoFID] ON [dbo].[SITJointInfo] ([OrderAuditInfoFID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SITJointInfo] ADD CONSTRAINT [FK_SITJointInfo_OrderAuditInfo] FOREIGN KEY ([OrderAuditInfoFID]) REFERENCES [dbo].[OrderAuditInfo] ([OrderAuditInfoID])
GO
ALTER TABLE [dbo].[SITJointInfo] ADD CONSTRAINT [FK_SITJointInfo_Orders] FOREIGN KEY ([OrdersFID]) REFERENCES [dbo].[Orders] ([PriKey])
GO
GRANT SELECT ON  [dbo].[SITJointInfo] TO [MssExec]
GRANT INSERT ON  [dbo].[SITJointInfo] TO [MssExec]
GRANT DELETE ON  [dbo].[SITJointInfo] TO [MssExec]
GRANT UPDATE ON  [dbo].[SITJointInfo] TO [MssExec]
GO