Дополнения к посту
3
-- Добавление записи о сессии
CREATE TRIGGER [dbo].[DATA_UPDATE_USER]
ON [dbo].[REP_SESSIONS] FOR INSERT
AS
BEGIN
SET NOCOUNT ON;
declare @per_oid numeric(18,0)
declare @row as Int
set @per_oid = (select top 1 ITSM_PERSONS.PER_OID from inserted i
inner join ITSM_PERSONS ON i.SES_ACC_OID = ITSM_PERSONS.PER_ACC_OID)
set @row = (select count(*)
from ITSM_PER_CFT001 t1
where t1.ITSM_PER_CFT001_oid = @per_oid
and DateDiff(day, A0$_WORKDAY, getUTCDate()) = 0)
If (@row = 0) and (IsNull(@per_oid, 0) > 0)
begin
update ITSM_PER_CUSTOM_FIELDS
set PEC_PERSONDATE1 = getUTCDAte()
where PEC_PER_OID = @per_oid
-- and DateDiff(day, A0$_WORKDAY, getUTCDate())=0
insert into ITSM_PER_CFT001(ITSM_PER_CFT001_oid, A0$_WORKDAY, A0$_REG_PERS2, A0$_UNREG_PERS2)
values(@per_oid, cast(round(cast(getUtcDate() as float), 0, 1) as datetime), getUtcDate(), NULL)
end
END
_________________________________________
-- Удаление записи о сессии
CREATE TRIGGER [dbo].[REP_SESSIONS_D]
ON [dbo].[REP_SESSIONS]
FOR DELETE
AS
BEGIN
-- Update ITSM_PER_CUSTOM_FIELDS - время UnLogin
UPDATE ITSM_PER_CUSTOM_FIELDS
SET PEC_PERSONDATE2 = getUTCDate()
FROM deleted d
inner join ITSM_PERSONS ON ITSM_PERSONS.PER_ACC_OID = d.SES_ACC_OID
inner join ITSM_PER_CUSTOM_FIELDS as pe ON ITSM_PERSONS.PER_OID = pe.PEC_PER_OID
WHERE (d.SES_ACC_OID <> 1)
-- Update ITSM_PER_CFT001 - время UnLogin
update ITSM_PER_CFT001 set
A0$_UNREG_PERS2 = getUTCDate()
from deleted
inner join ITSM_PERSONS ON ITSM_PERSONS.PER_ACC_OID = deleted.SES_ACC_OID
inner join ITSM_PER_CFT001 ON ITSM_PERSONS.PER_OID = ITSM_PER_CFT001.ITSM_PER_CFT001_oid
and DateDiff(day, ITSM_PER_CFT001.A0$_WORKDAY, getUTCDate()) = 0
END
Структура таблицы журнала учёта рабочего времени в самом простейшем виде:
ВНИМАНИЕ данное поле создано в ручную из админки в SD (Поля менялись в ручную в базе после создания)
Key Colunm name Data type Length Allow nulls
3 Id_Journal int 4 0
0 ITSM_PER_CFT001_oid decimal 9 0
0 A0$_WORKDAY datetime 8 0
0 A0$_REG_PERS2 datetime 8 1
0 A0$_UNREG_PERS2 datetime 8 1
Дополнительно введено ключевое поле "Id_Journal", иначе с двойным ключём по полям ID и Дате сервисдеску было не по себе. Данный триггер доводил до ума
Виталий Сидоров