[RESOLVED] SQL 2005 trigger glitch?

[BruceG]

Hey, wondering if any fellow old-schoolers ran into this issue, and if this is a SQL 2005 glitch or whether I did something wrong.
This is really a "post-mortem", as I seem to have fixed the issue.
So I am maintaining a legacy app for one of my clients - VB6 with SQL 2005 backend.
A requested application update called for adding 4 new fields to a particular table.
Did the usual ALTER TABLE ADD ... and as expected, expanding the table's columns shows the 4 new fields at the end.
The code uses in-line SQL, and btw it uses this alternative INSERT/SELECT syntax: "INSERT tablename SELECT col1 = value, col2 = value, etc."
I happened to place the code for new fields above the last one, not at the very end - i.e.:
The existing code had:
INSERT tablename SELECT col1 = value, col2 = value, ... coln = value
My modification initially was:
INSERT tablename SELECT col1 = value, col2 = value, ... newcol1 = value, newcol2 = value, newcol3 = value, newcol4 = value, coln = value
Shouldn't matter, right?
Well, there is an insert trigger on this table that will prevent the insert if "coln" has a value it doesn't like.
And when the insert is attempted, the trigger throws an exception because it thinks coln has an invalid value.
Yet a Debug.Print of the SQL INSERT statement shows that the columns have the right values.
Head-scratcher...
So, just for kicks, I changed my SQL to:
INSERT tablename SELECT col1 = value, col2 = value, ... coln = value, newcol1 = value, newcol2 = value, newcol3 = value, newcol4 = value
And what do you know? - the trigger was happy and did not throw an exception, and all appeared well.
Any thoughts on this? Was there a glitch in SQL 2005 triggers that was corrected later?

[Zvoni]

Depends on the code of the trigger.
maybe you used ordinal access there, and the nth col is an integer, and your first try was passing text.
*shrug*
Do you have a description of the exception?

[BruceG]

Just found the answer: apparently the funky INSERT/SELECT syntax inserts the values in the order specified, ignoring the "colname = " part.

[wqweto]

Just found the answer: apparently the funky INSERT/SELECT syntax inserts the values in the order specified, ignoring the "colname = " part.

Yes, the actual names of the columns in the SELECT do not matter, its only their position which should match. The colname = value syntax is equivalent to value AS colname syntax which we usually frown upon the former.

If you have to reorder the columns in the SELECT you have to use INSERT MyTable(column-list-here) syntax. In fact we always use explicit column list with our inserts as to not depend on physical column order of base tables.

It is only for temp tables created inside a stored procedure when we skip column list if appropriate as both CREATE TABLE and INSERT statements are within the scope of SP i.e. both are owned by it.

[Zvoni]

So i wasn't that far off with my suspicion....

[BruceG]

Thanks, guys, this has been a good teachable moment for me.
I have come to find out that wqweto is indeed correct.
The form of the INSERT statement that is being used in this legacy app is composed of two parts: the SELECT part, in which, as wqweto correctly points out, treats the "colname = " part as a column alias.
In a SSMS query window, as you know you can run a SELECT query with no table, such as "SELECT 1, 2". You can give these values aliases, such as "SELECT x = 1, y = 2".
So this form of the INSERT statement says: "INSERT TableName (the SELECT statement that generates a list of values)"
Which as we know from the discussion above is the same as doing an INSERT statement without the column references, thereby defaulting to the ordinal position of the columns, i.e.: "INSERT TableName (list of values)".
Had I originally authored this, I naturally would have used the recommended explicit column list form: "INSERT (column list) VALUES (value list)".