Логирование детали Файлы

Может update для tbl_Files решит вопрос ? :wink:
Какая версия исполняемых файлов на Вашей машине?

Версия 3.3.2.47
Terrasoft Sales

Игорь, а что вы имеете ввиду под апдейтом tbl_Files?
Поле ActionID хранится только в таблицах логов и содержимое там стандартное, на сколько я понимаю.

Светлана,

Дело в том, что есть бага в хранимой процедуре, которая генерирует триггера к таблицам.
Мы об этой проблеме сообщали команде, которая внедряла нам Террасофт (из самого Террасофта).

Действительно, по-идее должно быть следующее:
I - вставка (Insert)
U - обновление (Update)
D - удаление (Delete)

Есть:
D - вставка
U - обновление
D - удаление

komgbu,

а вам эту багу еще не исправили?

Светлана,

Нет, не исправили, у нас много других более критичных проблем, помимо этой :)
Я думаю стоит подождать, пока ответят разработчики из Террасофт в этой ветке.

Здравствуйте,
В хранимой процедуре tsp_TableTrigger поменяйте строчку 306
[sql]
' ,''D'' as [ActionID]' + @NL +
[/sql]
на
[sql]
' ,''I'' as [ActionID]' + @NL +
[/sql]
и пересохраните таблицу, что бы пересоздались тригерра.

Проверял на tbl_ContactLog, все работает корректно

В итоге хранимая процедура будет иметь вид:
[sql]
--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
[/sql]

Алексей, все получилось, спасибо! :)