September 10, 2013 at 8:08 am
bitbucket-25253 (9/9/2013)
Now I known why I detest using triggers.
Yet another reason. What a horrible design decision. :blink:
September 10, 2013 at 8:38 am
Jeff.MSSqlSage (9/10/2013)
You guys certainly have an interesting definition of what defines "basics".I don't think an undocumented feature of the ALTER TRIGGER functionality qualifies as "basics".
+1
Good question though, as this is good to know. I thought you had to explicitly re-enable, so I learned something.
September 10, 2013 at 8:53 am
Jeff.MSSqlSage (9/10/2013)
You guys certainly have an interesting definition of what defines "basics".I don't think an undocumented feature of the ALTER TRIGGER functionality qualifies as "basics".
I don't know why or where I learned this, but this undocumented feature has been around forever, I thought it was how you enabled disabled triggers in the past. Might be a bad habit I picked up off of somebody else though.
September 10, 2013 at 9:10 am
sestell1 (9/10/2013)
bitbucket-25253 (9/9/2013)
Now I known why I detest using triggers.Yet another reason. What a horrible design decision. :blink:
I wish I was the first who had an opportunity to say this. 😉
edit: cleaned up wording
September 10, 2013 at 10:08 am
mtassin (9/10/2013)
Jeff.MSSqlSage (9/10/2013)
You guys certainly have an interesting definition of what defines "basics".I don't think an undocumented feature of the ALTER TRIGGER functionality qualifies as "basics".
I don't know why or where I learned this, but this undocumented feature has been around forever, I thought it was how you enabled disabled triggers in the past. Might be a bad habit I picked up off of somebody else though.
+1. I had seen this in place too and thought that was how it was done. Glad to know it is actually an odd behavior that hopefully will be documented in BOL soon!
Thanks for the question Pavel.
September 10, 2013 at 6:09 pm
KWymore (9/10/2013)
mtassin (9/10/2013)
Jeff.MSSqlSage (9/10/2013)
You guys certainly have an interesting definition of what defines "basics".I don't think an undocumented feature of the ALTER TRIGGER functionality qualifies as "basics".
I don't know why or where I learned this, but this undocumented feature has been around forever, I thought it was how you enabled disabled triggers in the past. Might be a bad habit I picked up off of somebody else though.
+1. I had seen this in place too and thought that was how it was done. Glad to know it is actually an odd behavior that hopefully will be documented in BOL soon!
Thanks for the question Pavel.
It's not just odd, it's appalling. Terrible interface design. Alter Trigger shouldn't automatically enable - if there's a need to enable at the same time as altering the right design is to put an extra parameter on the alter interface that allows the user to say whether he wants the trigger left as it is, or enabled, or disabled (all three options should be provided).
However, since it's there by design it will be good to get it documented, which presumably will happen eventually if MS remember they've said they'll document it.
Tom
September 10, 2013 at 6:22 pm
L' Eomot Inversé (9/10/2013)
Nice question that illustrates an interesting undocumented feature.Richard Warr (9/10/2013)
I found some supporting documentation here. Seems a bit of a "hidden gem" though :crazy:Back in May Microsoft said this was by design and they were going to document in BOL, and in June they said it would take a couple of weeks to make the change. (see this connect entry).
Twelve weeks on it's still not documented in BOL (not even for SQL 2014). I wonder if they've forgotten?
edit: get link tags right
+1
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 10, 2013 at 6:43 pm
L' Eomot Inversé (9/10/2013)
KWymore (9/10/2013)
mtassin (9/10/2013)
Jeff.MSSqlSage (9/10/2013)
You guys certainly have an interesting definition of what defines "basics".I don't think an undocumented feature of the ALTER TRIGGER functionality qualifies as "basics".
I don't know why or where I learned this, but this undocumented feature has been around forever, I thought it was how you enabled disabled triggers in the past. Might be a bad habit I picked up off of somebody else though.
+1. I had seen this in place too and thought that was how it was done. Glad to know it is actually an odd behavior that hopefully will be documented in BOL soon!
Thanks for the question Pavel.
It's not just odd, it's appalling. Terrible interface design. Alter Trigger shouldn't automatically enable - if there's a need to enable at the same time as altering the right design is to put an extra parameter on the alter interface that allows the user to say whether he wants the trigger left as it is, or enabled, or disabled (all three options should be provided).
However, since it's there by design it will be good to get it documented, which presumably will happen eventually if MS remember they've said they'll document it.
Like with an Alter Index, if the Index is disabled you cannot alter the index to reorg nor can you alter it with a set option. Similar should be in place for an Alter trigger. Alter trigger should require the enable be specified or it should not be alterable.
Alter index on the other hand will enable the index if a REBUILD is issued in the alter statement. Maybe part of the thinking is that the body of the structure has changed so it is "meant" to now be enabled. I'd still rather see it required to issue the enable statement if the trigger is disabled.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 10, 2013 at 7:46 pm
SQLRNNR (9/10/2013)
L' Eomot Inversé (9/10/2013)
KWymore (9/10/2013)
mtassin (9/10/2013)
Jeff.MSSqlSage (9/10/2013)
You guys certainly have an interesting definition of what defines "basics".I don't think an undocumented feature of the ALTER TRIGGER functionality qualifies as "basics".
I don't know why or where I learned this, but this undocumented feature has been around forever, I thought it was how you enabled disabled triggers in the past. Might be a bad habit I picked up off of somebody else though.
+1. I had seen this in place too and thought that was how it was done. Glad to know it is actually an odd behavior that hopefully will be documented in BOL soon!
Thanks for the question Pavel.
It's not just odd, it's appalling. Terrible interface design. Alter Trigger shouldn't automatically enable - if there's a need to enable at the same time as altering the right design is to put an extra parameter on the alter interface that allows the user to say whether he wants the trigger left as it is, or enabled, or disabled (all three options should be provided).
However, since it's there by design it will be good to get it documented, which presumably will happen eventually if MS remember they've said they'll document it.
Like with an Alter Index, if the Index is disabled you cannot alter the index to reorg nor can you alter it with a set option. Similar should be in place for an Alter trigger. Alter trigger should require the enable be specified or it should not be alterable.
Alter index on the other hand will enable the index if a REBUILD is issued in the alter statement. Maybe part of the thinking is that the body of the structure has changed so it is "meant" to now be enabled. I'd still rather see it required to issue the enable statement if the trigger is disabled.
Agreed. Just because you altered a disabled trigger does not mean that you are ready to use that trigger again.
September 11, 2013 at 2:47 am
Stewart "Arturius" Campbell (9/10/2013)
another notch to my list of reasons to dislike triggers
Really? Can't say this behaviour would appear anywhere on my list of bad things about triggers. I've occasionally needed to disable triggers, but the process is always to disable them, do the update that required them to be disabled, then immediately re-enable. I can't imagine any circumstance where I'd want to alter a disabled trigger.
September 11, 2013 at 3:31 am
Nice Question....
September 11, 2013 at 9:41 am
Thanks for the great question! Can't ever get enough on triggers! 🙂
I don't work with triggers that often, so it's a great boost to get a good question like this.
Just my 2 cents, if the answer had been put like the following, it might've been easier to read:
"Two rows: one with name 'First' and one with name 'Third' "
September 12, 2013 at 12:04 am
Good basic question and discussion 🙂
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
September 13, 2013 at 1:04 am
Good question. 🙂
September 17, 2013 at 2:55 am
Toreador (9/11/2013)
Stewart "Arturius" Campbell (9/10/2013)
another notch to my list of reasons to dislike triggersReally? Can't say this behaviour would appear anywhere on my list of bad things about triggers. I've occasionally needed to disable triggers, but the process is always to disable them, do the update that required them to be disabled, then immediately re-enable. I can't imagine any circumstance where I'd want to alter a disabled trigger.
Seconded.
We would only alter a trigger with a new release of our system, not on the fly within the code. If we wanted a trigger to do different things, we would code the trigger to have those possibilities within it.
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply