Smart Rename from columns wrongfuly substitutes name

Refactors and formats SQL code.

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

Smart Rename from columns wrongfuly substitutes name

Postby paulo.morgado » Fri Jun 11, 2010 3:02 pm

(SQL Refactor version 1.5.1.61)

I have a set of logging tables in my database that have the same set of columns of the table they are logging changes and some more log related columns. Log tables are populated through instead of insert, delete and update triggers of the logged table.

When I rename one column that's both in the log and logged table, SQL Refactor wrongfuly replaces the name of the column in the deleted and inserted tables.
Paulo Morgado
Portugal
Web Site
Weblog
Twitter
paulo.morgado
 
Posts: 52
Joined: Wed Aug 16, 2006 3:41 pm
Location: Portugal

Postby Brian Donahue » Mon Jun 14, 2010 10:06 am

Hello Paulo,

I'm not completely clear on what is happening. SQL Refactor can only smart-rename one column at a time, so the best assumption that I can make is that you are either smart-renaming a computed column, or the changes necessary to complete the smart rename are unnecessarily rebuilding tables (copying data to a new table, dropping the old one and renaming the temporary table back) and we have a patch that we can give you in case the latter is the problem.

Please let me know.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Postby paulo.morgado » Thu Jun 17, 2010 11:50 pm

Sorry for the late reply.

Just imagine you have this table:

Code: Select all
CREATE TABLE [Log].[Test](
   [Test] [nchar](10) NULL
) ON [PRIMARY]


And this one:

Code: Select all
CREATE TABLE [dbo].[Test](
   [Test] [nchar](10) NULL
) ON [PRIMARY]


With this trigger:

Code: Select all
CREATE TRIGGER [dbo].[TestTrigger] ON [dbo].[Test]
    AFTER INSERT
AS
    BEGIN
        SET NOCOUNT ON ;

        INSERT  INTO [Log].[Test]
                ( [Test] )
                SELECT  [Test]
                FROM    inserted

    END


If I use SQL Refactor to rename [Log].[Test].[Test] to [Column], the generated trigger script is this:

Code: Select all
ALTER TRIGGER [dbo].[TestTrigger] ON [dbo].[Test]
    AFTER INSERT
AS
    BEGIN
        SET NOCOUNT ON ;

        INSERT  INTO [Log].[Test]
                ( [Column] )
                SELECT  [Column]
                FROM    inserted

    END
Paulo Morgado
Portugal
Web Site
Weblog
Twitter
paulo.morgado
 
Posts: 52
Joined: Wed Aug 16, 2006 3:41 pm
Location: Portugal

Postby Brian Donahue » Fri Jun 18, 2010 9:37 am

Hi Paulo,

To summarize, the problem is that the trigger is altered to select [Column] FROM Inserted? Is this supposed to remain as [Test]?
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Postby paulo.morgado » Fri Jun 18, 2010 8:04 pm

Yes. Because it's a trigger for [dbo].[Test] and not [Log].[Test], which means that the structure of inserted is the same as [dbo].[Test], not [Log].[Test].
Paulo Morgado
Portugal
Web Site
Weblog
Twitter
paulo.morgado
 
Posts: 52
Joined: Wed Aug 16, 2006 3:41 pm
Location: Portugal

Postby Brian Donahue » Mon Jun 21, 2010 9:47 am

Thanks for clearing that up. I have opened a bug for SQL Refactor - SR-889. We should be in touch with you if there are any updates.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am


Return to SQL Refactor 1

Who is online

Users browsing this forum: No registered users and 1 guest