Протоколирование изменений

В каждой таблице системы МЕДИАЛОГ имеются поля, в которых фиксируется пользователь, создавший записи и дата создания, а также пользователь, последний изменивший запись и дата последнего изменения записи. Это соответственно поля KRN_CREATE_USER_ID, KRN_CREATE_DATE, KRN_MODIFY_USER_ID и KRN_MODIFY_DATE. В качестве пользователя в этих полях фиксируется ID пользователя (MEDECINS_ID).

Кроме того, в системе поддерживается протоколирование редактирования и удаления данных в таблицах. Для этого в меню Настройка / Список пользователей и права доступа в разделе Протоколирование изменений необходимо отметить таблицы, которые будут протоколироваться (рис. 10.26).

Протоколирование изменений в таблицах без первичного ключа или с нецелочисленым первичным ключом недоступно.

Рисунок 10.26. Протоколирование изменений: выбор таблиц

В таблицах KRN_SYS_TRACE и KRN_SYS_TRACE_LOG при каждом изменении протоколируемых таблиц будут появляться соответствующие сведения о пользователе, сделавшем изменение в протоколируемой таблице, дате и времени изменения, типе действия, прежнем и новом значении измененного поля в таблице.

В таблице KRN_SYS_TRACE можно увидеть следующее:

TABLE_NAME – внутреннее имя таблицы, в которой было сделано изменение (удаление).

REC_ID – ID записи, в которой было сделано изменение (удаление)

USER_ID – логин пользователя, сделавшего изменение (ID SQL-логина).

DATE_CHANGE – дата изменения (удаления) записи.

ACTION – тип действия, u – update (изменение), d – delete (удаление).

В таблице KRN_SYS_TRACE_LOG в поле USER_ID вместо ID SQL-логина прописывается ID пользователя в системе МЕДИАЛОГ (MEDESINS_ID). Помимо вышеперечисленных полей в данной таблице присутствует поле LOG, в котором в текстовом виде собрана вся информация о модификации записи.

В таблице KRN_SYS_DELETE_TRACE фиксируется информация об удаленных записях:

TABLE_NAME – имя таблицы, из которой удалена запись.

REC_ID – ID удаленной записи.

DBUSER_ID –

EXTERNAL_USER_ID – пользователь, удаливший запись.

DATE_DELETE – дата удаления.

HOST_NAME – имя компьютера, с которого произведено удаление.

Пример лога из таблицы KRN_SYS_TRACE_LOG для действия UPDATE:

UserID = 68 DateTime = 24/01/2004 10:38:15 Action = UPDATE
PATIENT_ARRIVEE_OLD = False PATIENT_ARRIVEE_NEW = True
MODIFY_DATE_TIME_OLD = MODIFY_DATE_TIME_NEW = 24/01/2004 10:34:32 MEDECINS_MODIFY_ID_OLD = MEDECINS_MODIFY_ID_NEW = 1369

Из вышеприведенного лога можно увидеть, что пользователем с номером логина 68 24/01/2004 в 10:38:15 была произведена модификация строки № 126459 в таблице PLANNING (Записи в расписании). При этом были сделаны следующие изменения: поле PATIENT_ARRIVEE (Пациент пришел) изменено с положения false на true, поле MODIFY_DATE_TIME (дата и время редактирования записи) изменилось на 24/01/2004 10:34:32, а поле MEDECINS_MODIFY_ID (Пользователь, изменивший запись) изменилось на значение 1369.

Пример лога из таблицы KRN_SYS_TRACE_LOG для действия DELETE:

UserID=15 DateTime=20/01/2004 14:46:38 Action=DELETE PLANNING_ID=129502 PL_SUBJ_ID=1343 DATE_CONS=05/01/2004 HEURE=1430 DUREE=30
PATIENTS_ID=140620 NOM=ИВАНОВА PRENOM=Наталья MOTIF=""
COMMENTAIRE= COLOR=65535 FONT=-2147483640 DUREE_TEXT=3

В этом случае пользователем с номером логина 15 20/01/2004 в 14:46:38 было удалена запись пациента «ИВАНОВА Наталья» с PATIENTS_ID=140620. Запись была в расписании с ID= 1343 на 05/01/2004 в 14:30 продолжительностью 30 минут.

Просматривать протокол изменений можно с помощью утилиты SQL Query Analyzer. Узнать ID пользователей SQL-сервера поможет процедура sp_helpuser, которая выполняется в SQL Query Analyzer.

Пример скрипта для поиска записей по таблице KRN_SYS_TRACE_LOG:

SELECT * FROM KRN_SYS_TRACE_LOG

WHERE TABLE_NAME='PLANNING'

AND DATE_CHANGE BETWEEN '2004-01-19 08:00' AND '2004-01-20 20:00'

AND ACTION = 'D'

Этот скрипт позволяет найти все удаленные записи из таблицы PLANNING за период между 2004-01-19 08:00 и 2004-01-20 20:00.

Очистка лога

Следующий скрипт используется для очистки таблиц протоколирования

declare @NumDays int

/**********************************************************/

/*установка числа дней от текущей даты до даты, по которую будут удаляться записи */

set @NumDays = 100

/**********************************************************/

alter table KRN_SYS_TRACE_LOG DISABLE TRIGGER ALL

alter table KRN_SYS_TRACE DISABLE TRIGGER ALL

alter table KRN_SYS_DELETE_TRACE DISABLE TRIGGER ALL

delete from KRN_SYS_TRACE_LOG where DateDiff(d,DATE_CHANGE,GetDate())>= @NumDays

delete from KRN_SYS_TRACE where DateDiff(d,DATE_CHANGE,GetDate()) >= @NumDays

delete from KRN_SYS_DELETE_TRACE where DateDiff(d,DATE_DELETE,GetDate())>=@NumDays

alter table KRN_SYS_TRACE_LOG ENABLE TRIGGER ALL

alter table KRN_SYS_TRACE ENABLE TRIGGER ALL

alter table KRN_SYS_DELETE_TRACE ENABLE TRIGGER ALL