CREATE TABLE [dbo].[MSSDocumentImage]
(
[MSSDocumentImageID] [int] NOT NULL IDENTITY(1, 1),
[OrderFID] [int] NOT NULL,
[DocumentName] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DocumentImage] [image] NULL,
[ExtImageFID] [int] NULL,
[MSSDocumentImageDBFID] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

-- Deletes any documents that are stored in external document dbs that are linked to MSSDocumentImage records getting deleted
CREATE trigger [dbo].[DocumentImageDeleted] on [dbo].[MSSDocumentImage]
after delete as
set nocount on

    -- Table to hold a couple columns from the MSSDocumentImage records getting deleted.
    declare @theDeletedDocumentImages table
    (
        ExternalImageId int,
        ExternalImageDbId int
    )

    -- Table to hold the primary keys and names of the distinct external document image databases
    -- that store the images for the MSSDocumentImage records getting deleted.
    declare @theUniqueDocumentImageDatabases table
    (
        Id int identity(1,1) primary key clustered,
        ExternalImageDb sysname,
        ExternalImageDbId int
    )
    declare @thePrimaryDocDBName sysname  
    set @thePrimaryDocDBName = dbo.GetDocDbName()

    -- Get the deleted document images, along with the ID of the database that the document image lives in.
    insert into @theDeletedDocumentImages
    select
        ExternalImageId = deleted.ExtImageFID,
        ExternalImageDbId = deleted.MSSDocumentImageDBFID
    from deleted
    where deleted.ExtImageFID is not null

    -- Get a unique list of the document image databases that will need to delete from.
    -- Inner join sys.databases to make sure the databases actually exist on the server.
    insert into @theUniqueDocumentImageDatabases
    select distinct
        isnull( MSSDocumentImageDB.DatabaseName, @thePrimaryDocDBName ),
        MSSDocumentImageDB.MSSDocumentImageDBID
    from @theDeletedDocumentImages as DeletedImages
    inner join MSSDocumentImageDB on MSSDocumentImageDB.MSSDocumentImageDBID = DeletedImages.ExternalImageDbId
    inner join sys.databases on databases.[name] = dbo.MSSDocumentImageDB.DatabaseName

    if exists ( select top 1 1 from @theUniqueDocumentImageDatabases )
    begin
        -- Get ready to loop through each of the unique document image databases.
        declare @theRowIterator int
        declare @theRowCount int
        select
            @theRowIterator = 1,
            @theRowCount = count(*) from @theUniqueDocumentImageDatabases
        declare @theDeleteCommand nvarchar(max)
        declare @theDocDbName sysname
        declare @theDocDbId int

        while( @theRowIterator <= @theRowCount )
        begin
            declare @theImageIdsToDelete IntList
        
            -- Get the name and ID of the document image database to delete from.
            select
                @theDocDBName = ExternalImageDb,
                @theDocDbId = ExternalImageDbId
            from @theUniqueDocumentImageDatabases where Id = @theRowIterator

            -- Get the primary keys of the images we need to delete from the current document image database.
            insert into @theImageIdsToDelete
            select ExternalImageId from @theDeletedDocumentImages where ExternalImageDbId = @theDocDbId

            -- Prepare a delete command and run it.
            set @theDeleteCommand =
            'delete ' + @theDocDBName + '..DocumentImage ' +
            'from @inExternalDocumentImageIDs theExternalDocumentImageIDs ' +
            'inner join ' + @theDocDBName + '..DocumentImage DocumentImage on theExternalDocumentImageIDs.Item = DocumentImage.DocumentImageID '
            exec sp_executesql @theDeleteCommand, N'@inExternalDocumentImageIDs IntList readonly', @theImageIdsToDelete

            -- Increment the row iterator.
            set @theRowIterator = @theRowIterator + 1;
        end
    end
GO
ALTER TABLE [dbo].[MSSDocumentImage] ADD CONSTRAINT [pk_MSSDocumentImage] PRIMARY KEY CLUSTERED  ([MSSDocumentImageID]) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_MSSDocumentImage_ExtImageFID_MSSDocumentImageDBFID] ON [dbo].[MSSDocumentImage] ([ExtImageFID], [MSSDocumentImageDBFID]) WHERE ([ExtImageFID] IS NOT NULL AND [MSSDocumentImageDBFID] IS NOT NULL) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_MSSDocumentImage_Order_DocName] ON [dbo].[MSSDocumentImage] ([OrderFID], [DocumentName]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[MSSDocumentImage] ADD CONSTRAINT [FK_MSSDocumentImage_MSSDocumentImageDB] FOREIGN KEY ([MSSDocumentImageDBFID]) REFERENCES [dbo].[MSSDocumentImageDB] ([MSSDocumentImageDBID])
GO
ALTER TABLE [dbo].[MSSDocumentImage] ADD CONSTRAINT [FK_MSSDocumentImage_Orders] FOREIGN KEY ([OrderFID]) REFERENCES [dbo].[Orders] ([PriKey])
GO
GRANT SELECT ON  [dbo].[MSSDocumentImage] TO [MssExec]
GRANT INSERT ON  [dbo].[MSSDocumentImage] TO [MssExec]
GRANT DELETE ON  [dbo].[MSSDocumentImage] TO [MssExec]
GRANT UPDATE ON  [dbo].[MSSDocumentImage] TO [MssExec]
GO