• malfist (unregistered)

    Poor martin

  • Sarcasm FTW (unregistered)

    It is always fun to look back at code and admonish the original writer to only later find out it was you.

  • TooSoonASys, She Says... (unregistered)

    Confession: I have, on at least one occasion, posted obscene, unsavory or otherwise inappropriate comments to this website.

  • (cs)

    This is why triggers should be WAY more complicated. Any idiot can write them these days, and that will just not do.

    Come to think of it, the first thing I do when I notice a trigger on a table is say "Idiot!" and brace myself for the worst.

  • Synchronos (unregistered)
    Alex Papadimoulis:
    Apparently, not many of you were willing to own up to your own curious previsions and submit them non-anonymously.

    No wonder, if you want us to see our mistakes even before we do them.

  • (cs)

    I must admit I'm curious to know what the notification email said in lieu of deleting the reservation.

    "Reservation successfully deleted."

  • Amtep (unregistered)

    I like how our thoughtful submitter made sure that Martin would receive all 1137 notification emails. Craftsmanship like that is just not appreciated anymore these days.

  • (cs) in reply to Synchronos
    Synchronos:
    Alex Papadimoulis:
    Apparently, not many of you were willing to own up to your own curious previsions and submit them non-anonymously.

    No wonder, if you want us to see our mistakes even before we do them.

    If you want user-submitted WTFs, just read the comments.

  • Larry (unregistered)

    I thought I deleted this comment.

  • jverd (unregistered)

    Wow, that's just precious!

    But I am confused about one thing. The rows were getting deleted one at a time, right? But if it's an INSTEAD OF trigger, how were they getting deleted at all?

    Addendum: I think the author should share some of his coffee with me. I spent a good 15 seconds trying to copy/paste the captcha and getting pissed off that it wasn't working. Brillant!

  • (cs)

    I am so disappointed. I kept waiting for the inevitable slip-up followed by:

    (4926184 row(s) affected)

    ...and a head banging the desk.

  • (cs)

    This is the way he should have fixed it, in true WTF fashion:

    while (o.entriesRemain()) { o.delete(); }

    Simple but effective, and just think of all those emails!

  • AP2 (unregistered) in reply to jverd
    jverd:
    But I am confused about one thing. The rows were getting deleted one at a time, right? But if it's an INSTEAD OF trigger, how were they getting deleted at all?

    The INSTEAD OF had its own DELETE. At least that's what I've understood from the text.

  • SomeCoder (unregistered) in reply to Coyne
    Coyne:
    I am so disappointed. I kept waiting for the inevitable slip-up followed by:

    (4926184 row(s) affected)

    ...and a head banging the desk.

    I too, was waiting for this. As someone who made this mistake years ago, I'm ALWAYS waiting for this :)

  • TooSoonASys, She Says... (unregistered) in reply to malfist
    malfist:
    Poor martin
    What, did he get a pink-sock?
  • влÐ (unregistered)

    Heh, Martin earned it after blocking every Thursday for everyone for a few years. You gotta teach him his lesson somehow.

  • foo (unregistered) in reply to Amtep
    Amtep:
    I like how our thoughtful submitter made sure that Martin would receive all 1137 notification emails. Craftsmanship like that is just not appreciated anymore these days.

    Don't be rediculous. The emails were sent to "allusers"

  • (cs)

    I had this same situation one time. There, I fixed it by running the DELETE statement in a loop until SELECT returned 0 records.
    I'm a bit of a Rock Star 'round these parts.

  • Keine Macht fuer niemand (unregistered)

    My name is Martin

    I could have done the same

  • Keine Macht fuer niemand (unregistered) in reply to boog
    boog:
    I must admit I'm curious to know what the notification email said in lieu of deleting the reservation.

    "Reservation successfully deleted."

    But the email got deleted before the mail was sent...

  • mr. curmudgeon (unregistered)

    most frightening part:

    this time making doubly sure that I was on the correct server
  • (cs)

    TRWTF is deleting the reservation instead of marking it as "Canceled" or the equivalent.

  • typ (unregistered)
    Being in the habit of coding till 3:00AM nightly, I was averaging about three to four cups a day. As I sat down at my desk to tackle one the several projects I had been assigned, I got a frantic call from the Director of IT, Jeremy.

    Only four cups? That is noon for me (and yes that is probably not a good habit).

  • (cs) in reply to Coyne
    Coyne:
    I am so disappointed. I kept waiting for the inevitable slip-up followed by:

    (4926184 row(s) affected)

    ...and a head banging the desk.

    I had slightly more faith - I was waiting for the inevitable missing commit.

  • Gunslinger (unregistered)

    Who hasn't done this?

  • (cs) in reply to SomeCoder
    SomeCoder:
    Coyne:
    I am so disappointed. I kept waiting for the inevitable slip-up followed by:

    (4926184 row(s) affected)

    ...and a head banging the desk.

    I too, was waiting for this. As someone who made this mistake years ago, I'm ALWAYS waiting for this :)

    That's why I was waiting for it, too, I guess.

    We have this utility that allows you to save rows into a table. While saving, you can use the option APPEND=YES. The default is, of course, APPEND=NO and so guess what happens if you save 20 rows without specifying APPEND=YES?

  • Cheong (unregistered) in reply to Amtep

    He should make sure the code sends 1337 emails each time to ensure its 1337ness.

  • Glenn (unregistered)

    So.... TRWTF is being able to write a delete statement against a production database. Surely the developer should not have access. What about testing and a change control process?

  • (cs) in reply to Coyne
    Coyne:
    SomeCoder:
    Coyne:
    I am so disappointed. I kept waiting for the inevitable slip-up followed by:

    (4926184 row(s) affected)

    ...and a head banging the desk.

    I too, was waiting for this. As someone who made this mistake years ago, I'm ALWAYS waiting for this :)
    That's why I was waiting for it, too, I guess.
    Yeah, I was waiting for it too. :) I guess it's good that this WTF provided a bit more novelty than we were expecting...

  • Maxpm (unregistered)

    Eh. The address probably would have been marked as spam before it could do too much damage.

    But then people wouldn't get email confirmations about their reservations being deleted! Oh, no!

  • Kayaman (unregistered) in reply to typ

    I was reading the wikipedia's "List of countries by coffee consumption per capita" and noticed USA's 4.2kg (that's about 9 pounds) and thought to myself:

    Only four kilos? That's April for us.

  • (cs) in reply to TooSoonASys, She Says...
    TooSoonASys:
    Confession: I have, on at least one occasion, posted obscene, unsavory or otherwise inappropriate comments to this website.

    Yes, but from a database trigger?

  • (cs) in reply to Glenn
    Glenn:
    So.... TRWTF is being able to write a delete statement against a production database. Surely the developer should not have access. What about testing and a change control process?

    In its defence, this application is merely an internal company admin tool.

  • trtrwtf (unregistered) in reply to Matt Westwood
    Matt Westwood:
    Glenn:
    So.... TRWTF is being able to write a delete statement against a production database. Surely the developer should not have access. What about testing and a change control process?

    In his defence, this developer is merely an internal company admin tool.

    FTFY

  • (cs) in reply to foo
    foo:
    Amtep:
    I like how our thoughtful submitter made sure that Martin would receive all 1137 notification emails. Craftsmanship like that is just not appreciated anymore these days.

    Don't be rediculous. The emails were sent to "allusers"

    Please unsubscribe me from this list.

  • (cs) in reply to jverd
    jverd:
    Wow, that's just precious!

    But I am confused about one thing. The rows were getting deleted one at a time, right? But if it's an INSTEAD OF trigger, how were they getting deleted at all?

    Addendum: I think the author should share some of his coffee with me. I spent a good 15 seconds trying to copy/paste the captcha and getting pissed off that it wasn't working. Brillant!

    What is happening to this site????

    The last 3 or 4 comments I have made have vanished without a trace (one made last night in this thread, and several in other threads over the last month or so), I do not think that i have been rude in any way....

    What's the deal, I'm even a registered user for what it is worth.

    Yours Yazeran

    Plan: To go to Mars one day with a hammer.

    PS I cant even see that I have made any posts when I go into stats on my user page....

  • (cs) in reply to pjt33
    pjt33:
    foo:
    Amtep:
    I like how our thoughtful submitter made sure that Martin would receive all 1137 notification emails. Craftsmanship like that is just not appreciated anymore these days.

    Don't be rediculous. The emails were sent to "allusers"

    Please unsubscribe me from this list.

    You didn't send that unsubscribe request to all users, I hope?

  • Line Commander (unregistered) in reply to Coyne
    Coyne:
    We have this utility that allows you to save rows into a table. While saving, you can use the option APPEND=YES. The default is, of course, APPEND=NO and so guess what happens if you save 20 rows without specifying APPEND=YES?
    /me cacls softly in the background.
  • nah (unregistered) in reply to Matt Westwood

    People. Please stop replying to this to allusers. You're filling everybodys mailboxes and making the server slow down.

  • QJo (unregistered) in reply to nah
    nah:
    People. Please stop replying to this to allusers. You're filling everybodys mailboxes and making the server slow down.
    Can you stop these messages coming to me? My inbox is filling up with messages I don't want.

    As I'm not sure whose job it is to address this matter, I am sending it to all users, in the hope that somebody out there is paying attention.

  • R (unregistered)

    I hope Matthew recognizes the loss in productivity that he just produced.

    Just think about it, a meeting reservation system that doesn't cancel reservations. How many meetings have been averted by this? How many meetings will the developers now have to attend?

  • L. (unregistered) in reply to AP2
    AP2:
    jverd:
    But I am confused about one thing. The rows were getting deleted one at a time, right? But if it's an INSTEAD OF trigger, how were they getting deleted at all?

    The INSTEAD OF had its own DELETE. At least that's what I've understood from the text.

    Errr . how would that not trigger the same trigger again in a never-ending loop of love and unity ?

  • L. (unregistered) in reply to L.
    L.:
    AP2:
    jverd:
    But I am confused about one thing. The rows were getting deleted one at a time, right? But if it's an INSTEAD OF trigger, how were they getting deleted at all?

    The INSTEAD OF had its own DELETE. At least that's what I've understood from the text.

    Errr . how would that not trigger the same trigger again in a never-ending loop of love and unity ?

    oops .. i meant pull the trigger ... or fire was it ? ^^

    Captcha: eros ... pull the trigger, definitely.

  • (cs)

    I have a 2 dumb questions.

    Let us assume that the specification for the system is "The client has to be notified when any change happens to their reservation" This includes the "Your reservation for resource Room 101 for Monday 5 September 2011 has been deleted" message.

    1. Why is getting that email sent at the point in time that the delete occurs a bad thing?

    2. What else would you do, have something polling to go "Is anything missing now? How about now? Now?" How else would you be able to act on a direct DELETE statement.

  • (cs) in reply to L.
    L.:
    Errr . how would that not trigger the same trigger again in a never-ending loop of love and unity ?
    The database takes special care to ensure that the trigger is not entered recursively. From the MSSQL docs (in the Remarks section; there's no convenient anchor nearby):
    If an INSTEAD OF trigger defined on a view executes a statement against the view that would ordinarily fire the INSTEAD OF trigger again, it is not called recursively. Instead, the statement is resolved as modifications against the base tables underlying the view. In this case, the view definition must meet all the restrictions for an updatable view. For a definition of updatable views, see Modifying Data Through a View.
    While that's not necessarily the right database docs, I'd be very startled if others worked any different: recursive entry of a trigger would be “A Very Bad Thing”.
  • Programming Hero (unregistered) in reply to L.

    Assuming SQL Server, triggers can be configured to be non-recursive and have varying levels of permitted nesting.

    No sane person would enable recursive triggers in their database.

  • Oslo (unregistered)
    I wish I could remember my mindset at the time, but I suspect it just seemed like a neat trick.

    Not coding till 3 am should fix that.

  • JamieC (unregistered) in reply to Lockwood
    Lockwood:
    I have a 2 dumb questions.

    Let us assume that the specification for the system is "The client has to be notified when any change happens to their reservation" This includes the "Your reservation for resource Room 101 for Monday 5 September 2011 has been deleted" message.

    1. Why is getting that email sent at the point in time that the delete occurs a bad thing?

    2. What else would you do, have something polling to go "Is anything missing now? How about now? Now?" How else would you be able to act on a direct DELETE statement.

    To answer your dumb questions

    1. It's not, and nobody that Im aware of said it was so.

    2. Sending emails (outside of perhaps server monitoring) is not the job of SQL Server. This is application logic and should happen in the application. ie

    3. User Chooses to delete a block of meetings

    a) begin atomic operation b) Delete records from database (better: Mark as cancelled in database) c) Compile 1 email (FFS!) with a list of the deleted meetings and send to originator of meeting / requestor of delete. 3) Sit back happy that you have a sensible solution.

  • AgTrier (unregistered)

    STORY OF THE ORIGINAL NON-DELETING DELETE:

    IBM executive to programmer: And don't forget that there must also be a way to delete files from the disk. Remember: we have top-secret data that we don't want to keep on the drive for longer than necessary.

    Programmer: OK, I found a very ressource-efficient way to delete files.

    Executive: Great! Here's your bonus.

    Programmer: Nevermind. Can I just take some of the old disks back home instead?

    Executive: Sure thing (sacks programmer's bonus)

  • Nagesh (unregistered)

    No problem here: this is preferred way to prevet colleagues from damaging database. Privilejes not to be extending possibly letting randumb operater annialate usefull meeting property of company.

    Please be not to be damnaging database in the future.

Leave a comment on “Confessions: The Non-deleting Delete”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article