Ведет подсчет кол. Заданий из вкладки Обращения. Прикручено к полю 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
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