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



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.


  1. Create a new table named AuditHistory with old rating, new rating, and modified time
  2. 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]

GO

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

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER TRIGGER [Access].[_dm_Employee_AFTER_UPDATE]

ON [Access].[Employee]

WITH EXECUTE AS 'AccessWriter'

AFTER UPDATE

AS

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

       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.

No comments:

Post a Comment