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

690
2
08-23-2017 03:32 PM
JasonHoward
New Contributor II

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!

0 Kudos
2 Replies
Asrujit_SenGupta
MVP Regular Contributor

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.

JasonHoward
New Contributor II

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.

0 Kudos