- Feature Articles
- CodeSOD
- Error'd
-
Forums
-
Other Articles
- Random Article
- Alex's Soapbox
- Announcements
- Best of…
- Best of Email
- Best of the Sidebar
- Bring Your Own Code
- Coded Smorgasbord
- Mandatory Fun Day
- Off Topic
- Representative Line
- News Roundup
- Editor's Soapbox
- Software on the Rocks
- Souvenir Potpourri
- Sponsor Post
- Tales from the Interview
- The Daily WTF: Live
- Virtudyne
Admin
Bring back capital punishment.
Admin
Buy a dog a bark yourself!
Admin
<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>
Admin
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.)
Admin
Nothing like a "person customer" entity, as opposed to ... I guess ... a robot customer, or an animal customer?
Admin
Alex,
you mis-spelled brillance.
Admin
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*#%...
Admin
Great job, guys. Sheer brilliance.
Another example of why I won't be doing business programming any time soon!
Admin
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.
Admin
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.
Admin
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!
Admin
maybe its written for some ancient version of oracle which doesn't have RI built-in?
Admin
It's amazing how someone can both know what they are doing and have no idea at the same time.
Admin
Forget what all the SQL Server experts say. Always use cursors - even when a simple select and conditional statement would do the trick.
Admin
"Cursors, foiled again."
Admin
Just a newbie question, but couldn't the same thing be accomplished (without iterating) with a select/join/exists type test?
Admin
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...)
Admin
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.
Admin
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...)
Admin
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...
Admin
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
Admin
this was posted, as comment when this was originally posted (see if a "classic" not posted by alex)
CAPTCHA : enterprisey
Admin
Alternately, this WTF could have been entitled "Who gives a FK?"
Admin
*bows in awe of your comment*
Admin
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).
Admin
Why?
Admin
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.
Admin
<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>
Admin
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.
Admin
We can just send this user to the "fuffycat" website for all their SQL/cursor needs:
Admin
Ate my link..... :P
http://www.fluffycat.com/SQL/Cursor-Oracle/
Admin
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%') LOOPv_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.
Admin
I concur.
Admin
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..."
Admin
Did I see Paula just go by
Admin
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...
Admin
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.
Admin
yeah but you wouldnt need to reference the friggen view in the first place if you just enforced a foreign key constraint
Admin
I hope you're serialized.
Admin
"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.
Admin
Wait until he has to localize the error messages.
Captcha: wtf.
Admin
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.
Admin
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.
Admin
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.
Admin
Well, it implies "only 1 null" and "unique" if the PK is on a nullable column.
Admin
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.
Admin
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?
Admin
In Oracle, primary keys cannot be null.
Admin
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.
Admin
<FONT face=Verdana>
</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>