topic Issue with SQL Trigger running After Delete in Geodatabase Questions https://community.esri.com/t5/geodatabase-questions/issue-with-sql-trigger-running-after-delete/m-p/876134#M6517 <HTML><HEAD></HEAD><BODY><P>I've created a set of triggers for our street centerline and address point feature classes.&nbsp; The first trigger maintains a perpetual unique key, the second copies inserts or updates to a history table, and the third trigger runs after delete and copies deleted records to the same history table.</P><P></P><P>The first two triggers are working fine.&nbsp; The third trigger (deletes)&nbsp;works just fine on address points, and on a test copy of street centerlines, but is failing on our production street centerlines with the error:&nbsp; "Unable to save edits.&nbsp; Underlying DBMS error [HY000:[Microsoft][SQL Server Native Client 11.0]Connection is busy with results for another command]".&nbsp; All three feature classes are registered as versioned with edits moving to base.</P><P></P><P>Our environment is ArcGIS 10.3.1 running on top of SQL Server 2008R2.</P><P></P><P>I'm sure someone will point out that I could take advantage of the SDE Archiving functionality, but I decided against that approach because I like moving edits directly to base.&nbsp; I've designed a couple of QA/QC views that&nbsp;are more useful if editors can immediately see&nbsp;their changes.&nbsp; There are always pros and cons, right?&nbsp; <span class="lia-unicode-emoji" title=":slightly_smiling_face:">🙂</span></P><P></P><P>Has anyone had this same error?&nbsp; Any ideas for troubleshooting?</P></BODY></HTML> Tue, 21 Feb 2017 22:04:25 GMT ShawnaErnst 2017-02-21T22:04:25Z Issue with SQL Trigger running After Delete https://community.esri.com/t5/geodatabase-questions/issue-with-sql-trigger-running-after-delete/m-p/876134#M6517 <HTML><HEAD></HEAD><BODY><P>I've created a set of triggers for our street centerline and address point feature classes.&nbsp; The first trigger maintains a perpetual unique key, the second copies inserts or updates to a history table, and the third trigger runs after delete and copies deleted records to the same history table.</P><P></P><P>The first two triggers are working fine.&nbsp; The third trigger (deletes)&nbsp;works just fine on address points, and on a test copy of street centerlines, but is failing on our production street centerlines with the error:&nbsp; "Unable to save edits.&nbsp; Underlying DBMS error [HY000:[Microsoft][SQL Server Native Client 11.0]Connection is busy with results for another command]".&nbsp; All three feature classes are registered as versioned with edits moving to base.</P><P></P><P>Our environment is ArcGIS 10.3.1 running on top of SQL Server 2008R2.</P><P></P><P>I'm sure someone will point out that I could take advantage of the SDE Archiving functionality, but I decided against that approach because I like moving edits directly to base.&nbsp; I've designed a couple of QA/QC views that&nbsp;are more useful if editors can immediately see&nbsp;their changes.&nbsp; There are always pros and cons, right?&nbsp; <span class="lia-unicode-emoji" title=":slightly_smiling_face:">🙂</span></P><P></P><P>Has anyone had this same error?&nbsp; Any ideas for troubleshooting?</P></BODY></HTML> Tue, 21 Feb 2017 22:04:25 GMT https://community.esri.com/t5/geodatabase-questions/issue-with-sql-trigger-running-after-delete/m-p/876134#M6517 ShawnaErnst 2017-02-21T22:04:25Z Re: Issue with SQL Trigger running After Delete https://community.esri.com/t5/geodatabase-questions/issue-with-sql-trigger-running-after-delete/m-p/876135#M6518 <HTML><HEAD></HEAD><BODY><P>You have a violation of trigger nesting levels. Wrap up the separate triggers for (inserts/updates) and (deletes) into a single trigger. You'd be better off using Change Data Capture though.&nbsp;</P></BODY></HTML> Wed, 29 Nov 2017 19:10:34 GMT https://community.esri.com/t5/geodatabase-questions/issue-with-sql-trigger-running-after-delete/m-p/876135#M6518 ThomasColson 2017-11-29T19:10:34Z