'    ,''D'' as [ActionID]' + @NL + 

 '    ,''I'' as [ActionID]' + @NL + 

--USE [XRMDistri_Demo_RUS_3_3_2_172]
GO
/****** Object:  StoredProcedure [dbo].[tsp_TableTrigger]    Script Date: 12/12/2011 10:40:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[tsp_TableTrigger]
  @ATableName sysname,
  @TrackFieldsInfo varchar(8000),
  @TrackFieldsInfoForDelete varchar(8000),
  @ATrackChangesEnabled int = 1,
  @AClosingPeriodEnabled int = 0,
  @ADBSchema sysname = 'dbo'
as
begin
  set nocount on
------------------- declare -------------------------
 
  declare @SchemaTableName sysname
  declare @TableName sysname
  declare @TableLogName sysname
  declare @ShortTableName sysname
  declare @InteadOfInsertTriggerName sysname
  declare @UpdateTriggerName sysname
  declare @DeleteTriggerName sysname
  declare @UpdateTriggerNameOld sysname
  declare @DeleteTriggerNameOld sysname
  declare @NL sysname
  declare @Column varchar(150)
  declare @LookupColumn varchar(150)
  declare @Line int
  declare @CheckReplication varchar(250)
  declare @DisplayColumn varchar(33)
  declare @SchemaJoinTable varchar(70)
  declare @JoinTable varchar(33)
  declare @JoinField varchar(33)
  declare @DBName sysname  
  declare @RecordTitle varchar(40)
  declare @TableID varchar(36)
  declare @Comma varchar(10)
  declare @Version int
  declare @Trigger2005 varchar(8000)
  declare @DatabaseLogBegin varchar(1000)
  declare @ContactID varchar(1000)
  declare @InsertTableLogBegin varchar(1000)
  declare @DBErrorMessageBegin nvarchar(10)
  declare @DBErrorMessageEnd nvarchar(10)
  declare @IsAdministratedByRecords varchar(10)
 
---------------- clear old triggers -----------------
 
  set @IsAdministratedByRecords = 
    (select Upper(p.Value) from tbl_Service s left outer join tbl_ServiceProperty p on s.id = p.ServiceID
      where s.Code = @ATableName and p.Name = 'IsAdministratedByRecords');
 
  set @TableName = '[' + @ATableName + ']'  
  set @InteadOfInsertTriggerName = '[' + @ADBSchema + '].[tr_' + @ATableName + '_II]'
  set @UpdateTriggerName = '[' + @ADBSchema + '].[tr_' + @ATableName + '_IU]'
  set @DeleteTriggerName = '[' + @ADBSchema + '].[tr_' + @ATableName + '_ID]'
 
  if (not object_id(@InteadOfInsertTriggerName, 'TR') is null)
  begin
    exec('DROP TRIGGER ' + @InteadOfInsertTriggerName)
  end  
  if (not object_id(@UpdateTriggerName, 'TR') is null)
  begin
    exec('DROP TRIGGER ' + @UpdateTriggerName)
  end
  if (not object_id(@DeleteTriggerName, 'TR') is null)
  begin
    exec('DROP TRIGGER ' + @DeleteTriggerName)
  end  
 
  if ((not isnull(@ATrackChangesEnabled, 0) = 1) and 
	(not isnull(@AClosingPeriodEnabled, 0) = 1))
  begin
    exec tsp_CreateDeleteTrigger 
      @Table = @ATableName,
      @IsTrackChanges = 0,
	    @UseClosingPeriod = 0
    return
  end
---------------- initialization ---------------------
 
  set @SchemaTableName = '[' + @ADBSchema + '].' + @TableName  
  set @TableLogName = '[' + @ADBSchema + '].[' + @ATableName + 'Log]'  
  set @ShortTableName = replace(@ATableName, 'tbl_', '')
 
  set @DBName = db_name()
  set @NL = char(13) + char(10)
  set @CheckReplication = 
    '  IF (SYSTEM_USER = ''TS_REPLICATION'') RETURN ' + @NL
  set @TableID = (
    select top 1 [ID] from [tbl_Service] [S]
    where [S].[Code] = @ATableName)
 
  if (@TableID is null) 
  begin
    RAISERROR ('Can''t track changes for new table', 16, 1)
    return
  end
 
  if (@AClosingPeriodEnabled = 1)
  begin
    set @DBErrorMessageBegin = '<#'
    set @DBErrorMessageEnd = '#>'
 
    declare @QuerySQLText nvarchar(4000)
    declare @ClosedPeriodMessage nvarchar(270)
    select
          @ClosedPeriodMessage = replace(@DBErrorMessageBegin + 
						[ClosedPeriodMessage] + @DBErrorMessageEnd, '%', '%%')
    from [tbl_PreventEditing] where [RootTableID] = @TableID     
    set @QuerySQLText = [dbo].[fn_GetClosingPeriodCheckSQL](@TableID)    
 
    declare @UpdateCheckSQLText nvarchar(4000)
    set @UpdateCheckSQLText = @QuerySQLText    
  end 
 
  create table #Trigger ( 
    [Line] int not null identity(1, 1), 
    [Trigger_Text] nvarchar(4000)
  )
 
  create table #TrackFields ( 
    [Line] int not null identity(1, 1), 
    [TrackField] varchar(8000)
  )
 
  insert into #TrackFields ([TrackField])
  select [Part] from [fn_ParseString](@TrackFieldsInfo, ';')
  order by [Line]
 
  set @RecordTitle = ''''''
  set @Line = (select top 1 [Line] from #TrackFields)
  select @Column = [TrackField] from #TrackFields where [Line] = @Line
  if ((@Column is not null) and (@Column <> '') and (@Column <> '...'))  
     set @RecordTitle = '[D].[' + @Column + ']'
  delete from #TrackFields where [Line] = @Line  
 
  set @Version = @@microsoftversion /0x01000000
 
  if (@Version > 8)   
  begin
    set @Trigger2005 =
	    'declare @TriggerText nvarchar(max) ' + @NL + 
	    'set @TriggerText = '''' ' + @NL + 
      'declare @TriggerRec nvarchar(max) ' + @NL +
      'set @TriggerRec = '''' ' + @NL +
      'declare [c] cursor for ' + @NL +
      '  select [Trigger_Text] from #Trigger order by [Line] ' + @NL +
      'open [c] ' + @NL +
      'while (1 = 1) ' + @NL +
      'begin ' + @NL +
      '  fetch next from [c] into @TriggerRec ' + @NL +
      '  if @@fetch_status = -1 break ' + @NL +
      '  if @@fetch_status = -2 continue   ' + @NL +
      '  set @TriggerText = @TriggerText + @TriggerRec ' + @NL +
      'end ' + @NL +
      'close [c] ' + @NL +
      'deallocate [c] ' + @NL +
      'exec (@TriggerText)' 
  end
 
  set @ContactID = @NL +  
    '  declare @ContactID uniqueidentifier' + @NL +
    '  declare @UserIsAdmin int' + @NL +
    '  select @ContactID = au.UserContactID, ' + @NL +
    '         @UserIsAdmin = au.UserIsAdmin ' + @NL +
    '    from tbl_AdminUnit au' + @NL +
    '  where au.SQLObjectName = system_user' + @NL + @NL
 
  set @DatabaseLogBegin =
    '  insert into [tbl_DatabaseLog]' + ' (' + @NL + 
    '    [TableID]' + @NL +
    '    ,[RecordID]' + @NL +
    '    ,[RecordTitle]' + @NL +
    '    ,[ActionID]' + @NL +
    '    ,[CreatedOn]' + @NL +
    '    ,[CreatedByID]' + @NL +
    '  ) ' + @NL
 
------------ instead of update ----------------------
 
  declare @AdministratedByRecords varchar(10)
  set @AdministratedByRecords = 
    (select Upper(p.Value) from tbl_Service s left outer join tbl_ServiceProperty p on s.id = p.ServiceID
      where s.Code = @ATableName and p.Value = 'True'
        and p.Name = 'IsAdministratedByRecords')
 
  exec tsp_CreateUpdateTrigger
    @ADBSchema = @ADBSchema,
    @ATableName = @ATableName,
    @IsTable = 1,
    @AClosingPeriodEnabled = @AClosingPeriodEnabled,
    @AdministratedByRecords = @AdministratedByRecords,
    @ATrackChangesEnabled = @ATrackChangesEnabled,
    @TableLogName = @TableLogName,
    @TrackFieldsInfo = @TrackFieldsInfo,
    @TrackFieldsInfoForDelete = @TrackFieldsInfoForDelete
  if (@AdministratedByRecords = 'TRUE')
  begin
    exec tsp_CreateUpdateTrigger
      @ADBSchema = @ADBSchema,
      @ATableName = @ATableName,
      @IsTable = 0,
      @AClosingPeriodEnabled = @AClosingPeriodEnabled,
      @AdministratedByRecords = @AdministratedByRecords,
      @ATrackChangesEnabled = @ATrackChangesEnabled,
      @TableLogName = @TableLogName,
      @TrackFieldsInfo = @TrackFieldsInfo,
      @TrackFieldsInfoForDelete = @TrackFieldsInfoForDelete
  end
 
------------ instead of delete ----------------------
 
  exec tsp_CreateDeleteTrigger 
    @Table = @ATableName,
    @IsTrackChanges = @ATrackChangesEnabled,
	  @UseClosingPeriod = @AClosingPeriodEnabled,
    @RecordTitle = @RecordTitle,
    @DBSchema = @ADBSchema
 
------------ instead of insert ----------------------
if ((@AClosingPeriodEnabled = 1) or (@ATrackChangesEnabled = 1))
begin
  delete from #Trigger
  insert into #Trigger ([Trigger_Text]) select  
    'create trigger ' + @InteadOfInsertTriggerName + ' on ' + @SchemaTableName + @NL +
    'instead of insert' + @NL + 
    'as' + @NL + 
    '  set nocount on' + @NL 
 
  if (@AClosingPeriodEnabled = 1)
  begin
    declare @InsertCheckSQLText nvarchar(4000)
    set @InsertCheckSQLText = @QuerySQLText    
    set @InsertCheckSQLText = replace(@InsertCheckSQLText, @SchemaTableName, '[inserted]')
    insert into #Trigger ([Trigger_Text]) select @NL +
      '  declare @DenyRecordsCount int ' + @NL +
      '  set @DenyRecordsCount = 0 ' + @NL + 
      '  SELECT ' + @NL +
      '      @DenyRecordsCount = count(*) ' + @NL + @InsertCheckSQLText + @NL + @NL +
      '  if (@DenyRecordsCount > 0) ' + @NL +
      '  begin ' + @NL +
      '    RAISERROR(''' + isnull(@ClosedPeriodMessage, '') + ''', 16, 1) ' + @NL +
      '    return ' + @NL +
      '  end ' + @NL
  end
 
  insert into #Trigger ([Trigger_Text]) select @NL +
    '  insert into ' + @SchemaTableName + ' select * from [inserted]'
 
  if (@ATrackChangesEnabled = 1)
  begin
    insert into #Trigger ([Trigger_Text]) select @NL + 
      @ContactID +
      '  insert into ' + @TableLogName + ' (' + @NL +
      '    [RecordID]' + @NL +
      '    ,[ActionID]' + @NL +
      '    ,[CreatedOn]' + @NL +
      '    ,[CreatedByID]'    
 
    delete from #TrackFields
    insert into #TrackFields ([TrackField])
    select [Part] from [fn_ParseString](@TrackFieldsInfoForDelete, ';')  
    order by [Line]
 
    declare [c1] cursor for
      select [TrackField] from #TrackFields
      order by 1
 
    open [c1]
    while (1 = 1)
    begin    
      fetch next from [c1] into @Column
      if @@fetch_status = -1 break
      if @@fetch_status = -2 continue
 
      if (patindex('%,%', @Column) > 0) 
      begin 
        declare [c2] cursor for
        select [Part] from [fn_ParseString](@Column, ',')
        order by [Line]
 
        open [c2]
        fetch next from [c2] into @Column
        fetch next from [c2] into @DisplayColumn
        close [c2]
        deallocate [c2]
 
        insert into #Trigger ([Trigger_Text])
        select @NL + '    ,[' + @DisplayColumn + ']'
      end
      insert into #Trigger ([Trigger_Text])
      select @NL + '    ,[' + @Column + ']'
    end
    close [c1]
    deallocate [c1]
 
    insert into #Trigger ([Trigger_Text]) select @NL + 
      '  ) ' + @NL + 
      '  select' + @NL + 
      '    [D].[ID] as [RecordID]' + @NL +
      '    ,''I'' as [ActionID]' + @NL + 
      '    ,getdate() as [CreatedOn]' + @NL +
      '    ,[D].[ModifiedByID] as [CreatedByID]'
 
    declare [c1] cursor for
      select [TrackField] from #TrackFields
      order by 1
 
    open [c1]
    while (1 = 1)
    begin
      fetch next from [c1] into @Column
      if @@fetch_status = -1 break
      if @@fetch_status = -2 continue
      if (patindex('%,%', @Column) > 0) 
      begin      
        declare [c2] cursor for
        select [Part] from [fn_ParseString](@Column, ',')
        order by [Line]
 
        open [c2]
        fetch next from [c2] into @Column
        fetch next from [c2] into @DisplayColumn
        fetch next from [c2] into @JoinTable
        fetch next from [c2] into @JoinField
 
        set @DisplayColumn = '[' + @DisplayColumn + ']'
        set @JoinTable = '[' + @JoinTable + ']'
        set @SchemaJoinTable = '[' + @ADBSchema + '].' + @JoinTable
        set @JoinField = '[' + @JoinField + ']'
 
        close [c2]
        deallocate [c2]
 
        insert into #Trigger ([Trigger_Text]) select @NL +
          '   ,(select ' + @JoinTable + '.' + @JoinField + @NL +
          '      from ' + @SchemaJoinTable + ' as ' + @JoinTable + @NL +
          '      where  ' + @JoinTable + '.[ID] = [D].[' + @Column + '])' + @NL + 
          '    as ' + @DisplayColumn
      end 
 
      insert into #Trigger ([Trigger_Text])
      select @NL + '    ,[D].[' + @Column + '] as [' + @Column + ']'
    end
    close [c1]
    deallocate [c1]
 
    insert into #Trigger ([Trigger_Text]) select @NL + 
      '  from [INSERTED] AS [D] ' + @NL
 
    insert into #Trigger ([Trigger_Text]) select @NL + 
      @DatabaseLogBegin +
      '  select' + @NL + 
      '    ''' + @TableID + ''' as [TableID]' + @NL + 
      '    ,[D].[ID] as [RecordID]' + @NL + 
      '    ,' + @RecordTitle + ' as [RecordTitle]' + @NL + 
      '    ,''I'' as [ActionID]' + @NL + 
      '    ,getdate() as [CreatedOn]' + @NL +
      '    ,@ContactID as [CreatedByID]' + @NL +    
      '  from [INSERTED] AS [D]' 
  end
 
  if (@Version = 8)
    exec master.dbo.xp_execresultset 'select [Trigger_Text] from #Trigger order by [line]', @DBName
  else
    exec (@Trigger2005)
end
end
drop table #TrackFields
drop table #Trigger