Tables [dbo].[MSSDocumentImage]
Properties
PropertyValue
CollationSQL_Latin1_General_CP1_CI_AS
Row Count (~)0
Created3:21:00 PM Wednesday, September 26, 2012
Last Modified7:36:15 PM Wednesday, February 28, 2024
Columns
NameData TypeMax Length (Bytes)Allow NullsIdentity
Cluster Primary Key pk_MSSDocumentImage: MSSDocumentImageIDMSSDocumentImageIDint4
No
1 - 1
Foreign Keys FK_MSSDocumentImage_Orders: [dbo].[Orders].OrderFIDIndexes IX_MSSDocumentImage_Order_DocName: OrderFID\DocumentNameOrderFIDint4
No
Indexes IX_MSSDocumentImage_Order_DocName: OrderFID\DocumentNameDocumentNamevarchar(128)128
No
DocumentImageimagemax
Yes
Indexes IX_MSSDocumentImage_ExtImageFID_MSSDocumentImageDBFID: ExtImageFID\MSSDocumentImageDBFIDExtImageFIDint4
Yes
Foreign Keys FK_MSSDocumentImage_MSSDocumentImageDB: [dbo].[MSSDocumentImageDB].MSSDocumentImageDBFIDIndexes IX_MSSDocumentImage_ExtImageFID_MSSDocumentImageDBFID: ExtImageFID\MSSDocumentImageDBFIDMSSDocumentImageDBFIDint4
Yes
Indexes Indexes
NameColumnsUniqueFiltered
Cluster Primary Key pk_MSSDocumentImage: MSSDocumentImageIDpk_MSSDocumentImageMSSDocumentImageID
Yes
IX_MSSDocumentImage_ExtImageFID_MSSDocumentImageDBFIDExtImageFID, MSSDocumentImageDBFID
Yes
Yes
IX_MSSDocumentImage_Order_DocNameOrderFID, DocumentName
Yes
Triggers Triggers
NameANSI Nulls OnQuoted Identifier OnOn
DocumentImageDeleted
Yes
Yes
After Delete
Foreign Keys Foreign Keys
NameColumns
FK_MSSDocumentImage_MSSDocumentImageDBMSSDocumentImageDBFID->[dbo].[MSSDocumentImageDB].[MSSDocumentImageDBID]
FK_MSSDocumentImage_OrdersOrderFID->[dbo].[Orders].[PriKey]
Permissions
TypeActionOwning Principal
GrantDeleteMssExec
GrantInsertMssExec
GrantSelectMssExec
GrantUpdateMssExec
SQL Script
CREATE TABLE [dbo].[MSSDocumentImage]
(
[MSSDocumentImageID] [int] NOT NULL IDENTITY(1, 1),
[OrderFID] [int] NOT NULL,
[DocumentName] [varchar] (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
Uses
Used By