topic SQL Trigger for Auditing in SDE in Geodatabase Questions

SQL Trigger for Auditing in SDE

AdityarajChavada — Tue, 12 Jun 2018 06:04:58 GMT

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.

Re: SQL Trigger for Auditing in SDE

Asrujit_SenGupta — Tue, 12 Jun 2018 06:22:35 GMT

You can setup the trigger on the D-table for that feature class. However, do note that the D-table is updated in 2 scenarios:

1. when a feature is actually deleted, (which you need)

2. when an existing feature is updated (which you don't need)

delta tables

So this might not give you the exact info you want to collect.

Re: SQL Trigger for Auditing in SDE

AdityarajChavada — Tue, 12 Jun 2018 09:50:56 GMT

Thanks for your reply! If I setup the trigger on the D-table, I am not sure I can get the Shape information unless I am make a complicated sql join with Versioning system table.

Re: SQL Trigger for Auditing in SDE

vijaybadugu — Wed, 07 Aug 2024 14:10:13 GMT

I am updating acres field on A table for add/update. Do I need to use _evw view for update query or direct version table like update a.485 set acres =2.44 where OBJECTID in (Select OBJECTID from Inserted) or update [tablename]_evw set acres =2.44 where OBJECTID in (Select OBJECTID from Inserted) ?

after editing either of these methods, it inserts 0's in global id field like 000000-00000-0000-00000

Re: SQL Trigger for Auditing in SDE

Asrujit_SenGupta — Fri, 16 Aug 2024 03:36:40 GMT

Edits on traditional versioned data performed using SQL should only be made through versioned views.

An overview of editing versioned data using SQL

Re: SQL Trigger for Auditing in SDE

Bud — Fri, 16 Aug 2024 04:51:09 GMT

Edit Log to capture Insert, Update, and Deletes via ArcGIS Arcade attribute rule

https://community.esri.com/t5/arcgis-pro-questions/edit-log-to-capture-insert-update-and-deletes-via/m-p/1513089/highlight/true#M86261