May 21, 2011 at 12:49 pm
Hi everyone,
since I got so much help the last time my knowlage hit rock bottom, I will try with a new trigger question
One of our systems has a bug somewhere, it overwrites a group code. (I know fix the source to fix the problem) The thing is that it is doing right in the first place but then messes up somewhere, the system Is BIG and complex from many diffret input programs.
I did a quick fix(DOOOOH) to temporary stop the problem, I made an after update trigger to restore the the value of the cell that was edited. It works as a charm. For one row that is. If there is more than one row with the same group code it crashes with error code
Server: Msg 512, Level 16, State 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Here is my Trigger if it helps.
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
UPDATE [dbo].[bokningar]
SET
[Webbokningsnr] = (SELECT CASE
WHEN D.Webbokningsnr IS NULL THEN I.Webbokningsnr
WHEN I.Webbokningsnr IS NULL THEN D.Webbokningsnr
WHEN I.Webbokningsnr <> D.Webbokningsnr THEN D.Webbokningsnr
ELSE D.Webbokningsnr
END
FROM deleted D INNER JOIN inserted I ON I.ID = D.ID)
WHERE ID = (SELECT ID FROM deleted)
END;
My guess is that I am guild as the last time for written a crappy trigger. And yes we are tring to find the source of the problem. This is just a quick protection so our customers can continue with daily work.
Would realy love som help and guidience with this,
/Wiklander
May 21, 2011 at 4:12 pm
You are nearly there, but you need to think in terms of sets of data, rather than individual rows.
UPDATE [dbo].[bokningar]
SET
[dbo].[bokningar].[Webbokningsnr] = ISNULL(D.Webbokningsnr,I.Webbokningsnr)
FROM
deleted D
JOIN
inserted I
ON
I.ID = D.ID
WHERE
[dbo].[bokningar].ID = D.ID
Notice also that your complicated CASE statement can be boiled down to one ISNULL.
So, here we are taking the set of old rows (deleted), joined to the set of new rows (inserted) on ID, joined to the original table on ID and updating the Webbokningsnr column to match the original value (from deleted) unless that is null, when it will take the new value (from inserted).
Hope that helps.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);May 21, 2011 at 5:40 pm
I remember reading about something with update and join, where you needed to have the first table in the from clause be the table being updated. Something Jeff mentioned about it will at some point hit a tipping point and slow to a crawl - don't remember the exact details.
Anyway, to avoid this, the statement that MM posted would be changed to this:
UPDATE t1
SET t1.Webbokningsnr = ISNULL(D.Webbokningsnr,I.Webbokningsnr)
FROM [dbo].[bokningar] t1
JOIN deleted D
ON t1.ID = D.ID
JOIN inserted I
ON I.ID = D.ID;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 22, 2011 at 10:07 am
Wayne is absolutely spot on about the possible performance problem associated with joined UPDATEs. The "target" of the UPDATE MUST be somewhere in the FROM clause (I usually make it the first table in the FROM clause to make double checking easier) for ALL joined UPDATEs.
Yes, UPDATEs that do otherwise will usually work but every system has a tipping point where the joined UPDATE will begin "Halloweening" (a name given to the problem by some IBM folks that discovered it on a Halloween). When the tipping point is reached or conditions are "just right", instead of the joined UPDATE taking (for example), less than a second, it'll slam all the CPU's (according to MAXDOP settings) 100% into the wall for hours (not a guess on my part... it actually happened at one of my previous jobs and I've also had a half dozen people write to me with similar problems which this simple rule fixed immediately).
If you look in Books Online, you won't find a single example of a joined UPDATE where the target of the UPDATE isn't in the FROM clause on joined UPDATEs.
Last but not least, the use of full-table names to identify columns in the SELECT list (and other places) has (IIRC) been deprecated. Instead, you should use a table alias. To be sure, I don't know what will happen to code that uses a Table Alias that is exactly the same as the Table Name when they finally make this change and that's why I avoid that habit, as well.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 22, 2011 at 5:20 pm
Jeff Moden (5/22/2011)
Wayne is absolutely spot on about the possible performance problem associated with joined UPDATEs. The "target" of the UPDATE MUST be somewhere in the FROM clause (I usually make it the first table in the FROM clause to make double checking easier) for ALL joined UPDATEs.Yes, UPDATEs that do otherwise will usually work but every system has a tipping point where the joined UPDATE will begin "Halloweening" (a name given to the problem by some IBM folks that discovered it on a Halloween). When the tipping point is reached or conditions are "just right", instead of the joined UPDATE taking (for example), less than a second, it'll slam all the CPU's (according to MAXDOP settings) 100% into the wall for hours (not a guess on my part... it actually happened at one of my previous jobs and I've also had a half dozen people write to me with similar problems which this simple rule fixed immediately).
If you look in Books Online, you won't find a single example of a joined UPDATE where the target of the UPDATE isn't in the FROM clause on joined UPDATEs.
Last but not least, the use of full-table names to identify columns in the SELECT list (and other places) has (IIRC) been deprecated. Instead, you should use a table alias. To be sure, I don't know what will happen to code that uses a Table Alias that is exactly the same as the Table Name when they finally make this change and that's why I avoid that habit, as well.
Thanks for pointing that out Wayne and for confirming it Jeff - one to file away.
Do you have any further details about this problem - is it a known bug? is there any evidence of it still being a problem - Jeff mentions the problem happening in the past - was it on any specific version?
Do you know what factors were involved in hitting the "tipping point" previously? I would like to run some tests on this and any pointers on conditions that may cause the bad behaviour would be appreciated.
I can see that under quick test conditions there is no difference in the plan at all and no significant difference in the execution stats so far...
I am absolutely not doubting what you say - I am just interested in the mechanics of when it goes wrong.
It is obviously a simple thing to always make sure the tables are all in the FROM clause (I would normally do that, not because I knew about this problem, but it looks nicer to me - this was a blip) and so I will stick to that rule just to be sure.
Thanks
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);May 22, 2011 at 9:37 pm
MM - Lots! of good questions. I'm sorry that I don't have an answer for you, just what I barely remember from a while back.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 23, 2011 at 2:25 am
Jeff,
Love the phrase:
... slam all the CPU's into the wall for hours ...
🙂
Just trying to visualize it now...
Bits of silicon smeared all over the server room walls... nice!
May 23, 2011 at 2:35 am
Tx everyone.
The update works like a charm. Thanx a million.
Now we can work on solving the source to the problem.
/Wiklander :-D:-D:-D
Agree with above nice phrase :w00t:
May 23, 2011 at 2:44 am
mister.magoo (5/22/2011)
Jeff Moden (5/22/2011)
Wayne is absolutely spot on about the possible performance problem associated with joined UPDATEs. The "target" of the UPDATE MUST be somewhere in the FROM clause (I usually make it the first table in the FROM clause to make double checking easier) for ALL joined UPDATEs.Yes, UPDATEs that do otherwise will usually work but every system has a tipping point where the joined UPDATE will begin "Halloweening" (a name given to the problem by some IBM folks that discovered it on a Halloween). When the tipping point is reached or conditions are "just right", instead of the joined UPDATE taking (for example), less than a second, it'll slam all the CPU's (according to MAXDOP settings) 100% into the wall for hours (not a guess on my part... it actually happened at one of my previous jobs and I've also had a half dozen people write to me with similar problems which this simple rule fixed immediately).
If you look in Books Online, you won't find a single example of a joined UPDATE where the target of the UPDATE isn't in the FROM clause on joined UPDATEs.
Last but not least, the use of full-table names to identify columns in the SELECT list (and other places) has (IIRC) been deprecated. Instead, you should use a table alias. To be sure, I don't know what will happen to code that uses a Table Alias that is exactly the same as the Table Name when they finally make this change and that's why I avoid that habit, as well.
Thanks for pointing that out Wayne and for confirming it Jeff - one to file away.
Do you have any further details about this problem - is it a known bug? is there any evidence of it still being a problem - Jeff mentions the problem happening in the past - was it on any specific version?
Do you know what factors were involved in hitting the "tipping point" previously? I would like to run some tests on this and any pointers on conditions that may cause the bad behaviour would be appreciated.
I can see that under quick test conditions there is no difference in the plan at all and no significant difference in the execution stats so far...
I am absolutely not doubting what you say - I am just interested in the mechanics of when it goes wrong.
It is obviously a simple thing to always make sure the tables are all in the FROM clause (I would normally do that, not because I knew about this problem, but it looks nicer to me - this was a blip) and so I will stick to that rule just to be sure.
Thanks
It's actually not a bug. It's a usage flaw just as if you used a NEWID() random number generator as the object of a CASE statement.
I've not been able to nail down a guaranteed "Halloweening" with this problem. It does vary based on MAXDOP settings, the combination of indexes you may or may not have, the number of joins you have and how they use or abuse the indexes, and a whole bunch of other things (apparently). I can't stress enough that this isn't a bug... no where in any MS documentation have I seen an example joined UPDATE that doesn't have the target table in the FROM clause.
In the few places I've had the priv to actually see it happen, it appeared to do just what the definition of "Halloweening" says it will do... essentially, it causes a Cartesian join across 2 or more of the joined tables and it does so internally.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 23, 2011 at 2:50 am
Almost forgot... I've had people report the problem on all versions except 2008, so far. I don't believe they "fixed" it because it's actually not a bug. The only reason why people don't report this type of problem more often is because they simply don't know what they're looking at or why it's happening. Sometimes they get lucky in "fixing" it by messing around with indexes (changes the symptoms) or maybe even using a query/table hint. The real fix is to simply use the UPDATE statment correctly. The problem with that is just like I said... most people don't see the problem even if they stare at the code for several hours (like I did the first time I found one many years ago).
--Jeff Moden
Change is inevitable... Change for the better is not.
May 23, 2011 at 2:54 am
Jeff Moden (5/23/2011)
Almost forgot... I've had people report the problem on all versions except 2008, so far. I don't believe they "fixed" it because it's actually not a bug. The only reason why people don't report this type of problem more often is because they simply don't know what they're looking at or why it's happening. Sometimes they get lucky in "fixing" it by messing around with indexes (changes the symptoms) or maybe even using a query/table hint. The real fix is to simply use the UPDATE statment correctly. The problem with that is just like I said... most people don't see the problem even if they stare at the code for several hours (like I did the first time I found one many years ago).
Thanks Jeff, I guess in the absence of anything firm to grab hold of and play with, I might just take it on board and leave the "playing" until I see one happening!
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);May 23, 2011 at 2:56 am
nigel. (5/23/2011)
Jeff,Love the phrase:
... slam all the CPU's into the wall for hours ...
🙂
Just trying to visualize it now...
Bits of silicon smeared all over the server room walls... nice!
Heh... actually, it's an old Navy phrase for maxing something (anything) out and comes from the term "Balls to the Wall". And, no... that's not as dirty as it sounds... centrifugal governors on engines had a set of iron balls on arms that would swing out further as the engine increased speed. They needed the weight on the end of the arms to "lever" a push pin that was connected to a flow regulation valve.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 23, 2011 at 2:59 am
mister.magoo (5/23/2011)
Jeff Moden (5/23/2011)
Almost forgot... I've had people report the problem on all versions except 2008, so far. I don't believe they "fixed" it because it's actually not a bug. The only reason why people don't report this type of problem more often is because they simply don't know what they're looking at or why it's happening. Sometimes they get lucky in "fixing" it by messing around with indexes (changes the symptoms) or maybe even using a query/table hint. The real fix is to simply use the UPDATE statment correctly. The problem with that is just like I said... most people don't see the problem even if they stare at the code for several hours (like I did the first time I found one many years ago).Thanks Jeff, I guess in the absence of anything firm to grab hold of and play with, I might just take it on board and leave the "playing" until I see one happening!
One thing that I can tell you for sure... in every instance that it occurred where someone contacted me, it was a hard fault. Adding the target table to the FROM clause has fixed the problem every time, so far.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply