AnsweredAssumed Answered

SQL Trigger for Auditing in SDE

Question asked by Adityaraj on Jun 11, 2018
Latest reply on Jun 12, 2018 by Adityaraj

We are using 10.4 Geodatabase matching with ArcGIS release. Our goal is to setup a trigger on database side that will create a record for every time a record in deleted from the Default Version in SDE for Audit Purpose with following information:

ObjectID, Shape, Deleted By, Deleted On.

So, I created a table – Audit_Log on SQL side. Then, I setup a Trigger after Delete on the Base Table using the following SQL code:

CREATE TRIGGER [dbo].[Audit_Log] ON [dbo].[Table_A]

AFTER DELETE

AS

BEGIN

          

  INSERT INTO [dbo].[ Audit_Log]

  ([OID],[SHAPE],[DELETED_BY],[DELETED_ON])

  SELECT OBJECTID,SHAPE, SUSER_NAME(), GETDATE()

  FROM deleted

END

The above trigger is not running because I believe that all the data in Geodatabase is registered as versioned with edits not moving to base. I understand that with this option (edits not moving to base) the trigger will not fire up till the Compression is ran.

Is there any I can setup a trigger with the current setup to get the information required for audit purpose? Am I asking for a lot here?

We are aware of the Archiving Functionality in SDE. Unfortunately, it does not record system user who deletes the record. It only stamps the time when the feature was moved to Archived table.

Any help or guidance on the subject matter will be appreciated.   

Outcomes