SharePoint Connoisseur: Workaround for SharePoint 2013 access app no audit history issue – use data micro to track field change to an audit list

SharePoint 2013 Access App features a new application model. By using Microsoft SharePoint 2013 to host the front end of the app and Microsoft SQL Server 2012 as its data storage technology, SharePoint 2013 Access App has been adopted quickly by power users who has database development background. Jeff Conrad from Microsoft mentioned Access 2013 is to change the world one app at a time."]

Since Access App is a database application as we discussed in previous blog, it does not support some of the OoB functions. One of the key functions not supported is the audit history. If users modified or delete an entry from Access App list (table), there is no audit history. In this blog, I would like to provide a workaround learned from Microsoft Access App expertise Jeff Conrad to track the audit history.

I’ve using the similar Access Apps “HarryDevApp2” with two tables “Employee” and “Rating” as in previous blog. You could find the database name like “db_af65a2a2_8e70_47d6_994b_207ff0351650” and the two tables named “Access. Employee” and “Access.Rating” are created inside that database. Since employee rating is extremely import and we like to track the change history. Here is the procedure learned from Jeff Conrad.

Create a new table named AuditHistory with old rating, new rating, and modified time
Create a “On Update” event and add the following data macro

After the macro saved, when you change the rating of one record, the change will be recorded into the AuditHistory table. Of cause, you can capture other information to make it more usable.

The design behind “On Update” event is that it creates a trigger on the database side to capture the changed data and insert to AuditHistory table. You can see the trigger in the database table.

The trigger created by the macro on the database side is listed below.

USE [db_af65a2a2_8e70_47d6_994b_207ff0351650]

/****** Object: Trigger [Access].[_dm_Employee_AFTER_UPDATE] Script Date: 3/13/2014 1:49:17 PM ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

ALTER TRIGGER [Access].[_dm_Employee_AFTER_UPDATE]

ON [Access].[Employee]

WITH EXECUTE AS 'AccessWriter'

AFTER UPDATE

BEGIN

SET NOCOUNT ON

SET XACT_ABORT OFF

DECLARE @_dm_actionRunning NVARCHAR(128)

DECLARE @_dm_macroRunning NVARCHAR(128)

DECLARE @_dm_traceOn BIT = 0

IF AccessSystem.ApplicationPropertiesSelect('DataMacroTracing') IS NOT NULL

SET @_dm_traceOn = 1

DECLARE @_dm_lastLoggingID INT

IF @_dm_traceOn > 0

SELECT

@_dm_lastLoggingID = COALESCE(Max(ID), 0)

FROM

[Access].[Trace]

DECLARE @_dm_savePoint as VARCHAR(36)

SET @_dm_savePoint = CONVERT(VARCHAR(36), NEWID())

DECLARE @_dm_initialTranCount INT

SET @_dm_initialTranCount = @@TRANCOUNT

IF @_dm_initialTranCount > 0

SAVE TRAN @_dm_savePoint

ELSE

BEGIN TRAN

IF OBJECT_ID('tempdb..#Context') IS NOT NULL

BEGIN

DECLARE @_dm_isTopLevel BIT

SELECT @_dm_isTopLevel = isTopLevel

FROM #Context

WHERE ID = 1

IF @_dm_isTopLevel = 1

BEGIN

UPDATE #Context

SET isTopLevel = 0

WHERE ID = 1

SET @_dm_initialTranCount = 0

END

SET @_dm_macroRunning = N'Employee:On Update'

BEGIN TRY

DECLARE @varRatingOld Int

DECLARE @varRatingNew Int

DECLARE @_dm_cancelRecordChange Bit

DECLARE @_dm_temp1 Int

DECLARE _dm_cur1 CURSOR LOCAL STATIC FOR

SELECT

[Rating]

FROM

[DELETED]

OPEN _dm_cur1

DECLARE @_dm_temp2 Int

DECLARE _dm_cur2 CURSOR LOCAL STATIC FOR

SELECT

[Rating]

FROM

[INSERTED]

OPEN _dm_cur2

FETCH NEXT FROM

_dm_cur1

INTO

@_dm_temp1

FETCH NEXT FROM

_dm_cur2

INTO

@_dm_temp2

WHILE @@FETCH_STATUS = 0

BEGIN

/* Get the old and new values of the rating and place them in variables. */

SET @_dm_actionRunning = N'SetLocalVar'

SET @varRatingOld = @_dm_temp1

IF @_dm_traceOn > 0

EXEC [AccessSystem].[LogActionTrace] @_dm_macroRunning, N'SetLocalVar', N'varRatingOld', @varRatingOld, N'', N''

SET @_dm_actionRunning = N'SetLocalVar'

SET @varRatingNew = @_dm_temp2

IF @_dm_traceOn > 0

EXEC [AccessSystem].[LogActionTrace] @_dm_macroRunning, N'SetLocalVar', N'varRatingNew', @varRatingNew, N'', N''

/* Create a record in the AuditHistory table and pass in the local variables to the appropriate fields. */

DECLARE @_dm_temp3 NVarChar(220)

DECLARE @_dm_temp4 Decimal(28,6)

DECLARE @_dm_temp5 Decimal(28,6)

DECLARE @_dm_temp6 Date

SET @_dm_temp4 = 0.0

SET @_dm_temp5 = 0.0

SET @_dm_cancelRecordChange = 0

IF @_dm_traceOn > 0

EXEC [AccessSystem].[LogActionTrace] @_dm_macroRunning, N'CreateRecord', N'', N'', N'', N''

SET @_dm_actionRunning = N'SetField'

SET @_dm_temp3 = @_dm_temp3

IF @_dm_traceOn > 0

EXEC [AccessSystem].[LogActionTrace] @_dm_macroRunning, N'SetField', N'AuditHistory.Employee', @_dm_temp3, N'', N''

SET @_dm_actionRunning = N'SetField'

SET @_dm_temp4 = @varRatingOld

IF @_dm_traceOn > 0

EXEC [AccessSystem].[LogActionTrace] @_dm_macroRunning, N'SetField', N'AuditHistory.OldRating', @_dm_temp4, N'', N''

SET @_dm_actionRunning = N'SetField'

SET @_dm_temp5 = @varRatingNew

IF @_dm_traceOn > 0

EXEC [AccessSystem].[LogActionTrace] @_dm_macroRunning, N'SetField', N'AuditHistory.NewRating', @_dm_temp5, N'', N''

SET @_dm_actionRunning = N'SetField'

SET @_dm_temp6 = AccessSystem.NOW()

IF @_dm_traceOn > 0

EXEC [AccessSystem].[LogActionTrace] @_dm_macroRunning, N'SetField', N'AuditHistory.LastEditedTime', @_dm_temp6, N'', N''

SET @_dm_actionRunning = N'CreateRecord'

IF @_dm_cancelRecordChange = 0

BEGIN

INSERT INTO [AuditHistory] (

[Employee],

[OldRating],

[NewRating],

[LastEditedTime])

VALUES (

@_dm_temp3,

@_dm_temp4,

@_dm_temp5,

@_dm_temp6)

END

FETCH NEXT FROM

_dm_cur1

INTO

@_dm_temp1

FETCH NEXT FROM

_dm_cur2

INTO

@_dm_temp2

END

CLOSE _dm_cur1

DEALLOCATE _dm_cur1

CLOSE _dm_cur2

DEALLOCATE _dm_cur2

END TRY

BEGIN CATCH

DECLARE @_dm_sqlException NVARCHAR(128) = N'SQL exception'

DECLARE @_dm_errorNumber INT = ERROR_NUMBER()

DECLARE @_dm_errorMessage NVARCHAR(4000) = ERROR_MESSAGE()

IF @_dm_TraceOn > 0

BEGIN

/* Before rollback happens, cache the tracing info so that we can refill later */

DECLARE @tracingCache TABLE(

[ID] INT IDENTITY PRIMARY KEY,

[MacroName] nvarchar(128),

[ActionName] nvarchar(128),

[Operand] nvarchar(4000),

[Output] nvarchar(max),

[TargetRow] nvarchar(4000),

[Timestamp] datetime2(3),

[RuntimeErrorMessage] nvarchar(4000))

INSERT INTO @tracingCache

SELECT

[MacroName],

[ActionName],

[Operand],

[Output],

[TargetRow],

[Timestamp],

[RuntimeErrorMessage]

FROM

[Access].[Trace]

WHERE

[ID] > @_dm_lastLoggingID

END;

IF @_dm_initialTranCount > 0

BEGIN

IF XACT_STATE() <> -1

ROLLBACK TRAN @_dm_savepoint

END

ELSE

ROLLBACK TRAN

IF @_dm_TraceOn > 0 AND XACT_STATE() <> -1

BEGIN

/* After rollback happens, refill the Tracing table with the cached info */

INSERT INTO [Access].[Trace](

[MacroName],

[ActionName],

[Operand],

[Output],

[TargetRow],

[Timestamp],

[RuntimeErrorMessage])

SELECT

[MacroName],

[ActionName],

[Operand],

[Output],

[TargetRow],

[Timestamp],

[RuntimeErrorMessage]

FROM

@tracingCache

/* By default, RAISERROR raises an error message with an error number of 50000.

If the caught error's ID is 50000, then it is a user raised error

Else the caught error is a SQL exception (the Error ID won't be 50000 for SQL exception) */

IF @_dm_errorNumber <> 50000

/* Log the current running macro/action with the SQL exception error */

EXEC [AccessSystem].[LogActionTrace] @_dm_macroRunning, @_dm_actionRunning, '', '', '', @_dm_sqlException;

END;

THROW;

END CATCH

END

Now you understand the Access App is just a database UI and you can use all your database development skills to empower your access apps.

SharePoint Connoisseur

Thursday, March 13, 2014

Workaround for SharePoint 2013 access app no audit history issue – use data micro to track field change to an audit list

1 comment: