230 lines
8.2 KiB
Transact-SQL
230 lines
8.2 KiB
Transact-SQL
DROP PROCEDURE IF EXISTS auditLog.usp_AuditProcessing
|
|
GO
|
|
|
|
CREATE PROCEDURE auditLog.usp_AuditProcessing
|
|
AS
|
|
BEGIN
|
|
DECLARE @messageType varchar(100)
|
|
,@dialog uniqueidentifier
|
|
,@messageBody nvarchar(max)
|
|
,@tableName nvarchar(80)
|
|
,@conversationHandle UNIQUEIDENTIFIER
|
|
,@messageTypeName sysname;
|
|
|
|
CREATE TABLE #linkTable (
|
|
id int
|
|
,objectId int
|
|
,intezmenyId int
|
|
,tanevId int
|
|
)
|
|
|
|
|
|
WHILE (1 = 1) BEGIN
|
|
BEGIN TRANSACTION;
|
|
WAITFOR
|
|
(RECEIVE TOP(1)
|
|
@conversationHandle = conversation_handle,
|
|
@messageBody = message_body,
|
|
@messageTypeName = message_type_name
|
|
FROM auditlog.AuditQueue
|
|
), TIMEOUT 1000;
|
|
|
|
IF (@@ROWCOUNT = 0)
|
|
BEGIN
|
|
ROLLBACK TRANSACTION;
|
|
BREAK;
|
|
END
|
|
|
|
BEGIN TRY
|
|
|
|
DECLARE
|
|
@action char(1)
|
|
,@alterationTime datetime
|
|
|
|
-- Handle the Message
|
|
IF @messageTypeName = N'AuditMessage' BEGIN
|
|
IF ISJSON(@messageBody) = 1 BEGIN
|
|
|
|
TRUNCATE TABLE #linkTable
|
|
|
|
SELECT
|
|
@tableName = name
|
|
,@alterationTime = alterationTime
|
|
FROM OPENJSON (@messageBody) WITH (
|
|
name nvarchar(50) '$.name'
|
|
,alterationTime datetime '$.alterationTime'
|
|
)
|
|
|
|
SET @action =
|
|
CASE
|
|
WHEN NOT EXISTS (SELECT 1 FROM OPENJSON (@messageBody, '$.deleted')) THEN 'I'
|
|
WHEN NOT EXISTS (SELECT 1 FROM OPENJSON (@messageBody, '$.inserted')) THEN 'D'
|
|
ELSE 'U'
|
|
END
|
|
|
|
IF @action = 'I' BEGIN -- INSERT
|
|
INSERT INTO dbo.T_ENTITYHISTORY (C_ALTERATIONDATE, C_ENTITYID, C_ENTITYNAME, C_REASON, C_FELHASZNALOID, C_INTEZMENYID, C_TANEVID)
|
|
OUTPUT INSERTED.ID, INSERTED.C_ENTITYID, INSERTED.C_INTEZMENYID, INSERTED.C_TANEVID INTO #LinkTable
|
|
SELECT
|
|
@alterationTime AS C_ALTERATIONDATE
|
|
,JSON_Value (c.value, '$.ID') AS C_ENTITYID
|
|
,@tableName AS C_ENTITYNAME
|
|
,'New' AS C_REASON
|
|
,JSON_Value (c.value, '$.CREATOR') as C_FELHASZNALOID
|
|
,JSON_Value (c.value, '$.C_INTEZMENYID') as C_INTEZMENYID
|
|
,JSON_Value (c.value, '$.C_TANEVID') as C_TANEVID
|
|
FROM OPENJSON (@messageBody, '$.inserted') as c
|
|
|
|
|
|
INSERT INTO dbo.T_ENTITYATTRIBUTEHISTORY (C_CURRENTVALUE, C_ORIGINALVALUE, C_PROPERTYNAME, C_ENTITYHISTORYID)
|
|
SELECT val, NULL, col, lk.id
|
|
FROM #linkTable lk
|
|
INNER JOIN (
|
|
SELECT
|
|
JSON_Value (c.value, '$.ID') as objectId
|
|
,JSON_Value (c.value, '$.C_INTEZMENYID') as intezmenyId
|
|
,JSON_Value (c.value, '$.C_TANEVID') as tanevId
|
|
,p.[key] AS col
|
|
,p.[Value] AS val
|
|
FROM OPENJSON (@messageBody, '$.inserted') as c
|
|
CROSS APPLY OPENJSON (c.value, '$.col') as p
|
|
) i ON i.objectId = lk.objectId AND i.intezmenyId = lk.intezmenyId AND i.tanevId = lk.tanevId
|
|
END
|
|
|
|
IF @action = 'D' BEGIN -- DELETED
|
|
INSERT INTO dbo.T_ENTITYHISTORY (C_ALTERATIONDATE, C_ENTITYID, C_ENTITYNAME, C_REASON, C_FELHASZNALOID, C_INTEZMENYID, C_TANEVID)
|
|
OUTPUT INSERTED.ID, INSERTED.C_ENTITYID, INSERTED.C_INTEZMENYID, INSERTED.C_TANEVID INTO #LinkTable
|
|
SELECT
|
|
@alterationTime AS C_ALTERATIONDATE
|
|
,JSON_Value (c.value, '$.ID') AS C_ENTITYID
|
|
,@tableName AS C_ENTITYNAME
|
|
,'Removed' AS C_REASON
|
|
,JSON_Value (c.value, '$.CREATOR') as C_FELHASZNALOID
|
|
,JSON_Value (c.value, '$.C_INTEZMENYID') as C_INTEZMENYID
|
|
,JSON_Value (c.value, '$.C_TANEVID') as C_TANEVID
|
|
FROM OPENJSON (@messageBody, '$.deleted') as c
|
|
|
|
|
|
INSERT INTO dbo.T_ENTITYATTRIBUTEHISTORY (C_CURRENTVALUE, C_ORIGINALVALUE, C_PROPERTYNAME, C_ENTITYHISTORYID)
|
|
SELECT NULL, val, col, lk.id
|
|
FROM #linkTable lk
|
|
INNER JOIN (
|
|
SELECT
|
|
JSON_Value (c.value, '$.ID') as objectId
|
|
,JSON_Value (c.value, '$.C_INTEZMENYID') as intezmenyId
|
|
,JSON_Value (c.value, '$.C_TANEVID') as tanevId
|
|
,p.[key] AS col
|
|
,p.[Value] AS val
|
|
FROM OPENJSON (@messageBody, '$.deleted') as c
|
|
CROSS APPLY OPENJSON (c.value, '$.col') as p
|
|
) d ON d.objectId = lk.objectId AND d.intezmenyId = lk.intezmenyId AND d.tanevId = lk.tanevId
|
|
END
|
|
|
|
IF @action = 'U' BEGIN -- UPDATE
|
|
INSERT INTO dbo.T_ENTITYHISTORY (C_ALTERATIONDATE, C_ENTITYID, C_ENTITYNAME, C_REASON, C_FELHASZNALOID, C_INTEZMENYID, C_TANEVID)
|
|
OUTPUT INSERTED.ID, INSERTED.C_ENTITYID, INSERTED.C_INTEZMENYID, INSERTED.C_TANEVID INTO #LinkTable
|
|
SELECT
|
|
@alterationTime AS C_ALTERATIONDATE
|
|
,JSON_Value (c.value, '$.ID') AS C_ENTITYID
|
|
,@tableName AS C_ENTITYNAME
|
|
,'Modified' AS C_REASON
|
|
,JSON_Value (c.value, '$.MODIFIER') as C_FELHASZNALOID
|
|
,JSON_Value (c.value, '$.C_INTEZMENYID') as C_INTEZMENYID
|
|
,JSON_Value (c.value, '$.C_TANEVID') as C_TANEVID
|
|
FROM OPENJSON (@messageBody, '$.inserted') as c
|
|
|
|
INSERT INTO dbo.T_ENTITYATTRIBUTEHISTORY (C_CURRENTVALUE, C_ORIGINALVALUE, C_PROPERTYNAME, C_ENTITYHISTORYID)
|
|
SELECT NULLIF(i.val, N'#NULL#'), NULLIF(d.val, N'#NULL#'), d.col, lk.id
|
|
FROM #LinkTable lk
|
|
INNER JOIN (
|
|
SELECT
|
|
JSON_Value (c.value, '$.ID') as objectId
|
|
,JSON_Value (c.value, '$.C_INTEZMENYID') as intezmenyId
|
|
,JSON_Value (c.value, '$.C_TANEVID') as tanevId
|
|
,JSON_Value (c.value, '$.CREATOR') as creator
|
|
,JSON_Value (c.value, '$.MODIFIER') as modifier
|
|
,p.[key] AS col
|
|
,ISNULL(p.[Value], N'#NULL#') AS val
|
|
FROM OPENJSON (@messageBody, '$.deleted') as c
|
|
CROSS APPLY OPENJSON (c.value, '$.col') as p
|
|
) d ON d.objectId = lk.objectId AND d.intezmenyId = lk.intezmenyId AND d.tanevId = lk.tanevId
|
|
INNER JOIN (
|
|
SELECT
|
|
JSON_Value (c.value, '$.ID') as objectId
|
|
,JSON_Value (c.value, '$.C_INTEZMENYID') as intezmenyId
|
|
,JSON_Value (c.value, '$.C_TANEVID') as tanevId
|
|
,JSON_Value (c.value, '$.CREATOR') as creator
|
|
,JSON_Value (c.value, '$.MODIFIER') as modifier
|
|
,p.[key] AS col
|
|
,ISNULL(p.[Value], N'#NULL#') AS val
|
|
FROM OPENJSON (@messageBody, '$.inserted') as c
|
|
CROSS APPLY OPENJSON (c.value, '$.col') as p
|
|
) i ON d.objectId = i.objectId AND d.intezmenyId = i.intezmenyId AND d.tanevId = i.tanevId AND d.col = i.col AND i.val <> d.val
|
|
|
|
END
|
|
END
|
|
END
|
|
ELSE IF @messageTypeName = 'EndOfStream' BEGIN
|
|
-- initiator is signaling end of message stream: end the dialog
|
|
END CONVERSATION @conversationHandle;
|
|
END
|
|
|
|
-- If end dialog message, end the dialog
|
|
ELSE IF @messageTypeName = N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog' BEGIN
|
|
END CONVERSATION @conversationHandle;
|
|
END
|
|
|
|
ELSE IF @messageTypeName = N'DEFAULT' BEGIN
|
|
END CONVERSATION @conversationHandle;
|
|
END
|
|
|
|
-- If error message, log and end conversation
|
|
ELSE IF @messageTypeName = N'http://schemas.microsoft.com/SQL/ServiceBroker/Error' BEGIN
|
|
DECLARE @error INT;
|
|
DECLARE @description NVARCHAR(4000);
|
|
-- Pull the error code and description from the doc
|
|
WITH XMLNAMESPACES ('http://schemas.microsoft.com/SQL/ServiceBroker/Error' AS ssb)
|
|
SELECT
|
|
@error = CAST(@messageBody AS xml).value('(//ssb:Error/ssb:Code)[1]', 'INT'),
|
|
@description = CAST(@messageBody AS xml).value('(//ssb:Error/ssb:Description)[1]', 'NVARCHAR(4000)');
|
|
|
|
|
|
--INSERT INTO [dbo].[ErrorLog] (ConversationHandle, message_body, message_type_name)
|
|
--SELECT @conversationHandle, CAST(@messageBody AS nvarchar(max)), @messageTypeName;
|
|
|
|
RAISERROR(N'Received error Code:%i Description:"%s"', 16, 1, @error, @description) WITH LOG;
|
|
|
|
-- Now that we handled the error logging cleanup
|
|
END CONVERSATION @conversationHandle;
|
|
END
|
|
|
|
-- If everything succeeded correctly commit the transaction
|
|
COMMIT TRANSACTION;
|
|
|
|
END TRY
|
|
|
|
-- If we have any error get caught we want to throw the error
|
|
-- and then rollback the transaction to put the message on the queue
|
|
BEGIN CATCH
|
|
IF (ERROR_NUMBER() = 1205)
|
|
BEGIN
|
|
ROLLBACK TRANSACTION;
|
|
EXEC [auditlog].[uspLogError];
|
|
CONTINUE;
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
ROLLBACK TRANSACTION --ReceiveSavePoint;
|
|
|
|
EXEC [auditlog].[uspLogError];
|
|
|
|
INSERT INTO [auditlog].[AuditServiceFailedItems]
|
|
([conversation_handle], [message_body])
|
|
VALUES (@conversationHandle, @messageBody);
|
|
THROW;
|
|
END
|
|
END CATCH
|
|
END
|
|
END
|
|
GO
|