Smart Rename from columns wrongfuly substitutes name

paulo_morgado

(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.

Brian Donahue

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.

paulo_morgado

Sorry for the late reply.

Just imagine you have this table:

CREATE TABLE [Log].[Test](
	[Test] [nchar](10) NULL
) ON [PRIMARY]

And this one:

CREATE TABLE [dbo].[Test](
	[Test] [nchar](10) NULL
) ON [PRIMARY]

With this trigger:

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:

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

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

    END

Brian Donahue

Hi Paulo,

To summarize, the problem is that the trigger is altered to select [Column] FROM Inserted? Is this supposed to remain as [Test]?

paulo_morgado

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].

Brian Donahue

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.