• Guayo (unregistered)

    Ok... Maybe I'm just wake up in stupid mode but WTF is wrong with that trigger? It doesn't allow to insert or update customer_products which its cust_no and campaign_no doesn't have a record in the action table or that the matching record in action table is not a root action (It seem that a tree hierarchy is present in that table).
    Besides 2 unused vars... what's wrong with that?

  • Guayo (unregistered)

    ... the cursor isn't necessary for what it seems he want to verify (IF EXISTS(...) wold do that) but unless I missing something very obvious I wouldn't say this is a WTF.

  • It wasn't me (unregistered)

    There is something to be said about using a CURSOR for absolutely no reason. A IF EXISTS(SELECT * FROM ...) would work exactly the same.

  • AEDIS (unregistered)

    I won't have to use foreign keys ever again! Thanks Jason! ;-)

  • Michael K. Campbell (unregistered)

    It GRIEVES me to have to say this... but: FOREIGN KEY CONSTRAINT anyone? (i.e. part of a thing known in some parts of the universe as "Referential Integrity") Skip the EXISTS crap and skip the Trigger...

  • Guayo (unregistered)

    I'm curious... What's the syntax of the constraint (foreign key?) which is equivalent to this trigger?

  • Jason Strate (unregistered)

    IF EXISTS(SELECT * FROM ...) would work if the trigger itself was even necessary. The point of the trigger itself was to avoid having to add the column action_number to the table customer_products. This guy had some sort of thign for CURSORs they are in everything here.

  • rojohn (unregistered)

    Perhaps the person developer wanted the string database error to be presentable to the person user.

  • Guayo (unregistered)

    OK, the guy has a thing with cursors and cursor performance in SQL Server doesn't really shine, but... a WTF?... c'mon!... maybe he comes from a different RDBMS background where cursors are sometimes preferred over set operations.
    I do believe It's more a WTF believing that this kind of integrity rule can be do it with a foreign key.

  • Ron (unregistered)

    This one is awesome, it hurts my brain.

    Also, I don't think you can use a foreign key constraint for this. The business logic isn't quite as simple as that.

    This looks almost auto-generated. "for this person customer" ?

  • Lon (unregistered)

    Maybe I'm missing something, but the thing that makes me go wtf is, the comment about trying to only use only one table. That's like saying when I write programs I try to only use one object, or one variable...it's just shouldn't be the design goal.

  • Jeff S (unregistered)

    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)

  • Guayo (unregistered)

    @Jeff S

    1) Agreed, but still thinks that isn't a really WTF in itself...
    2) INSERTED pseudo-table does contain updated rows too.
    3) That's true indeed. The trigger as it is could allow an insert or update of a row in customer_products without the needed root action if the statement inserted or updated more than one row. So this trigger doesn't function well with such multiple row manipulation. We don't know if such multiple inserts/updates occurs in that system (so this would be a bug) or not.
    4) What you say is pure speculation and I don't see the point of implying a poorly normalized model. As far as I can see there isn't a hint of a badly designed model. It's just that in this case the business rule the trigger expresses can't be done with foreign keys constraints. Plain and simple.

  • JeffS (unregistered)

    i agree we tend to get a little picky around here to find faults with the code. and that's not the idea of a WTF -- a WTF literally should make you say "What the F**K!" as opposed to "I think it can be done better ... let me think about it" .

    for point 2 -- yes, you are correct!

    point 4 -- if i needed to have a FK reference to this tree only on the root, then the root has a different purpose than the rest of the tree and i would put it in another table, and put all the subnodes in another (recursive) table or whatever structure you need. tehn you can have your FK constraint directly to your root table. no trigger, no mess. if the root has different rules as opposed to the rest of the tree, it belongs in a different table.

    but overall, you're probably right -- maybe not a WTF but rather simply some "non-optimal code".


    Truly, though, anyone who uses cursors when set-based would work should be shot -- that point simply cannot be debated ! :)

  • Lon (unregistered)

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

    haha, lol, okay, that makes me feel better. :) I thought that the writer of the trigger had posted the comments.

  • Nick D (unregistered)

    OMG! imagine when the table fills up a bit. You'll be able to go make a cup of tea in the time it takes to insert a row!

  • Dave M. (unregistered)

    > You'll be able to go make a cup of
    > tea in the time it takes to insert
    > a row!

    How's that a bad thing? I charge by the hour.

  • Zka (unregistered)

    I agree Guayo. This is not so WTF in itself :)

  • Hassan Voyeau (unregistered)

    This is a WTF, it's wrong to write a trigger that doesn't cater for multiple inserts. This reminded me of a posr I read some time ago [ http://weblogs.sqlteam.com/tarad/archive/2004/09/14/2077.aspx ]

  • trigger happy (unregistered)

    I hate triggers... And using one to make sure that your data is valid is stupid... I say WTF.

  • Wim Hollebrandse (unregistered)

    The funniest things on here are the comments.

    People that ask things like:"WTF is wrong with that trigger?" My, oh my.

    WTF is wrong with it? It shouldn't bloody be there in the first place!!! Databases handle referential integrity pretty well without triggers. Primary and foreign key relationships, remember?

    Shocking. 'Shock and awe' springs to mind.

    </rant>

  • Milo (unregistered)

    I my mind, triggers should serve no purpose in a well design, referential database. If you have them, you KNOW you shouldn't.

    And please, spare the "I see nothing wrong with this."

    Like sticking a plaster (band-aid) over a broken leg, please, fix the problem, not the side-effect.

  • uzy (unregistered)

    JeffS comment about the root table: How do you reference a parent root node (as opposed to a parent subnode) from the subnodetable? Do you construct a subnodetable_with_root_parents and a subnodetable_with_subnode_patents? Or do you use a negative FK for root table? With referential Integrity??

  • Jeff S (unregistered)

    @uzy --

    One way to do this is: The "Root" table has just one level -- the Roots of the tree structure. Maybe the PK is RootID. Other tables can reference the root table via a standard FK relationship.

    The "subnode" table -- the rest of the tree, minus the root -- has a structure like this:

    RootID, NodeID, ParentID

    In which RootID references the roots table, and ParentID/RootID reference back to the subnode table itself on RootID/NoteID.

    An example of this --

    Suppose you have a project accounting system in which logically projects all belong to a company, and then from there they are a hierarichal structure. And other entities in your system might need to reference the Companies.

    Since "company" has a distinct meaning, seperate from the project structure, you wouldn't put it all in one table - you break out the companies into 1 table and the hierarhical projects in another. Logically, the whole thing is still 1 tree structure, but physcially you have broken it up.

    (sorry about the spelling -- i can't spell "hierarchy" to save my life)

  • Bustaz Kool (unregistered)

    Allow me to throw this on top of everything else...

    The CURSOR processes exactly ONE ROW!


    A multiple row Insert or Update potentially allows violations of thr RI.

  • mizd (unregistered)

    hi,

    some sort of RI can not be done with DRI (declarative referential integrity) which means using foreign key constraints.

    there are sometimes problems you can only solve with triggers.

    if you have a subcategory pattern, than no foreign key can help you.

    than you need a trigger.

    greets

  • Rick (unregistered)

    Came across this thread by chance, and thought I'd add my two cents. SQL Server was split from Sybase some years ago (check the Wikipedia page for full details), and Sybase didn't have foreign key relationships. You had to implement referential integrity using triggers.

    The upgrade path from Sybase to SQL Server was relatively painless, so you might simply be looking at old code ported from a Sybase app. I don't recall if Sybase included the EXISTS keyword, but I think it did include the option to process triggers row-by-row instead of set-based.

Leave a comment on “And I think I'll call it .... "Referential Integrity" !”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article