USP_OLAPATTRIBUTECATEGORYSELECTION_CREATEETLDELETEDID

CREATE procedure dbo.[USP_OLAPATTRIBUTECATEGORYSELECTION_CREATEETLDELETEDID]
with execute as owner as
  set nocount on;

  declare @TABLENAME nvarchar(100);
  declare @AUDITTABLENAME nvarchar(105);
  declare @ETLDELETEDIDTABLENAME nvarchar(118);
  declare @TRIGGERNAME nvarchar(255);
  declare @LASTREFRESH datetime;
  declare @SQL nvarchar(max);

  --Get last completed refresh time for the warehouse

  select @LASTREFRESH = max([DATAWINDOWCLOSE]) from dbo.[ETLHISTORY] where [COMPLETED] = 1;

  --Cycle through each attribute being brought into the warehouse

  declare ATTRIBUTECURSOR cursor local fast_forward for
    select tc.[TABLENAME]
    from dbo.[OLAPDATASOURCEATTRIBUTECATEGORY] o
    inner join dbo.[ATTRIBUTECATEGORY] ac on o.[ATTRIBUTECATEGORYID] = ac.[ID]
    inner join dbo.[TABLECATALOG] tc on ac.[TABLECATALOGID] = tc.[ID];

  open ATTRIBUTECURSOR;
  fetch next from ATTRIBUTECURSOR into @TABLENAME;
    while (@@FETCH_STATUS = 0)
    begin  
      set @AUDITTABLENAME = @TABLENAME + 'AUDIT';
      set @ETLDELETEDIDTABLENAME = @AUDITTABLENAME + '_ETLDELETEDID';
      set @TRIGGERNAME = 'TR_' + @TABLENAME + '_AUDIT_ETLDELETEDID';

      --Check for attribute table existence

      if exists(select 1 from sys.tables where name = @TABLENAME)
      begin
        --Check for ETLDELETEDID table 

        if not exists(select 1 from sys.tables where name = @ETLDELETEDIDTABLENAME)
        begin
          --Create ETLDELETEDID table for the attribute table

          set @SQL = 'create table [BBETLAUDIT].[' + @ETLDELETEDIDTABLENAME + ']' + char(10) +
                     '(' + char(10) +
                     '  [AUDITRECORDID] [uniqueidentifier] not null,' + char(10) +
                     '  [AUDITDATE] datetime not null,' + char(10) +
                     '  [AUDITTYPECODE] as 2,' + char(10) +
                     '  constraint [PK_' + @ETLDELETEDIDTABLENAME + '] primary key clustered ([AUDITDATE],[AUDITRECORDID])' + char(10) +
                     ') on [AUDITGROUP];'; 

          exec sp_executesql @SQL;

          --Populate newly created ETLDELETEID table

          set @SQL =  'insert into [BBETLAUDIT].[' + @ETLDELETEDIDTABLENAME + '] ([AUDITRECORDID], [AUDITDATE])' + char(10) +
                      ' select [AUDITRECORDID], [AUDITDATE] from dbo.[' + @AUDITTABLENAME + '] where [AUDITDATE] >= @LASTREFRESH and [AUDITTYPECODE] = 2;'                                

          exec sp_executesql @SQL, N'@LASTREFRESH datetime', @LASTREFRESH = @LASTREFRESH;

          --Grant permission on the ETLDETELTEDID table to the roles

          set @SQL = 'grant select on [BBETLAUDIT].[' + @ETLDELETEDIDTABLENAME + '] to BBAPPFXREPORTROLE;';
          exec sp_executesql @SQL;

          set @SQL = 'grant select on [BBETLAUDIT].[' + @ETLDELETEDIDTABLENAME + '] to BBAPPFXSERVICEROLE;';
          exec sp_executesql @SQL;
        end

        --Check to see if a trigger exists on the base table

        if not exists(select 1 from sys.triggers where name = @TRIGGERNAME)
        begin
          set @SQL =  'create trigger [dbo].[' + @TRIGGERNAME + '] on [dbo].[' + @TABLENAME + '] after delete not for replication as' + char(10) +
                      ' -- data mart incremental refresh support' + char(10) +

                      ' set nocount on;' + char(10) +
                      ' declare @AuditDate datetime = GetDate();' + char(10) + char(10) +
                      ' insert into [BBETLAUDIT].[' + @ETLDELETEDIDTABLENAME + '] ([AUDITRECORDID], [AUDITDATE])' + char(10) +
                      '   select [ID], @AuditDate from DELETED;';

          exec sp_executesql @SQL;                    
        end
      end

      fetch next from ATTRIBUTECURSOR into @TABLENAME;
    end

  close ATTRIBUTECURSOR;
  deallocate ATTRIBUTECURSOR;