• Richard Cutts (unregistered)

    Bring back capital punishment.

  • Paul Bowman (unregistered)

    Buy a dog a bark yourself!

  • (cs)
    Jake Vinson:

    One thing I absolutely hate about working with databases is when you have to use more than one table. Don't get me wrong, I'm usually able to shove all of my data in one table, but some times you just have no choice. Thanks to Jason Strate, I'm going to be prepared next time I use multiple tables. His colleague was able to figure out a way to ensure that no one goes adding rows willy-nilly. It's sheer brilliance!

    [image]


    <FONT face=Tahoma>NOT THOSE CURSORS AGAIN...

    how could anyone who know triggers and table joins do this?
    </FONT><FONT face=Tahoma>
    oh man...



    </FONT>
  • (cs)

    All your person root action customer campaign are belong to us. (I wish I could think of something more witty or sensible, but the data do not, as yet, appear to support any such conclusions.)

  • HardCode (unregistered)

    Nothing like a "person customer" entity, as opposed to ... I guess ... a robot customer, or an animal customer?

  • (cs) in reply to HardCode

    Alex,
    you mis-spelled brillance.

  • Bustaz Kool (unregistered)

    What a distinctly unique approach.  I, for one, am delighted to see a database engineer who is unconstrained by convention.  His primary concern here is the application of key business rules.  Instead of simply reading the answer out of a text book or a set of references, he was able to commit to solid principles which allowed the solution to coalesce.

    You must grant me, had this problem done been solved in a normal fashion, it would have been a real clustered f*#%...

  • nneonneo (unregistered)

    Great job, guys. Sheer brilliance.

    Another example of why I won't be doing business programming any time soon!

  • bob (unregistered)

    I worked with a guy who SWORE by doing all the refInt via triggers because (near quote) "The referential integrity error message are terrible! With triggers, I can put in really nice errors that explain the problem."

    Apparently- the concept of making your code handle exceptions, and making it so that ref. integrity can't be violated to begin with was lost on him.

  • MisterPleasant (unregistered)

    We don't need no stinkin' foreign key constraints!

    In the old pre-SQL Server 6.x days when constraints were not available, real men knew how to code triggers to enforce referential integrity.  However, this ain't how it was done.  Double shame on this yahoo for using a cursor in a trigger.  What RDBMS does he think he is working with... Oracle?  Modern RDBMS's support set based processing.  Like for the last 15+ years.

    Jeez.

  • (cs)

    I'm pretty sure this is because old versions of SQL Server didn't have built-in referential integrity.  Tools like ERwin would generate code exactly like this when creating the database from the physical model.

    It's not really a WTF, it's just old code brought forward into an RDBMS that now has this functionality built in.

    UPDATED: Duh, the ERwin code would do a SELECT, not use a CURSOR.  WTF indeed!

  • Mark H (unregistered) in reply to xrT
    xrT:

    <font face="Tahoma">NOT THOSE CURSORS AGAIN...

    how could anyone who know triggers and table joins do this?
    </font><font face="Tahoma">
    oh man...
    </font>



    maybe its written for some ancient version of oracle which doesn't have RI built-in?
  • Ron (unregistered)

    It's amazing how someone can both know what they are doing and have no idea at the same time.

  • (cs)

    Forget what all the SQL Server experts say.  Always use cursors - even when a simple select and conditional statement would do the trick.

  • (cs) in reply to Mark H
    Anonymous:
    xrT:

    <font face="Tahoma">NOT THOSE CURSORS AGAIN...

    how could anyone who know triggers and table joins do this?
    </font><font face="Tahoma">
    oh man...
    </font>



    maybe its written for some ancient version of oracle which doesn't have RI built-in?
    Yes, the one at Delphi. (Not Borland Delphi... the ORIGINAL Oracle at Delphi.)

    "Cursors, foiled again."
  • db-newbie (unregistered)

    Just a newbie question, but couldn't the same thing be accomplished (without iterating) with a select/join/exists type test?

  • (cs)

    Jake Vinson:
    Don't get me wrong, I'm usually able to shove all of my data in one table, but some times you just have no choice.

    As I'm doing SQL only on rare occasions, I'm not quite sure I can follow what's going on in this trigger.  Why did they heap the Self-Referential Identity on top of the Referential Integrity?  I mean what is that

    where [action].action_number = [action].root_action_number

    supposed to do?  Is the real WTF here that the [action] table is not normalized, I mean, that they put almost all of their data in the [action] table but just could not seem to fit the [customer_product] stuff in there too?  Could they have turned that Self-Referential Identity into a Referential Integrity constraint between the [root_action] and [sub_action] tables?  (I'm just guessing here, I might be completely misunderstanding something...)

  • (cs)

    Well, at least he DOES checks for foreign constraints.

    I'm too used to using no constraints nor foreign keys, and then coding the website that relies on that database so that it handles correctly the inconsistencies instead of pucking every time it finds something not normalized.

    Like an article whose author no longer exists on the database. The "author" field has an author_id instead of a NULL, and there is no row with that id on the author table. So what? There *was* an author at some point in time. It just that his data is no longer reachable. Of course this is bad, but for small sites it works fine :) Just make sure that you won't issue new authors with old ids.

  • Vincent (unregistered) in reply to Phil the ruler of heck

    Doesn't the where [action].action_number = [action].root_action_number mean that you test the "foreign key" only in that case? How do you do a foreign key that is only checked if two fields are equal? (of course if you need to do that it probably means that the design of the database is wrong, but...)

  • (cs) in reply to db-newbie

    db-newbie:
    Just a newbie question, but couldn't the same thing be accomplished (without iterating) with a select/join/exists type test?

    I'm not exactly an expert in these matters either, but...  almost certainly, the obvious WTF would seem to be that opening a cursor, actually requesting the data, and then iterating over the returned records is pointless if you just want to know whether or not there is at least one record matching the WHERE clause.  The Sybase way to do it would probably have been

    IF EXISTS (SELECT 1 FROM ... WHERE ...)

    if I recall that correctly, other DBMS's might use a slightly different syntax...

  • Rick DeBay (unregistered) in reply to Enric Naval
    Enric Naval:

    Well, at least he DOES checks for foreign constraints.

    I'm too used to using no constraints nor foreign keys, and then coding the website that relies on that database so that it handles correctly the inconsistencies instead of pucking every time it finds something not normalized.

    Like an article whose author no longer exists on the database. The "author" field has an author_id instead of a NULL, and there is no row with that id on the author table. So what? There *was* an author at some point in time. It just that his data is no longer reachable. Of course this is bad, but for small sites it works fine :) Just make sure that you won't issue new authors with old ids.



    A foreign key from the article to the author would have PREVENTED some schmuck from deleting the author from the database while his articles were still in the system.  Or you could let it delete the articles as well if you want to purge the system of the author and all his articles, or the database could even set the "author" field in the article to NULL.  It's all up the how the foreign key is defined.  What's worse is having a random pointer in the article.

    Rick DeBay
  • Bob Smith (unregistered)
    Jeff S:
    lots of WTF's:

    1) use of cursor instead of NOT EXISTS() as mentioned
    2) the trigger is for updated and inserted -- updated is ignored, only inserted rows are checked
    3) the trigger checks to see if at least 1 row in inserted is valid. if that 1 row is valid, the trigger succeeds for all rows. It needs to check all rows, as INSERTED can be a set not just 1 row.
    4) foreign key constraints can be comprised of more than 1 column. logically, if this constraint cannot be expressed as a simple FK constraint then the table structure is probably not normalized.

    It is kind of hard to figure out the true business rule this person is trying acheive here, but I'm sure there's lots of WTF's all over the place in this system based on this trigger.

    (lon -- he was being sarcastic saying everything should always be stored in 1 table)


    this was posted, as comment when this was originally posted (see if a "classic" not posted by alex)

    CAPTCHA : enterprisey
  • Philbert Desanex (unregistered)

    Alternately, this WTF could have been entitled "Who gives a FK?"

  • snoofle (unregistered) in reply to Philbert Desanex

    Anonymous:
    Alternately, this WTF could have been entitled "Who gives a FK?"

    *bows in awe of your comment*

  • David (unregistered) in reply to Richard Cutts

    Umm Yeah they never got rid of capital punishment.  I think you mean corporal punishment (after all it is rather harsh to kill somebody for bad code).

  • Wants to know (unregistered) in reply to David

    Anonymous:
    Umm Yeah they never got rid of capital punishment.  I think you mean corporal punishment (after all it is rather harsh to kill somebody for bad code).

    Why?

  • PikeStrider (unregistered) in reply to HardCode

    Anonymous:
    Nothing like a "person customer" entity, as opposed to ... I guess ... a robot customer, or an animal customer?

     

    I am guessing that this is to distinguish between individuals and retailers. If you have a company that sells through direct and also via retailers you would need this distinction.

  • some moron (unregistered) in reply to David
    Septic?:
    Umm Yeah they never got rid of capital punishment.  I think you mean corporal punishment (after all it is rather harsh to kill somebody for bad code).


    <font face="Verdana">maybe our friend doesn't come from your country?

    we don't all execute "bad people".

    i wish i knew more sql so i could figure out what's going on today... i'm guessing that a cursor iterates along a row? why is it frowned upon?
    </font>
  • (cs)

    A referential integrity constraint generally requires a primary key constraint on the referenced table (maybe some database systems handle that differently...). Which, in fact, implies "not null" and "unique". This trigger might be necessary because those requirements are not met. Which would in turn be another WTF, but that's another story.

  • Rob Banzai (unregistered)

    We can just send this user to the "fuffycat" website for all their SQL/cursor needs:

  • (cs) in reply to Rob Banzai

    Ate my link..... :P

    http://www.fluffycat.com/SQL/Cursor-Oracle/

  • (cs) in reply to rbanzai
    rbanzai:
    Ate my link..... :P

    http://www.fluffycat.com/SQL/Cursor-Oracle/


    Well, PL/SQL allows for a more direct approach:

    FOR v_ampie IN (SELECT film_title FROM film_table WHERE film_title LIKE 'American Pie%') LOOP
     v_american_pie_count := v_american_pie_count + 1; /* just an example, normally we would somehow process v_ampie */
    END LOOP;


    No need to explicitely declare, fetch and close the cursor.
  • (cs)
    Anonymous:
    "Who gives a FK?"

    Best comment yet. I want you to start writing the headlines. :)

    I concur.

  • (cs)
    Anonymous:
    http://www.drarok.com/Stored.txt

    Seriously, that made me scream out loud. That's scarier than most horror films I've seen.

    "Who gives a FK?"

    Best comment yet. I want you to start writing the headlines. :)



    Heh. That's normal for me. My co's enterprisey WTF has those kind of Stored Procs as the base minimum. You should see the core of the system : 4 to 10 times more complex and worse, undocumented.
    One can find random comments like - "why in the devil's name do we have to change this ?"
    Code repository comments include - "Kent added..."
  • (cs)

    Did I see Paula just go by

  • my name is missing (unregistered) in reply to byte_lancer

    At my previous job we had a case-tool managed AS400 "database" with a DB2 relational database engine on top of it which all web apps were forced to use. Since the AS400 RPG code reads/writes the "tables" without going through the DB2 engine, any referential integrity you might want in a database was ignored. Thus we had to code around such wonderous things as multiple rows returned on primary key queries, non-unique unique columns, seven kinds of dates (and none of them date type columns), etc. So this is quite normal-but-still-stupid looking to me...

    Amazing what crap people come up with...


  • (cs) in reply to some moron
    Anonymous:
    Septic?:
    Umm Yeah they never got rid of capital punishment.  I think you mean corporal punishment (after all it is rather harsh to kill somebody for bad code).


    <font face="Verdana">maybe our friend doesn't come from your country?

    we don't all execute "bad people".

    i wish i knew more sql so i could figure out what's going on today... i'm guessing that a cursor iterates along a row? why is it frowned upon?
    </font>


    Not only is the cursor iteration unneeded, but the entire trigger logic is unneeded.  What you're supposed to do is make the referential integrity constraint part of the table definition, something like:

    constraint some_constraint foreign key (x_key) references table_x(x_key) on delete cascade

    ("on delete" says what to do if someone tries to delete the row in table_x being referenced; similarly "on update")

    In this case, the campaign must match a "root action" i.e. a row in the 'action' table for which action_number = root_action_number, so you create a view that embodies that condition:

    create view root_action as select * from action where action_number = root_action_number

    and then reference the root_action view in the customer_products constraint.

  • Bob Smith (unregistered) in reply to emurphy
    emurphy:
    Anonymous:
    Septic?:
    Umm Yeah they never got rid of capital punishment.  I think you mean corporal punishment (after all it is rather harsh to kill somebody for bad code).


    <font face="Verdana">maybe our friend doesn't come from your country?

    we don't all execute "bad people".

    i wish i knew more sql so i could figure out what's going on today... i'm guessing that a cursor iterates along a row? why is it frowned upon?
    </font>


    Not only is the cursor iteration unneeded, but the entire trigger logic is unneeded.  What you're supposed to do is make the referential integrity constraint part of the table definition, something like:

    constraint some_constraint foreign key (x_key) references table_x(x_key) on delete cascade

    ("on delete" says what to do if someone tries to delete the row in table_x being referenced; similarly "on update")

    In this case, the campaign must match a "root action" i.e. a row in the 'action' table for which action_number = root_action_number, so you create a view that embodies that condition:

    create view root_action as select * from action where action_number = root_action_number

    and then reference the root_action view in the customer_products constraint.



    yeah but you wouldnt need to reference the friggen view in the first place if you just enforced a foreign key constraint
  • (cs) in reply to seymore15074
    seymore15074:

    I concur.


    I hope you're serialized.
  • (cs)

    "person customer" -- reminds me of that line in CSN's "Southern Cross": 

    Searching for that woman girl
    Who knows love can endure

    As opposed to a man girl, I guess.

  • Dash Riprock (unregistered) in reply to FredSaw

    Wait until he has to localize the error messages.

    Captcha: wtf.

  • Ryan (unregistered) in reply to Enric Naval

    Enric Naval wrote the following post at 06-30-2006 1:07 PM:

    ...The "author" field has an author_id instead of a NULL, and there is no row with that id on the author table. So what? There *was* an author at some point in time. It just that his data is no longer reachable. Of course this is bad, but for small sites it works fine :)

    Except when the invoice_header is missing for a bunch of invoice_lines, wihch would be really bad for even a "small site". Referential integrity is there to prevent logically corrupt data from entering the database. Even small sites require this. Not using RI for a DB is just as bad as putting "On Error Resume Next" on top of every ASP page you write.

    Enric, you're essentially advocating two extremely dangerous programming practices. Not validating input and improperly handling or ignoring errors are the #1 and #2 causes of security vulnerabilties, and probably also the #1 and #2 causes of non-security bugs in my experience. The "size" of the site doesn't really matter much when it's 0wned or horribly broken because of bad data. Think of the issues that could arise from not using DR between a "users" table and a "user_permissions" table, even in a simple application.

  • (cs) in reply to Bob Smith
    Anonymous:

    yeah but you wouldnt need to reference the friggen view in the first place if you just enforced a foreign key constraint


    I think you're wrong about this (unless FK constraints allow more options than I realize).  By implication (and if this isn't a WTF in itself), the 'action' table is conceptually a set of trees, with the 'root_action_number' column defining parent/child relationships, e.g.

    action_number / description / root_action_number
    1 / Foo / 1
    2 / Bar / 1
    3 / Baz / 2
    4 / Qux / 2
    5 / Abc / 5
    6 / Def / 5
    7 / Ghi / 5
    8 / Jkl / 6
    9 / Mno / 6
    10 / Prs / 7
    11 / Tuv / 7
    12 / Wxy / 7

    Note that the business logic in the original WTF does not let you reference any row in this table, only one that is a root of a tree, i.e. its root_action_number points to its own action_number (so in my example, only 1 and 5 are valid targets).  Hence a view that presents only those rows.

  • MB (unregistered) in reply to emurphy

    emurphy:

    <...snip...>
    Note that the business logic in the original WTF does not let you reference any row in this table, only one that is a root of a tree, i.e. its root_action_number points to its own action_number (so in my example, only 1 and 5 are valid targets).  Hence a view that presents only those rows.

    Which is why the trigger is needed. AFAIK, you can't have foreign keys on views. So, basically, the choice here is change the data model (which may be possible, but there's not enough info to know if that's the best way here...a heirarchial table is unusual, but necessary/desirable in some cases) or enforce referential integrity through a trigger.

    Now, the opening of a cursor to check for a row is inexcusable, but otherwise, I don't see a problem. Just change it to a NOT EXISTS (SELECT 1...) statement and you're good to go.

  • MB (unregistered) in reply to ammoQ

    ammoQ:
    A referential integrity constraint generally requires a primary key constraint on the referenced table (maybe some database systems handle that differently...). Which, in fact, implies "not null" and "unique". This trigger might be necessary because those requirements are not met. Which would in turn be another WTF, but that's another story.

    Well, it implies "only 1 null" and "unique" if the PK is on a nullable column.

  • MB (unregistered) in reply to Bob Smith

    Anonymous:
    Jeff S:
    lots of WTF's:

    1) use of cursor instead of NOT EXISTS() as mentioned
    2) the trigger is for updated and inserted -- updated is ignored, only inserted rows are checked

    3) the trigger checks to see if at least 1 row in inserted is valid. if that 1 row is valid, the trigger succeeds for all rows. It needs to check all rows, as INSERTED can be a set not just 1 row.
    4) foreign key constraints can be comprised of more than 1 column. logically, if this constraint cannot be expressed as a simple FK constraint then the table structure is probably not normalized.

    It is kind of hard to figure out the true business rule this person is trying acheive here, but I'm sure there's lots of WTF's all over the place in this system based on this trigger.

    (lon -- he was being sarcastic saying everything should always be stored in 1 table)



    this was posted, as comment when this was originally posted (see if a "classic" not posted by alex)

    CAPTCHA : enterprisey

    The updated table is the same as inserted. You just have a deleted table as well. Of course, he could have saved some work by checking for IF UPDATE(customer_number) OR UPDATE(campaign_number), but still that's not too terribly bad.

    The set based and cursor comments are both justified though...AAMOF, I didn't catch the fact that he wasn't looping the cursor.

  • Dazed (unregistered) in reply to MB
    Anonymous:

    The set based and cursor comments are both justified though...AAMOF, I didn't catch the fact that he wasn't looping the cursor.

    Cursors have featured more than once on this site. Having never used them, or felt the need to, could someone give an example of where they are actually appropriate?

  • (cs) in reply to MB
    Anonymous:

    ammoQ:
    A referential integrity constraint generally requires a primary key constraint on the referenced table (maybe some database systems handle that differently...). Which, in fact, implies "not null" and "unique". This trigger might be necessary because those requirements are not met. Which would in turn be another WTF, but that's another story.

    Well, it implies "only 1 null" and "unique" if the PK is on a nullable column.



    In Oracle, primary keys cannot be null.
  • (cs) in reply to Dazed
    Anonymous:
    Anonymous:

    The set based and cursor comments are both justified though...AAMOF, I didn't catch the fact that he wasn't looping the cursor.

    Cursors have featured more than once on this site. Having never used them, or felt the need to, could someone give an example of where they are actually appropriate?



    Consider a table with 10^9 records, and your program wants to process them all (e.g. write them to a tape drive). A "result set" would be too large for the computer's memory, but a cursor is just an interator over those records, so it doesn't require much memory.

  • (cs)

    <FONT face=Verdana>

    I don't get it - almost all of the stored procedures I have at work are like this - most are worse!

    Where's the WTF? The fact that I can't see one after having so many shoved down my throat over the last year?

    http://www.drarok.com/Stored.txt

    Really, what's the point in that SP!?!?
    </FONT>

    <FONT face=Verdana>I've recently had a small project with a little startup. The owner, newly graduated, had a bunch of those insert procedure. When I have asked him why he would do this, he then replied "This is mainly to free the client program, because the [customer's] computers are not very performant."</FONT>

    <FONT face=Verdana>He added to that "when we have this approach, it will be possible to update the procedure to proceed further validations, manage errors or exceptions, things that the front-end cannot manage. "</FONT>

    <FONT face=Verdana>I'm always in awe with what the school system produce.</FONT>

Leave a comment on “Classic WTF - And I think I'll call it .... &quot;Referential Integrity&quot; !”

Log In or post as a guest

Replying to comment #79975:

« Return to Article