topic Re: How can I receive a notification of when a version has been posted? in Geodatabase Questions

How can I receive a notification of when a version has been posted?

JasonHoward — Wed, 23 Aug 2017 22:32:28 GMT

Hi everyone,

I have a project coming up in which multiple editors will be working on a versioned geodatabase feature class. I would like to receive a ping from the database server when a user posts his or her version to a QAQC version so that I can check the editors' work before accepting it and posting the QAQC version to Default. I thought that a trigger that fires when some table is updated was the right track, but I'm just not sure which table the trigger should be reacting to. Does anyone have any experience in this area?

I'm working in SQL Server. Geodatabase and desktops are at 10.5.1.

Thanks!

Re: How can I receive a notification of when a version has been posted?

Asrujit_SenGupta — Thu, 24 Aug 2017 09:34:00 GMT

You can check the SDE_Versions table from the database end.

System tables of a geodatabase in SQL Server—Help | ArcGIS Desktop

The "state_id" value should change whenever your Editors will Post to QAQC version, so you may somehow use that to get a notification.

Usually you can manually check the 'Date Modified' part in the Geodatabase Administration window in ArcCatalog:

But this Modified date is NOT a part of the Versions table, so this cannot be used from the database end to keep a check.

Re: How can I receive a notification of when a version has been posted?

JasonHoward — Thu, 24 Aug 2017 19:44:02 GMT

This gets me most of the way, but I still need a way to catch only those posts going to the QAQC version....

CREATE TRIGGER dbo.NotifyJason
ON dbo.sde_versions
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;

IF (UPDATE(state_id))

IF (SELECT count(*) AS record_count FROM inserted i WHERE name = 'QAQC') > 0

INSERT INTO dbo.TEST (name, owner, state_id, parent_name, post_time)
SELECT i.name, i.owner, i.state_id, i.parent_name, GETDATE()
FROM inserted i
--then email me...

END

The table the output gets written into, dbo.TEST, cannot be registered with the geodatabase. The presence of the OBJECTID field on a geodatabase table was problematic for what I was doing here so I just created a table in the database to hold those Post records.