Smart Rename false positive in INSTEAD OF INSERT trigger

Refactors and formats SQL code.

Moderators: Chris Auckland, David Atkinson, Anu Deshpande, Luke Jefferson

Smart Rename false positive in INSTEAD OF INSERT trigger

Postby geophphrie » Wed Jun 17, 2009 5:09 pm

I was just doing a Smart Rename, renaming column "isactive" to "stati" in tbl_orgz. All dependent views and procs were handled correctly. However, the very last alter generated was an alter trigger on an "instead of insert" trigger on tbl_student: smart rename was wanting to swap out column isactive (which exists) for stati (which doesn't exist) on the SELECT <columns> FROM INSERTED. I have no idea how it thought this was related to my change, since this is a trigger on a different table. The tables are related by a FK, but the trigger itself doesn't involve the table I was changing. I think it would have failed if I had not caught it (since the new column name would not have existed), though actually... it might have completed the ALTER successfully and failed at runtime - this was a live db so I wasn't going to try it :-)

I guess since an INSTEAD OF INSERT trigger could potentially be used to do a multi-table related insert with one insert command, and the table I was changing was involved in an FK insert, Smart Rename though that the isactive column from the INSERTED alias could be the same as the column I was changing - kind of jumping to an incorrect conclusion in this case.
geophphrie
 
Posts: 4
Joined: Wed Jun 10, 2009 7:25 pm
Location: Chattanooga TN USA

Postby eddie davis » Thu Jun 18, 2009 8:17 pm

Thank you for your post into the forum.

What version of SQL Refactor are you using?

Can you please reply with the table creation scripts for the table tbl_orgz and for the tbl_student, including the trigger creation syntax?

If you do not want to make the creation scripts public, can you please send them in an e-mail to support@red-gate.com. Please place the following call reference number into the subject field of the e-mail, F0023399.

Many Thanks
Eddie
Eddie Davis
Technical Support Engineer
Red Gate Software Ltd
E-mail: support@red-gate.com
eddie davis
 
Posts: 943
Joined: Wed Jun 14, 2006 3:47 pm
Location: Red Gate Software


Return to SQL Refactor 1

Who is online

Users browsing this forum: No registered users and 0 guests