Erroneous table reference inserted

sqlScott2

possible bug in SQL Prompt 10.13.1.31417 (latest version verified)

this code:

UPDATE dbo.table1

SET CurrentStatusFlag = 'N'

FROM inserted

WHERE dbo.table1.key1   = inserted.key1

    AND dbo.table1.CurrentStatusFlag = 'Y';

UPDATE dbo.table1

SET CurrentStatusFlag = 'Y'

FROM inserted

WHERE inserted.key1     = dbo.table1.key1

    AND inserted.key2   = dbo.table1.key2

    AND inserted.key3   = dbo.table1.key3

    AND inserted.key4   = dbo.table1.key4

    AND inserted.key5   = dbo.table1.key5;

incorrectly became this:

    UPDATE dbo.table1

       SET CurrentStatusFlag = 'N'

      FROM inserted

     WHERE Inserted.key1   = Inserted.key1

       AND Inserted.CurrentStatusFlag = 'Y';

    UPDATE dbo.table1

       SET CurrentStatusFlag = 'Y'

      FROM inserted

     WHERE Inserted.key1    = Inserted.key1

       AND Inserted.key2    = Inserted.key2

       AND Inserted.key3    = Inserted.key3

       AND Inserted.key4    = Inserted.key4

       AND Inserted.key5    = Inserted.key5;

Fortunately, these two UPDATE statements are being replaced with a single one, so it would not have gone to PRODUCTION with this bad code.

This being from the AFTER INSERT trigger of TableA (very central transaction table, very active), this would have been desasterous.

It would have downed the whole system.

Dan_J

Hi @sqlScott2

I'm sorry you're seeing this issue! Thanks for reaching out to us regarding this.

For absolutely clarity, are you saying that SQL Prompt have changed your code, and not just the formatting of it?

sqlScott2

That is correct. dbo.table1 got changed to inserted in my example.

Dan_J

Hi @sqlScott2

My apology for the delay in coming back to you on this.

Would it be possible for you to send over your active style file so that I can use it in my testing of this issue? I'm happy to reach out to you directly via a ticket if you would prefer not to provide this via the forum.

sqlScott2

I am using a new work computer and I had tried to export my SQL Prompt settings on old computer and import them in on my new computer. Did NOT seem to be working. The instruction for that in the forum seemed conflicting with reality. So I am not sure what the right way to do it is.
On new computer I have SQL Prompt 10.13.3.32087
On old computer I have 10.13.1.31417

Maybe i need to get newer version on old computer?

Dan_J

Hi @sqlScott2,

The Export and Import functionality should allow for the bulk of your settings to be migrated across, however you will need to manually copy over your Snippet and Style folders over from:  C:\Users\youruser\AppData\Local\Red Gate\SQL Prompt 10\

Aside from your Snippets and Styles, are there any other settings you've noticed have not migrated over?

As far as I'm aware there should be no issues with doing this with the versions of SQL Prompt you have installed. 

Dan_J

Hi @sqlScott2

Thank you for the style file you provided.

Strangely, I am not seeing the same change to the code that you are describing, and so I have asked for our development teams' input on this.

I will come back to you on this again as soon as possible.

Dan_J

Hi @sqlScott2

My apology for the delay in coming back to you regarding this.

Unfortunately neither myself or our development team have been able to reproduce the behavior you reported here. Without a working reproduction of the issue here, it's impossible for the team to investigate this or produce a fix, if one is required.

Please do let us know if you notice anything like this happen again so that we can attempt to reproduce it.

sqlScott2

Thanks for trying. I will let you know if I see a simular problem in the future.