SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [SQLRunbook].[tDocument](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[File] [nvarchar](450) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Document] [varbinary](max) NULL,
	[DocumentType] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[LastModifiedDt] [datetime] NULL,
	[Owner] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[WatchFileForChange] [bit] NOT NULL,
	[RecCreatedDt] [datetime] NOT NULL,
	[RecCreatedUser] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[LastUpdatedDt] [datetime] NOT NULL,
	[LastUpdatedUser] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 CONSTRAINT [pkc_tDocument__Id] PRIMARY KEY NONCLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [qkn_tDocument__File] UNIQUE NONCLUSTERED 
(
	[File] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO

CREATE CLUSTERED INDEX [ixn_tDocument__RecCreatedDt] ON [SQLRunbook].[tDocument] 
(
	[RecCreatedDt] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [ixn_tDocument__LastModifiedDt] ON [SQLRunbook].[tDocument] 
(
	[LastModifiedDt] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [ixn_tDocument__LastUpdatedDt] ON [SQLRunbook].[tDocument] 
(
	[LastUpdatedDt] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [ixn_tDocument__LastUpdatedUser] ON [SQLRunbook].[tDocument] 
(
	[LastUpdatedUser] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [ixn_tDocument__Owner] ON [SQLRunbook].[tDocument] 
(
	[Owner] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [ixn_tDocument__RecCreatedUser] ON [SQLRunbook].[tDocument] 
(
	[RecCreatedUser] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
CREATE FULLTEXT INDEX ON [SQLRunbook].[tDocument](
[Document] TYPE COLUMN [DocumentType] LANGUAGE [Neutral], 
[File] LANGUAGE [Neutral])
KEY INDEX [pkc_tDocument__Id]ON ([ftSQLRunbookCatalog], FILEGROUP [PRIMARY])
WITH (CHANGE_TRACKING = AUTO, STOPLIST = SYSTEM)


GO
 ALTER AUTHORIZATION ON [SQLRunbook].[tDocument] TO  SCHEMA OWNER 
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/******************************************************************************
**	Auth: Bill Wunder
**	Date: April 1, 2007
**
*******************************************************************************
**	Change History
*******************************************************************************
**	Date		   Author		     Description of Change
**	Mar 10, 2009   bw                maintain row update audit columns       
**	          
*******************************************************************************/
CREATE TRIGGER [SQLRunbook].[trgDocument_Insert_Update_Delete]
ON [SQLRunbook].[tDocument]
FOR INSERT, UPDATE, DELETE
AS
BEGIN

 DECLARE @icount [INT]
  , @dcount [INT]
  , @DocumentId [INT]
  , @OriginalLogin NVARCHAR(256)
  , @Owner NVARCHAR(256)
  , @Op NVARCHAR(10);

 SET NOCOUNT ON;

 BEGIN TRY

  SELECT @icount = COUNT(*) FROM [inserted];
  SELECT @dcount = COUNT(*) FROM [deleted];
  SET @OriginalLogin  = ORIGINAL_LOGIN();

  -- no multi row
  IF @icount > 1 OR @dcount > 1
    RAISERROR ('Multi-row operations are not permitted', 16,1);

  -- EnforceOwnership - row level security - all other triggers reference these comments to aid consistency 
  -- only admins, the service or the document owner can update or delete if the host instance is also a 
  -- target instance the service account will be elevated to sysadmin for archive and trace. A sysadmin 
  -- will enter any database as dbo and the token will have no db role information (see sys.user_token)  
  -- Best to assure only members of the SQLRunbookAdminRole and SQLClueAdminRole roles are sa on the host
  IF @dcount = 1 
  AND (SELECT [SQLRunbook].[fnIsAdmin]()) = 0
  AND (SELECT [EnforceOwnership] FROM [SQLRunbook].[tOption]) = 1
   BEGIN
    SELECT @DocumentId = [Id], @Owner = [Owner] FROM [deleted] WHERE [Owner] <> @OriginalLogin; 
    IF @@ROWCOUNT = 1
     BEGIN
      SET @Op = CASE WHEN @icount = 1 AND @dcount = 1 THEN 'update' ELSE 'delete' END;
      RAISERROR ('User [%s] attempted to REMOVE Document %d owned by user [%s]'
                , 16
                , 1
                , @OriginalLogin
                , @DocumentId
                , @Owner);
     END;
   END;

  -- update the topic notes when associated documents are inserted, updated, or deleted
  -- always maintain the SQLRunbookUser
  
  -- delete
  IF @dcount = 1 and @icount = 0 
   EXEC [SQLRunbook].[pUserUpsert] @OriginalLogin = @OriginalLogin
                                    , @DocumentsDeleted = 1;   

  -- update
  IF @dcount = 1 and @icount = 1
   BEGIN
    UPDATE d
    SET [LastUpdatedDt] = CURRENT_TIMESTAMP
     , [LastUpdatedUser] = @OriginalLogin
    FROM [inserted] i
    JOIN [SQLRunbook].[tDocument] d
    ON i.[Id] = d.[Id]

    EXEC [SQLRunbook].[pUserUpsert] @OriginalLogin = @OriginalLogin
                                     , @DocumentsUpdated = 1;   

   END 

  -- insert
  IF @dcount = 0 and @icount = 1
   EXEC [SQLRunbook].[pUserUpsert] @OriginalLogin = @OriginalLogin
                                    , @DocumentsAdded = 1;   

 END TRY 

 BEGIN CATCH

  EXEC [SQLRunbook].[pLogSQLError] NULL, @@PROCID;

 END CATCH 

END;


GO
ALTER TABLE [SQLRunbook].[tDocument] ADD  CONSTRAINT [dft_tDocument_Owner]  DEFAULT (original_login()) FOR [Owner]
GO
ALTER TABLE [SQLRunbook].[tDocument] ADD  CONSTRAINT [dft_tDocument_WatchFileForChange]  DEFAULT ((1)) FOR [WatchFileForChange]
GO
ALTER TABLE [SQLRunbook].[tDocument] ADD  CONSTRAINT [dft_tDocument__RecCreatedDt]  DEFAULT (getdate()) FOR [RecCreatedDt]
GO
ALTER TABLE [SQLRunbook].[tDocument] ADD  CONSTRAINT [dft_tDocument_RecCreatedUser]  DEFAULT (original_login()) FOR [RecCreatedUser]
GO
ALTER TABLE [SQLRunbook].[tDocument] ADD  CONSTRAINT [dft_tDocument__LastUpdatedDt]  DEFAULT (getdate()) FOR [LastUpdatedDt]
GO
ALTER TABLE [SQLRunbook].[tDocument] ADD  CONSTRAINT [dft_tDocument_LastUpdatedUser]  DEFAULT (original_login()) FOR [LastUpdatedUser]
GO