SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE OR ALTER VIEW [dbo].[VWREC_PROFILE]
AS
SELECT TOP (100) PERCENT
            [CFG_PROFILE].[GUID]            as 'PROFILE_GUID',
            [CFG_PROFILE].[ACTIVE]          as 'ACTIVE',
            [CFG_PROFILE].[TYPE_ID]         as 'TYPE_ID',
            CASE
                WHEN [CFG_PROFILE].[TYPE_ID] = 1 THEN 'HTTP'
                WHEN [CFG_PROFILE].[TYPE_ID] = 2 THEN 'HTTPS'
                ELSE 'INVALID'
            END                             as 'TYPE',
            [CFG_PROFILE].[MANDANTOR]       as 'MANDANTOR',
            [CFG_PROFILE].[PROFILE_NAME]    as 'PROFILE_NAME',
            [CFG_PROFILE].[DESCRIPTION]     as 'DESCRIPTION',
            [CFG_PROFILE].[LOG_LEVEL_ID]    as 'LOG_LEVEL_ID',
            CASE
                WHEN [CFG_PROFILE].[LOG_LEVEL_ID] = 0 THEN 'TRACE'
                WHEN [CFG_PROFILE].[LOG_LEVEL_ID] = 1 THEN 'DEBUG'
                WHEN [CFG_PROFILE].[LOG_LEVEL_ID] = 2 THEN 'INFO'
                WHEN [CFG_PROFILE].[LOG_LEVEL_ID] = 3 THEN 'WARN'
                WHEN [CFG_PROFILE].[LOG_LEVEL_ID] = 4 THEN 'ERROR'
                WHEN [CFG_PROFILE].[LOG_LEVEL_ID] = 5 THEN 'CRITICAL'
                WHEN [CFG_PROFILE].[LOG_LEVEL_ID] = 6 THEN 'NONE'
                ELSE 'INVALID'
            END                             as 'LOG_LEVEL',
            [CFG_PROFILE].[LANGUAGE_ID]     as 'LANGUAGE_ID',
            CASE
                WHEN [CFG_PROFILE].[LANGUAGE_ID] = 1031 THEN 'de-de'
                WHEN [CFG_PROFILE].[LANGUAGE_ID] = 1033 THEN 'en-us'
                ELSE 'INVALID'
            END                             as 'LANGUAGE',
            [CFG_PROFILE].[ADDED_WHO]       as 'ADDED_WHO',
            [CFG_PROFILE].[ADDED_WHEN]      as 'ADDED_WHEN',
            [CFG_PROFILE].[CHANGED_WHO]     as 'CHANGED_WHO',
            [CFG_PROFILE].[CHANGED_WHEN]    as 'CHANGED_WHEN',
            [RUN_PROFILE].[FIRST_RUN]       as 'FIRST_RUN',
            [RUN_PROFILE].[LAST_RUN]        as 'LAST_RUN',
            [RUN_PROFILE].[LAST_RESULT]     as 'LAST_RESULT'

FROM        [TBREC_CFG_PROFILE] as [CFG_PROFILE] WITH (NOLOCK)
LEFT JOIN   [TBREC_RUN_PROFILE] as [RUN_PROFILE] WITH (NOLOCK) ON [CFG_PROFILE].[GUID] = [RUN_PROFILE].[PROFILE_ID]

ORDER BY    [CFG_PROFILE].[GUID] ASC;

GO

--------------------------------------------------------------------------------------

CREATE OR ALTER TRIGGER [dbo].[TRREC_VWREC_PROFILE_IO_INS]
ON [dbo].[VWREC_PROFILE]
INSTEAD OF INSERT
AS
BEGIN
	SET NOCOUNT ON;
	THROW 51000, 'DML is not allowed on view [dbo].[VWREC_PROFILE]. Use these Procedure instead: [PRREC_INSERT_OBJECT]', 1;
END;
GO

--------------------------------------------------------------------------------------

CREATE OR ALTER TRIGGER [dbo].[TRREC_VWREC_PROFILE_IO_UPD]
ON [dbo].[VWREC_PROFILE]
INSTEAD OF UPDATE
AS
BEGIN
	SET NOCOUNT ON;
	THROW 52000, 'DML is not allowed on view [dbo].[VWREC_PROFILE]. Use these Procedure instead: [PRREC_UPDATE_OBJECT]', 1;
END;
GO

--------------------------------------------------------------------------------------

CREATE OR ALTER TRIGGER [dbo].[TRREC_VWREC_PROFILE_IO_DEL]
ON [dbo].[VWREC_PROFILE]
INSTEAD OF DELETE
AS
BEGIN
	SET NOCOUNT ON;
	THROW 53000, 'DML is not allowed on view [dbo].[VWREC_PROFILE]. Use these Procedure instead: [PRREC_DELETE_OBJECT]', 1;
END;
GO