Пользователь
Логин:
Пароль:
Забыли свой пароль?

Поиск по сайту
 

 Расширенный поиск
Реклама

 

Опрос


Погода

Триггеры на базу SQL

Форумы
Обновления
Поиск
Пользователи 
Правила
Помощь
Войти

Страницы: 1 2 След.
RSS
Триггеры на базу SQL, (Все что могло быть полезно для Service Desk 4.5)
Ведет подсчет кол. Заданий из вкладки Обращения. Прикручено к полю scf_scnumber6

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [dbo].[CountOfWorkorders_DELETEsc]
ON [dbo].[ITSM_WORKORDERS]
FOR DELETE
AS

<hr>

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [dbo].[CountOfWorkorders_UPDATEsc]
ON [dbo].[ITSM_WORKORDERS]
FOR UPDATE
AS
IF UPDATE(WOR_SER_OID)
BEGIN
IF NOT((SELECT WOR_SER_OID FROM inserted) is NULL)
BEGIN
UPDATE sc
SET sc.scf_scnumber6 = isnull(sc.scf_scnumber6,0) + 1
FROM inserted i
INNER JOIN ITSM_SER_CUSTOM_FIELDS sc ON i.WOR_SER_OID = sc.SCF_SER_OID
WHERE not(i.WOR_SER_OID is null)
END

UPDATE sc
SET sc.scf_scnumber6 = isnull(sc.scf_scnumber6,0) - 1
FROM deleted d
INNER JOIN ITSM_SER_CUSTOM_FIELDS sc ON d.WOR_SER_OID = sc.SCF_SER_OID
WHERE not(d.WOR_SER_OID is null)

END
IF UPDATE(WOR_ACTUALFINISH)
BEGIN
SET NOCOUNT ON
UPDATE sc
SET sc.scf_scnumber6 = isnull(sc.scf_scnumber6,0) - 1
FROM inserted i
INNER JOIN ITSM_SER_CUSTOM_FIELDS sc ON i.WOR_SER_OID = sc.SCF_SER_OID
WHERE not(i.WOR_ACTUALFINISH is null)
-- Update +1 if WO set back in production
UPDATE sc
SET sc.scf_scnumber6 = isnull(sc.scf_scnumber6,0) + 1
FROM inserted i
INNER JOIN ITSM_SER_CUSTOM_FIELDS sc ON i.WOR_SER_OID = sc.SCF_SER_OID
WHERE (i.WOR_ACTUALFINISH is null)
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

<HR>


SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [dbo].[CountOfWorkorders_ADDsc]
ON [dbo].[ITSM_WORKORDERS]
FOR INSERT
AS
BEGIN
set NOCOUNT ON;
UPDATE sc
SET sc.scf_scnumber6 = isnull(sc.scf_scnumber6,0) + 1
FROM inserted i
INNER JOIN ITSM_SER_CUSTOM_FIELDS sc ON i.WOR_SER_OID = sc.SCF_SER_OID
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Позволяет зарегистрировать письмо от человека которого нет в базе и запишет его мыло в поле информация.

<HR>
CREATE TRIGGER [GetMail]

ON [dbo].[ITSM_SER_INFORMATION]
AFTER INSERT
AS
BEGIN
declare @sMail varchar(1024)
declare @CALLER_ID decimal(18,0)
declare @ORG_ID decimal(18,0)
declare @CALLER_ORG_ID decimal(18,0)
DECLARE @errno int /* Номер ошибки */

IF ( (select ins.SEI_INFORMATION from inserted ins) like '%<%>%'
AND
(select SER_CALLER_PER from inserted ins, dbo.ITSM_SERVICECALLS
where ins.SEI_SER_OID=dbo.ITSM_SERVICECALLS.SER_OID) is Null)
BEGIN
IF EXISTS (SELECT isnull ((SUBSTRING(ins.SEI_INFORMATION, PATINDEX('%<%' , ins.SEI_INFORMATION)+1 , PATINDEX('%>%' , ins.SEI_INFORMATION) - PATINDEX('%<%' , ins.SEI_INFORMATION) -1)), '')
FROM inserted ins)
BEGIN
SELECT @sMail =(SUBSTRING(ins.SEI_INFORMATION, PATINDEX('%<%' , ins.SEI_INFORMATION)+1 , PATINDEX('%>%' , ins.SEI_INFORMATION) - PATINDEX('%<%' , ins.SEI_INFORMATION) -1))
FROM inserted ins
if @sMail <> '' begin
IF @sMail like '%@%%.%'
begin

update dbo.ITSM_SER_CUSTOM_FIELDS
set SCF_SCTEXT11 = @sMail
from inserted ins
where ins.SEI_SER_OID = SCF_SER_OID
select @CALLER_ID= PER_OID from ITSM_PERSONS
where ITSM_PERSONS.PER_EMAIL=@sMail
select @CALLER_ORG_ID=PER_ORG_OID from ITSM_PERSONS
where ITSM_PERSONS.PER_EMAIL=@sMail
update dbo.ITSM_SERVICECALLS
set SER_CALLER_PER=@CALLER_ID
from inserted ins
where ins.SEI_SER_OID=dbo.ITSM_SERVICECALLS.SER_OID
select @ORG_ID=ORG_OID from dbo.ITSM_ORGANIZATIONS
where ITSM_ORGANIZATIONS.ORG_OID=@CALLER_ORG_ID
update dbo.ITSM_SERVICECALLS
set SER_CALLER_ORG=@ORG_ID
from inserted ins
where ins.SEI_SER_OID=dbo.ITSM_SERVICECALLS.SER_OID

end
end
end
END
END
<HR>
Копирует в поле PEC_PERSONDATE4 время захода специалиста в Service Desk.
Поле Custom Filds

<HR>
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

-- =============================================
-- Author: Nenashev Grogory
-- ALTER date: 27.10.2009
-- =============================================

CREATE TRIGGER [dbo].[DATA_UPDATE_USER]
ON [dbo].[REP_SESSIONS]
FOR INSERT
AS
IF UPDATE (SES_CREATED)
BEGIN
UPDATE pe
SET pe.PEC_PERSONDATE4 = REP_SESSIONS.SES_CREATED
FROM inserted REP_SESSIONS
LEFT JOIN REP_ACCOUNTS ON REP_SESSIONS.SES_ACC_OID = REP_ACCOUNTS.ACC_OID
LEFT JOIN ITSM_PERSONS ON REP_ACCOUNTS.ACC_OID = ITSM_PERSONS.PER_ACC_OID
LEFT JOIN ITSM_PER_CUSTOM_FIELDS AS pe ON ITSM_PERSONS.PER_OID = pe.PEC_PER_OID
WHERE not (REP_SESSIONS.SES_CREATED is null)
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

<HR>
Вычитает Кол. Всех связанных Эл. в карточке обращения к полю scf_scnumber7

<HR>
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

-- =============================================
-- Author: Vasily Kamenev, Nenashev Grogory
-- ALTER date: 27.10.2009
-- =============================================

CREATE TRIGGER [dbo].[DBRule_setChangeToSCDELETE]
ON [dbo].[ITSM_SERVICE_RELATIONS]
FOR DELETE
AS
BEGIN
SET NOCOUNT ON;
IF UPDATE(SRE_SER_OID) AND UPDATE(SRE_CHA_OID)
DECLARE @sc_oid As decimal(18,0),
@rel_oid AS decimal(18,0),
@folder As nvarchar(255)
SET @rel_oid = (SELECT MAX(SRE_OID) FROM dbo.ITSM_SERVICE_RELATIONS)
SELECT @folder=REP_CODES_TEXT.RCT_NAME, @sc_oid=ITSM_SERVICECALLS.SER_OID
FROM ITSM_SERVICE_RELATIONS INNER JOIN
ITSM_SERVICECALLS ON ITSM_SERVICE_RELATIONS.SRE_SER_OID = ITSM_SERVICECALLS.SER_OID INNER JOIN
REP_CODES_TEXT ON ITSM_SERVICECALLS.SER_POO_OID = REP_CODES_TEXT.RCT_RCD_OID
WHERE (ITSM_SERVICE_RELATIONS.SRE_OID = @rel_oid) AND (REP_CODES_TEXT.RCT_LNG_OID = 1033)
UPDATE ITSM_SER_CUSTOM_FIELDS
SET scf_scnumber7 = isnull(scf_scnumber7,0) - 1
where SCF_SER_OID = @sc_oid
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

<HR>
Прибавляет Кол. Всех связанных Эл. в карточке обращения к полю scf_scnumber7

<HR>
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
-- =============================================
-- Author: Vasily Kamenev, Nenashev Grogory
-- ALTER date: 27.10.2009
-- =============================================

CREATE TRIGGER [dbo].[DBRule_setChangePandingToSCADD]
ON [dbo].[ITSM_SERVICE_RELATIONS]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
IF UPDATE(SRE_SER_OID) AND UPDATE(SRE_CHA_OID)
DECLARE @sc_oid As decimal(18,0),
@ch_oid As decimal(18,0),
@rel_oid AS decimal(18,0),
@target AS datetime,
@folder As nvarchar(255)
SET @rel_oid = (SELECT MAX(SRE_OID) FROM dbo.ITSM_SERVICE_RELATIONS)
SELECT @folder=REP_CODES_TEXT.RCT_NAME, @sc_oid=ITSM_SERVICECALLS.SER_OID
FROM ITSM_SERVICE_RELATIONS INNER JOIN
ITSM_SERVICECALLS ON ITSM_SERVICE_RELATIONS.SRE_SER_OID = ITSM_SERVICECALLS.SER_OID INNER JOIN
REP_CODES_TEXT ON ITSM_SERVICECALLS.SER_POO_OID = REP_CODES_TEXT.RCT_RCD_OID
WHERE (ITSM_SERVICE_RELATIONS.SRE_OID = @rel_oid) AND (REP_CODES_TEXT.RCT_LNG_OID = 1033)
UPDATE ITSM_SER_CUSTOM_FIELDS
SET scf_scnumber7 = isnull(scf_scnumber7,0) + 1
where SCF_SER_OID = @sc_oid

END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

<HR>
При связанном изменении поле факт. Окончания не пусто. То вычитает 1 из поля scf_scnumber7 в обращении.

<hr>
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

-- =============================================
-- Author: Nenashev Grogory
-- ALTER date: 27.10.2009
-- =============================================


CREATE TRIGGER [dbo].[CountOfCHANGES_UPDATE]

ON [dbo].[ITSM_CHANGES]
FOR UPDATE
AS
IF UPDATE (CHA_ACTUALFINISH)
BEGIN
IF NOT((SELECT CHA_OID FROM inserted) is NULL)
SET NOCOUNT ON
UPDATE ch
SET ch.scf_scnumber7 = isnull(ch.scf_scnumber7,0) - 1
FROM inserted ITSM_CHANGES
left JOIN ITSM_SERVICE_RELATIONS ON ITSM_CHANGES.cha_oid = ITSM_SERVICE_RELATIONS.SRE_CHA_OID
left join ITSM_SERVICECALLS ON ITSM_SERVICECALLS.SER_OID = ITSM_SERVICE_RELATIONS.SRE_SER_OID
left join ITSM_SER_CUSTOM_FIELDS AS ch ON ITSM_SERVICECALLS.SER_OID = ch.SCF_SER_OID
WHERE not(ITSM_CHANGES.CHA_ACTUALFINISH is null)

END

IF UPDATE (CHA_ACTUALFINISH)
BEGIN
IF NOT((SELECT CHA_OID FROM inserted) is NULL)
SET NOCOUNT ON
UPDATE ch
SET ch.scf_scnumber7 = isnull(ch.scf_scnumber7,0) + 1
FROM inserted ITSM_CHANGES
left JOIN ITSM_SERVICE_RELATIONS ON ITSM_CHANGES.cha_oid = ITSM_SERVICE_RELATIONS.SRE_CHA_OID

left join ITSM_SERVICECALLS ON ITSM_SERVICECALLS.SER_OID = ITSM_SERVICE_RELATIONS.SRE_SER_OID
left join ITSM_SER_CUSTOM_FIELDS AS ch ON ITSM_SERVICECALLS.SER_OID = ch.SCF_SER_OID
WHERE (ITSM_CHANGES.CHA_ACTUALFINISH is null)

END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

<HR>
Изменения
В поле CCU_CHANGENUMBER3 +1 если добавили связанное задание.

<HR>
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

-- =============================================
-- Author: Vassil Kamenev
-- ALTER date: 05.12.2007
-- Description: Count WOs and store in Change
-- =============================================
ALTER TRIGGER [dbo].[CountOfWorkorders_ADD]
ON [dbo].[ITSM_WORKORDERS]
FOR INSERT
AS
--IF UPDATE(WOR_CHA_OID)
BEGIN
set NOCOUNT ON;
UPDATE ch
SET ch.CCU_CHANGENUMBER3 = isnull(ch.CCU_CHANGENUMBER3,0) + 1
FROM inserted i
INNER JOIN ITSM_CHA_CUSTOM_FIELDS ch ON i.WOR_CHA_OID = ch.CCU_CHA_OID
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

<HR>
Изменения
В поле CCU_CHANGENUMBER3 -1 если удалили связанное задание.

<hr>
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

-- =============================================
-- Author: Vassil Kamenev
-- ALTER date: 05.12.2007
-- Description: Count WOs and store in Change
-- =============================================
ALTER TRIGGER [dbo].[CountOfWorkorders_DELETE]
ON [dbo].[ITSM_WORKORDERS]
FOR DELETE
AS

BEGIN
UPDATE ch
SET ch.CCU_CHANGENUMBER3 = isnull(ch.CCU_CHANGENUMBER3,0) - 1
FROM deleted d
INNER JOIN ITSM_CHA_CUSTOM_FIELDS ch ON d.WOR_CHA_OID = ch.CCU_CHA_OID
END


SET ANSI_NULLS ON

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

<HR>
Задания - Изменения
В поле CCU_CHANGENUMBER3 +1 или -1 если связанное задание в поле факт.окончания (WOR_ACTUALFINISH) не пусто.

<hr>
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

-- =============================================
-- Author: Vassil Kamenev
-- ALTER date: 05.12.2007
-- Description: +1 and -1 WO from Change
-- =============================================
ALTER TRIGGER [dbo].[CountOfWorkorders_UPDATE]
ON [dbo].[ITSM_WORKORDERS]
FOR UPDATE
AS
IF UPDATE(WOR_CHA_OID)
BEGIN
IF NOT((SELECT WOR_CHA_OID FROM inserted) is NULL)
BEGIN
UPDATE ch
SET ch.CCU_CHANGENUMBER3 = isnull(ch.CCU_CHANGENUMBER3,0) + 1
FROM inserted i
INNER JOIN ITSM_CHA_CUSTOM_FIELDS ch ON i.WOR_CHA_OID = ch.CCU_CHA_OID
WHERE not(i.WOR_CHA_OID is null)
END

UPDATE ch
SET ch.CCU_CHANGENUMBER3 = isnull(ch.CCU_CHANGENUMBER3,0) - 1
FROM deleted d
INNER JOIN ITSM_CHA_CUSTOM_FIELDS ch ON d.WOR_CHA_OID = ch.CCU_CHA_OID
WHERE not(d.WOR_CHA_OID is null)

END
IF UPDATE(WOR_ACTUALFINISH)
BEGIN
SET NOCOUNT ON

UPDATE ch
SET ch.CCU_CHANGENUMBER3 = isnull(ch.CCU_CHANGENUMBER3,0) - 1
FROM inserted i
INNER JOIN ITSM_CHA_CUSTOM_FIELDS AS ch ON i.WOR_CHA_OID = ch.CCU_CHA_OID
WHERE not(i.WOR_ACTUALFINISH is null)

-- Update +1 if WO set back in production
UPDATE ch
SET ch.CCU_CHANGENUMBER3 = isnull(ch.CCU_CHANGENUMBER3,0) + 1
FROM inserted i
INNER JOIN ITSM_CHA_CUSTOM_FIELDS AS ch ON i.WOR_CHA_OID = ch.CCU_CHA_OID
WHERE (i.WOR_ACTUALFINISH is null)

END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

<hr>
добрый день!
есть такая задача. Необходимо сделать так, что-бы SC закрывался автоматически при выполнении всех WO, которые созданны по данному SC. Может кто-то уже делал такие настройки, как вообще это можно реализовать,желательно само правило увидеть, может где-то почитать про это можно...
Страницы: 1 2 След.

Сегодня были (гостей: 19, пользователей: 0, из них скрытых: 0)