CREATE TABLE [dbo].[Container]
(
[ContainerID] [int] NOT NULL IDENTITY(1, 1),
[ContainerNumber] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[BranchFID] [int] NOT NULL,
[OrderFID] [int] NULL,
[ContainerTypeFID] [int] NOT NULL,
[Length] [decimal] (10, 2) NULL,
[Width] [decimal] (10, 2) NULL,
[Height] [decimal] (10, 2) NULL,
[TareWeight] [int] NULL,
[ContainerStatusFID] [int] NOT NULL,
[Active] [bit] NOT NULL CONSTRAINT [DF_Container_Active] DEFAULT ((1)),
[OwnershipFID] [int] NULL,
[LastChangedByFID] [int] NOT NULL,
[CurrentLocation] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Cost] [decimal] (15, 2) NULL,
[Note] [varchar] (1024) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

/**
*    This "for insert" trigger inserts a Container History record telling us that the container was created.
*/
create trigger [dbo].[ContainerInsertTrigger] on [dbo].[Container] for insert
as
if 0 < ( select count(*) from inserted )
begin
    insert into ContainerHistory
    (
        ContainerFID,
        ActivityDescription,
        SysuserFID,
        DateEntered
    )
    select
        ContainerFID = ContainerID,
        ActivityDescription = 'Created',
        SysuserFID = LastChangedByFID,
        DateEntered = getdate()
    from inserted
end
GO

/**
*    This "for update" trigger logs changes to the Container table.
*/
CREATE trigger [dbo].[ContainerUpdateTrigger] on [dbo].[Container] for update
as
    if update( ContainerNumber ) and exists ( select 1 from inserted inner join deleted on inserted.ContainerID = deleted.ContainerID where inserted.ContainerNumber != deleted.ContainerNumber )
    begin
        insert into ContainerHistory
        (
            ContainerFID,
            ActivityDescription,
            OldValue,
            NewValue,
            SysuserFID,
            DateEntered
        )
        select
            ContainerFID = inserted.ContainerID,
            ActivityDescription = 'Container',
            OldValue = deleted.ContainerNumber,
            NewValue = inserted.ContainerNumber,
            SysuserFID = inserted.LastChangedByFID,
            DateEntered = getdate()
        from inserted
        inner join deleted on inserted.ContainerID = deleted.ContainerID     
    end  
    if update( BranchFID ) and exists ( select 1 from inserted inner join deleted on inserted.ContainerID = deleted.ContainerID where inserted.BranchFID != deleted.BranchFID )
    begin
        insert into ContainerHistory
        (
            ContainerFID,
            ActivityDescription,
            OldValue,
            NewValue,
            SysuserFID,
            DateEntered
        )
        select
            ContainerFID = inserted.ContainerID,
            ActivityDescription = 'Branch',
            OldValue = OldBranch.BranchID,
            NewValue = NewBranch.BranchID,
            SysuserFID = inserted.LastChangedByFID,
            DateEntered = getdate()
        from inserted
        inner join deleted on inserted.ContainerID = deleted.ContainerID
        inner join Branch OldBranch on deleted.BranchFID = OldBranch.BranchPriKey
        inner join Branch NewBranch on inserted.BranchFID = NewBranch.BranchPriKey     
    end  
    if update( OrderFID ) and exists ( select 1 from inserted inner join deleted on inserted.ContainerID = deleted.ContainerID where inserted.OrderFID is not null and deleted.OrderFID is null )
    begin
        insert into ContainerHistory
        (
            ContainerFID,
            ActivityDescription,
            OldValue,
            NewValue,
            SysuserFID,
            DateEntered,
            OrderFID
        )
        select
            ContainerFID = inserted.ContainerID,
            ActivityDescription = 'Assigned',
            OldValue = 'Not set',
            NewValue = Orders.OrderNo,
            SysuserFID = inserted.LastChangedByFID,
            DateEntered = getdate(),
            OrderFID = inserted.OrderFID
        from inserted
        inner join deleted on inserted.ContainerID = deleted.ContainerID
        inner join Orders on inserted.OrderFID = Orders.PriKey
    end  
    if update( OrderFID ) and exists ( select 1 from inserted inner join deleted on inserted.ContainerID = deleted.ContainerID where inserted.OrderFID is null and deleted.OrderFID is not null )
    begin
        insert into ContainerHistory
        (
            ContainerFID,
            ActivityDescription,
            OldValue,
            NewValue,
            SysuserFID,
            DateEntered
        )
        select
            ContainerFID = inserted.ContainerID,
            ActivityDescription = 'Released',
            OldValue = Orders.OrderNo,
            NewValue = 'Not set',
            SysuserFID = inserted.LastChangedByFID,
            DateEntered = getdate()
        from inserted
        inner join deleted on inserted.ContainerID = deleted.ContainerID
        inner join Orders on deleted.OrderFID = Orders.PriKey
    end  
    if update( ContainerTypeFID ) and exists ( select 1 from inserted inner join deleted on inserted.ContainerID = deleted.ContainerID where inserted.ContainerTypeFID != deleted.ContainerTypeFID )
    begin
        insert into ContainerHistory
        (
            ContainerFID,
            ActivityDescription,
            OldValue,
            NewValue,
            SysuserFID,
            DateEntered
        )
        select
            ContainerFID = inserted.ContainerID,
            ActivityDescription = 'Type',
            OldValue = OldContainerType.description,
            NewValue = NewContainerType.description,
            SysuserFID = inserted.LastChangedByFID,
            DateEntered = getdate()
        from inserted
        inner join deleted on inserted.ContainerID = deleted.ContainerID
        inner join ContainerType OldContainerType on deleted.ContainerTypeFID = OldContainerType.ContainerTypeID
        inner join ContainerType NewContainerType on inserted.ContainerTypeFID = NewContainerType.ContainerTypeID
    end  
    if update( Length ) and exists ( select 1 from inserted inner join deleted on inserted.ContainerID = deleted.ContainerID where inserted.Length != deleted.Length )
    begin
        insert into ContainerHistory
        (
            ContainerFID,
            ActivityDescription,
            OldValue,
            NewValue,
            SysuserFID,
            DateEntered
        )
        select
            ContainerFID = inserted.ContainerID,
            ActivityDescription = 'Length',
            OldValue = isnull( deleted.Length, 'Not set' ),
            NewValue = isnull( inserted.Length, 'Not set' ),
            SysuserFID = inserted.LastChangedByFID,
            DateEntered = getdate()
        from inserted
        inner join deleted on inserted.ContainerID = deleted.ContainerID
    end  
    if update( Width ) and exists ( select 1 from inserted inner join deleted on inserted.ContainerID = deleted.ContainerID where inserted.Width != deleted.Width )
    begin
        insert into ContainerHistory
        (
            ContainerFID,
            ActivityDescription,
            OldValue,
            NewValue,
            SysuserFID,
            DateEntered
        )
        select
            ContainerFID = inserted.ContainerID,
            ActivityDescription = 'Width',
            OldValue = isnull( deleted.Width, 'Not set' ),
            NewValue = isnull( inserted.Width, 'Not set' ),
            SysuserFID = inserted.LastChangedByFID,
            DateEntered = getdate()
        from inserted
        inner join deleted on inserted.ContainerID = deleted.ContainerID
    end  
    if update( Height ) and exists ( select 1 from inserted inner join deleted on inserted.ContainerID = deleted.ContainerID where inserted.Height != deleted.Height )
    begin
        insert into ContainerHistory
        (
            ContainerFID,
            ActivityDescription,
            OldValue,
            NewValue,
            SysuserFID,
            DateEntered
        )
        select
            ContainerFID = inserted.ContainerID,
            ActivityDescription = 'Height',
            OldValue = isnull( deleted.Height, 'Not set' ),
            NewValue = isnull( inserted.Height, 'Not set' ),
            SysuserFID = inserted.LastChangedByFID,
            DateEntered = getdate()
        from inserted
        inner join deleted on inserted.ContainerID = deleted.ContainerID
    end  
    if update( TareWeight ) and exists ( select 1 from inserted inner join deleted on inserted.ContainerID = deleted.ContainerID where inserted.TareWeight != deleted.TareWeight )
    begin
        insert into ContainerHistory
        (
            ContainerFID,
            ActivityDescription,
            OldValue,
            NewValue,
            SysuserFID,
            DateEntered
        )
        select
            ContainerFID = inserted.ContainerID,
            ActivityDescription = 'Tare Weight',
            OldValue = isnull( deleted.TareWeight, 'Not set' ),
            NewValue = isnull( inserted.TareWeight, 'Not set' ),
            SysuserFID = inserted.LastChangedByFID,
            DateEntered = getdate()
        from inserted
        inner join deleted on inserted.ContainerID = deleted.ContainerID
    end  
    if update( ContainerStatusFID ) and exists ( select 1 from inserted inner join deleted on inserted.ContainerID = deleted.ContainerID where inserted.ContainerStatusFID != deleted.ContainerStatusFID )
    begin
        insert into ContainerHistory
        (
            ContainerFID,
            ActivityDescription,
            OldValue,
            NewValue,
            SysuserFID,
            DateEntered
        )
        select
            ContainerFID = inserted.ContainerID,
            ActivityDescription = 'Status',
            OldValue = OldContainerStatus.Description,
            NewValue = NewContainerStatus.Description,
            SysuserFID = inserted.LastChangedByFID,
            DateEntered = getdate()
        from inserted
        inner join deleted on inserted.ContainerID = deleted.ContainerID
        inner join ContainerStatus OldContainerStatus on deleted.ContainerStatusFID = OldContainerStatus.ContainerStatusID
        inner join ContainerStatus NewContainerStatus on inserted.ContainerStatusFID = NewContainerStatus.ContainerStatusID
    end  
    if update( Active ) and exists ( select 1 from inserted inner join deleted on inserted.ContainerID = deleted.ContainerID where inserted.Active != deleted.Active )
    begin
        insert into ContainerHistory
        (
            ContainerFID,
            ActivityDescription,
            OldValue,
            NewValue,
            SysuserFID,
            DateEntered
        )
        select
            ContainerFID = inserted.ContainerID,
            ActivityDescription = 'Active',
            OldValue = case deleted.active when 1 then 'Active' else 'Inactive' end,
            NewValue = case inserted.Active when 1 then 'Active' else 'Inactive' end,
            SysuserFID = inserted.LastChangedByFID,
            DateEntered = getdate()
        from inserted
        inner join deleted on inserted.ContainerID = deleted.ContainerID
    end  
    if update( OwnershipFID ) and exists ( select 1 from inserted inner join deleted on inserted.ContainerID = deleted.ContainerID where isnull( inserted.OwnershipFID, 0 ) != isnull( deleted.OwnershipFID, 0 ) )
    begin
        insert into ContainerHistory
        (
            ContainerFID,
            ActivityDescription,
            OldValue,
            NewValue,
            SysuserFID,
            DateEntered
        )
        select
            ContainerFID = inserted.ContainerID,
            ActivityDescription = 'Ownership',
            OldValue = isnull( OldOwnership.description, 'Not set' ),
            NewValue = isnull( NewOwnership.Description, 'Not set' ),
            SysuserFID = inserted.LastChangedByFID,
            DateEntered = getdate()
        from inserted
        inner join deleted on inserted.ContainerID = deleted.ContainerID
        left outer join Ownership OldOwnership on deleted.OwnershipFID = OldOwnership.OwnershipID
        left outer join Ownership NewOwnership on inserted.OwnershipFID = NewOwnership.OwnershipID
    end  
    if update( CurrentLocation ) and exists ( select 1 from inserted inner join deleted on inserted.ContainerID = deleted.ContainerID where isnull( inserted.CurrentLocation, '' ) != isnull( deleted.CurrentLocation, '' ) )
    begin
        insert into ContainerHistory
        (
            ContainerFID,
            ActivityDescription,
            OldValue,
            NewValue,
            SysuserFID,
            DateEntered
        )
        select
            ContainerFID = inserted.ContainerID,
            ActivityDescription = 'Location',
            OldValue = isnull( deleted.CurrentLocation, 'Not set' ),
            NewValue = isnull( inserted.CurrentLocation, 'Not set' ),
            SysuserFID = inserted.LastChangedByFID,
            DateEntered = getdate()
        from inserted
        inner join deleted on inserted.ContainerID = deleted.ContainerID
    end  
    if update( Cost ) and exists ( select 1 from inserted inner join deleted on inserted.ContainerID = deleted.ContainerID where inserted.Cost !=  deleted.Cost )
    begin
        insert into ContainerHistory
        (
            ContainerFID,
            ActivityDescription,
            OldValue,
            NewValue,
            SysuserFID,
            DateEntered
        )
        select
            ContainerFID = inserted.ContainerID,
            ActivityDescription = 'Cost',
            OldValue = isnull( deleted.Cost, 'Not set' ),
            NewValue = isnull( inserted.Cost, 'Not set' ),
            SysuserFID = inserted.LastChangedByFID,
            DateEntered = getdate()
        from inserted
        inner join deleted on inserted.ContainerID = deleted.ContainerID
    end
    if update( Note ) and exists ( select 1 from inserted inner join deleted on inserted.ContainerID = deleted.ContainerID where isnull( inserted.Note, '' ) != isnull( deleted.Note, '' ) )
    begin
        insert into ContainerHistory
        (
            ContainerFID,
            ActivityDescription,
            OldValue,
            NewValue,
            SysuserFID,
            DateEntered
        )
        select
            ContainerFID = inserted.ContainerID,
            ActivityDescription = 'Note',
            OldValue = isnull( deleted.Note, 'Not set' ),
            NewValue = isnull( inserted.Note, 'Not set' ),
            SysuserFID = inserted.LastChangedByFID,
            DateEntered = getdate()
        from inserted
        inner join deleted on inserted.ContainerID = deleted.ContainerID
    end
GO
ALTER TABLE [dbo].[Container] ADD CONSTRAINT [PK_Container] PRIMARY KEY NONCLUSTERED  ([ContainerID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Container_ContainerNumber] ON [dbo].[Container] ([ContainerNumber]) INCLUDE ([OrderFID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ix_Container_ContainerStatus] ON [dbo].[Container] ([ContainerStatusFID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Container_OrderFID] ON [dbo].[Container] ([OrderFID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Container] ADD CONSTRAINT [FK_Container_Branch] FOREIGN KEY ([BranchFID]) REFERENCES [dbo].[Branch] ([BranchPriKey])
GO
ALTER TABLE [dbo].[Container] ADD CONSTRAINT [FK_Container_ContainerStatus] FOREIGN KEY ([ContainerStatusFID]) REFERENCES [dbo].[ContainerStatus] ([ContainerStatusID])
GO
ALTER TABLE [dbo].[Container] ADD CONSTRAINT [FK_Container_ContainerType] FOREIGN KEY ([ContainerTypeFID]) REFERENCES [dbo].[ContainerType] ([ContainerTypeID])
GO
ALTER TABLE [dbo].[Container] ADD CONSTRAINT [FK_Container_LastChangedBy] FOREIGN KEY ([LastChangedByFID]) REFERENCES [dbo].[Sysuser] ([SysUserID])
GO
ALTER TABLE [dbo].[Container] ADD CONSTRAINT [FK_Container_Order] FOREIGN KEY ([OrderFID]) REFERENCES [dbo].[Orders] ([PriKey])
GO
ALTER TABLE [dbo].[Container] ADD CONSTRAINT [FK_Container_Ownership] FOREIGN KEY ([OwnershipFID]) REFERENCES [dbo].[Ownership] ([OwnershipID])
GO
GRANT SELECT ON  [dbo].[Container] TO [MssExec]
GRANT INSERT ON  [dbo].[Container] TO [MssExec]
GRANT DELETE ON  [dbo].[Container] TO [MssExec]
GRANT UPDATE ON  [dbo].[Container] TO [MssExec]
GO