August 21, 2018 at 6:07 pm
Hi
I have notice something weird with the new syntax with a "drop if exist"
It looks like the new syntax fire database triggers even if it does nothing
Exemple :
I have a database with a database trigger :
create TRIGGER [my_db_trigger]
ON DATABASE
FOR CREATE_TABLE, ALTER_TABLE, RENAME, DROP_TABLE
AS
/*trigger logic*/
if i write a "traditionnal" column dropping
IF EXISTS(SELECT * FROM SYS.columns WHERE name='my_column' AND
OBJECT_ID = OBJECT_ID('[dbo].[my_table]'))
ALTER TABLE dbo.my_table DROP COLUMN [my_column]
If the column exist : column is dropped and the database trigger perform (as expected)
If the column doesnt exist : nothing is done (as expected)
if i use the new syntaxALTER TABLE dbo.my_table DROP COLUMN IF EXISTS my_column
If the column exist : column is dropped and the database trigger perform (as expected)
If the column doesn't exist : there is no schema modification BUT the trigger perform!(not as expected)
This is a very big difference and i found no trace of it in any Microsoft documentation
Do you think it is a bug?
of something missing in the documentation?
Or maybe it is in the documentation but i didn't saw it.
There is tons of website recommending the new syntax , but if the behavior is different this is no more a "replacement"
August 21, 2018 at 6:46 pm
Without researching this more myself, I would say this is expected as the ALTER TABLE is actually executed even though the column doesn't exist.
August 21, 2018 at 7:04 pm
Yes it may be expected but if you do a "drop table if exists" the database trigger is not fireddrop table if exists dbo.my_table
This look like to be inconsistent, why a drop column in an alter table fire the trigger even if it does nothing and a drop table with the same syntax doesnt fire it?
August 21, 2018 at 7:16 pm
Nkl - Tuesday, August 21, 2018 7:04 PMYes it may be expected but if you do a "drop table if exists" the database trigger is not fireddrop table if exists dbo.my_tableThis look like to be inconsistent, why a drop column in an alter table fire the trigger even if it does nothing and a drop table with the same syntax doesnt fire it?
Have you examined the XML data packet from when the trigger fired?
August 22, 2018 at 5:10 am
Think of the IF EXISTS command like the WHERE clause in a query. Yes, your WHERE clause may eliminate all the data in a table/index, but the table/index was still accessed. You're issuing an ALTER TABLE command, and then the IF EXISTS says whether or not anything happens, but the ALTER was still issued, just nothing happened.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 22, 2018 at 12:05 pm
Thanks @grant and @Lynn
What i found really weird is your explanation is valid for alter not for drop
drop table if exist will not fire the trigger but alter table drop column if exist fire it
It mean a drop that doesnt drop is not "issued"
but an alter that doesnt alter is "issued"
so the "if exists" is like a where clause only in an alter, not in a pure drop
August 22, 2018 at 12:14 pm
Nkl - Wednesday, August 22, 2018 12:05 PMThanks @grant and @Lynn
What i found really weird is your explanation is valid for alter not for drop
drop table if exist will not fire the trigger but alter table drop column if exist fire it
It mean a drop that doesnt drop is not "issued"
but an alter that doesnt alter is "issued"
so the "if exists" is like a where clause only in an alter, not in a pure drop
Well good. I'd hate to see it dropping tables.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 22, 2018 at 12:16 pm
It does seem like a weird inconsistency. You could try filing a bug against it, but it may fall into the "sometimes triggers are just weird" category and not get fixed anytime soon. It's very much an edge case and you could make the argument both for it not firing in the ALTER scenario or that it should fire in the DROP scenario.
As is often the way, the "replacement" syntax for something isn't always guaranteed 100% the same behaviour, unfortunately.
August 22, 2018 at 12:44 pm
Yes,, i have no problem to have different behavior
I have problem about the communication to us where Microsoft says "it is exactly the same" and it is not
Of course not everybody has a database level trigger but when you have it it can become a BIG difference
I agree with you : the 2 options (should not fire in alter or should fire in drop) have equally pro/cons
But whatever the option, having consistency is good
How to declare a bug to Microsoft?
there is the "feedback" website but this is more about enhancement
August 22, 2018 at 1:16 pm
Nkl - Wednesday, August 22, 2018 12:05 PMThanks @grant and @Lynn
What i found really weird is your explanation is valid for alter not for drop
drop table if exist will not fire the trigger but alter table drop column if exist fire it
It mean a drop that doesnt drop is not "issued"
but an alter that doesnt alter is "issued"
so the "if exists" is like a where clause only in an alter, not in a pure drop
You could have multiple DROP COLUMN IF EXISTS clauses in a single ALTER TABLE. This would explain why the DDL trigger on ALTER TABLE would still fire if a column did not exist. Simpler to implement would be my guess.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply