@ (invalid title)
-
I accidentally deleted lots of data on our test database because I was apparently the first person to ever execute "DeleteSkillCategory".
Definition of that procedure:
-- Description: Deletes a skill category ALTER PROCEDURE [dbo].[DeleteSkillCategory] @skillCategoryID INT AS BEGIN DELETE FROM User_has_Skill WHERE skillID IN (SELECT skillID FROM Skill WHERE skillCategoryID = skillCategoryID) DELETE FROM Skill WHERE skillID IN (SELECT skillID FROM Skill WHERE skillCategoryID = skillCategoryID) DELETE FROM SkillCategory WHERE skillCategoryID = @skillCategoryID END
I guess I should read the source of every goddamned stored procedure in our database before executing them from now on.
-
Wait, SQL Server cannot into foreign keys with ON DELETE CASCADE?
I always knew anyone using SQL Server is massively fucked. Even more than someone using MySQL.
-
Wait, SQL Server cannot into foreign keys with ON DELETE CASCADE?
No idea if it can or not. The former coworker who did all the database-related stuff wrote tons of garbage and had no clue about anything (which we only really noticed after he left - we barely touched his code before that).
-
No idea if it can or not. The former coworker who did all the database-related stuff wrote tons of garbage and had no clue about anything (which we only really noticed after he left - we barely touched his code before that).
You saw it coming...After one of our cow-orkers left and we hit quite a few of his precious little gems in the codebase, we multiply our own estimates by 3 (yes, those we previously multiplied by π to account for our own optimism) every time we work on something he might have touched.
-
SQL Server cannot into foreign keys with ON DELETE CASCADE?
SQL Server FKs can be defined with cascade delete, but I've personally never seen it used in the wild.
-
Also, GREAT JOB ON RUINING THE CODE EVEN MORE, DISCOIDENT.
-
It gets better.
Pretty much all the code (as in, some of the VB.NET code in the application) that deals with the things this procedure is supposed to delete, is bad or wrong.
-
I think he meant running something like
DELETE FROM foo CASCADE WHERE bar = 1
which is different than having a trigger do that.
-
Not sure what else you were expecting to happen, or why this is apparently a bad thing (in terms of the result). Would you prefer to have invalid foreign keys?
I'm assuming of course that Skill.skillCategoryID and User_has_Skill.skillID are not nullable. Particularly the first one - otherwise, it would be reasonable operationally to delete the unwanted category, then assign a new category to any skills left without a category, while maintaining the association of skills to users. So in this case I would expect the procedure to update the skillCategoryID to null on the affected Skill records and leave User_has_Skill alone.
-
Take another careful look at the code :P
-
And remember the topic title.
-
Not being familiar with T-SQL syntax, that really was a critical piece of information.
-
Whoops.
-
Whoops.
Why? I'd expect people here have enough deductive capability to put those two pieces of information together. Nobody apologizes for posting Haskell code either, and I'm going to buttume most of us don't speak Haskell either.
-
Of course, TR is making
SkillCategoryID
the PK ofSkillCategory
-
Take another careful look at the code :P
AH. Well, fair enough then.For anyone else who is as confused as I was: the deletes from the User_has_Skill and Skill tables use
WHERE skillCategoryID = skillCategoryID
rather thanWHERE skillCategoryID = @skillCategoryID
in the where clause. So instead of deleting all records linked to the category being deleted, they delete all records in the table. I can see how that might be a problem.
-
-
TR , of course, is why the author didn't just use
TRUNCATE
…
-
-
Do you have to select the table name from [spoiler]DUAL[/spoiler] thought?
Filed under: trigger warning
-
T-SQL doesn't need
DUAL
; you can write aSELECT
without a table
-
And remember the topic title.
That was the part that took me a while. For some reason my brain wasn't connecting "@" with "at".Meanwhile I immediately recognized "x = x" as an obvious mistake.
-
I know, I am just being facetious and making fun of how many freaking SQL dialects we have.
-
For some reason my brain wasn't connecting "@" with "at".
I tried changing the title to@
; Dischorse told me to go away
-
I initially wanted to make that the title but it didn't work.
So I set it to "at".
-
There you go.
-
RTLO to the rescue!
-
How?!
Did you use @?
-
... wow.
-
How?!
Your favourite Unicode character?
And yes, there are other ones I could use but I know the hex code of that one off the top of my head. Also, added new post notification fun
-
So, the current title is:
U+000A - Line Feed
U+0020 - Space
U+200F - Right-to-Left mark
U+0040 - I'll let you take a guess of what this character is
U+000A - Line Feed
U+0020 - SpaceWere those extra spaces necessary or are they left-overs from pasting?
-
All I added is the RTL. No idea where the rest came from. And I inserted it directly, no copy/pasting involved.
-
Wait, SQL Server cannot into foreign keys with ON DELETE CASCADE?
Just because you can doesn't mean you really want to. Sometimes enforcing referential integrity means rolling back an errant delete rather than propagating it to all the things.
-
T-SQL doesn't need DUAL; you can write a SELECT without a table
Evil idea: Hire some Oracle developers in a SQL Server shop, and create a DUAL table with 1000000 rows
-
The sproc is a WTF even if it worked. Why all the subqueries?
-
Fuck if I know.
There's not really a reason for it to be a sproc, either. We have longer INSERTs that are coded Application-side.
-
Oh hey, we did that too (cascade deletion via sproc).
Of course, then it turned out somebody hooked NHibernate to some of the object in question, and all kinds of hell broke loose...
-
Sometimes enforcing referential integrity means rolling back an errant delete rather than propagating it to all the things.
ON DELETE RESTRICT is for this.
-
...and I was under the impression that ON DELETE RESTRICT was the default for most databases.
-
ON DELETE RESTRICT is for this.
Yes, that's my point. So you have to go out of your way to delete all of the child rows instead of accidentally wiping them out.
-
Post 16 before someone explains the .
-
-
Why is the Dreamcast logo emoji blue?
-
Europe.
-
Huh. TIL.
See, that's an "advantage" of living in a backwater country and most of the material about this stuff being produced by Americans... even if I heard about that I forgot. And I don't think I ever saw a Dreamcast in person...
-
I saw a Hello Kitty Dreamcast in a store once and didn't buy it.
I regret it.
-
For future reference:
def symbols_regex /[\ -\/\[-\`\:-\@\{-\~]/m end
-
Supercalifragilisticexpialidocious...
s/unpretentious/unprecocious/
-
Supercalifragilisticexpialidocious
Humuhumunukunukuapua
?Llanfairpwllgwyngyllgogerychwyrndrobwllllantysiliogogogoch
?Methionylthreonylthreonylglutaminylarginyl...isoleucine
(unelided: 189,819 letters)
-
So, no discussing the meal called Lopadotemachoselachogaleokranioleipsanodrimhypotrimmatosilphioparaomelitokatakechymenokichlepikossyphophattoperisteralektryonoptekephalliokigklopeleiolagoiosiraiobaphetraganopterygon then…