all posts

The referenced entity insert was modified during ddl execution

Published to Blog on 5 Sep 2018

I ran into an issue recently when trying to create triggers for a table on a SQL Server database using node and the tedious library. I was using a promise to generate and execute the DDL to create each trigger and using Promise.all() to wait until they were all finished before moving on to the next step. The result was that rather than creating one large sql script and executing it step by step I was creating many small sql scripts and executing them asynchronously. It had all been working fine for a while but then I started getting errors that some of the triggers could not be created along with the following error:

The referenced entity 'insert' was modified during ddl execution

It seemed to be a timing issue because it would be different triggers each time the code was run and I would occasionally get a deadlock error response too. I looked through the code and the resulting sql that was generated several times and could not figure out what had changed. I finally realized what had changed was that in the past I was only creating at most one trigger for each table. Now in some cases I was trying to create two or three, separate triggers for insert, update, and/or delete rather than one trigger that had all the logic combined.

The change to having multiple triggers on the same table and executing all of the DDL async resulted in the possibility of more than one trigger trying to be created on a table at the same time. That explained the error that I was seeing.

The fix was to change the code so that the triggers were created sequentially. No more issues.

Dan Hounshell
Web geek, nerd, amateur maker. Likes: apis, node, motorcycles, sports, chickens, watches, food, Nashville, Savannah, Cincinnati and family.
Dan Hounshell on Twitter