• Replies 9 replies
  • Subscribers 412 subscribers
  • Views 56 views
  • Users 0 members are here
Related

Referential Integrity for TX_CUST_KEYWORD values

Larry Brindise
$organization

I have to assume my discovery is by design.

If I create an entry in "T_KWCODED_VALUES" and specify a value for the "key_value" field, then refer to that value by assigning it to a customer within the "TX_CUST_KEYWORD" table, then change the original value of the "key_value" field in "T_KWCODED_VALUES", then the "key_value" field in "TX_CUST_KEYWORD" is not automatically updated to match the modified value.

I am wondering how this is typically handled?  I am building some custom web code, and I want to join these two tables with the join clause partially conditioned by the "key_value" field.  But if I go into the T_KWCODED_VALUES table and change the value, then the attributes on the customer are not automatically updated, and so my join "breaks".

Is there a better way to do this?

Thanks!

  • $organization

    Hi Larry -

    You've found one of my favorite things to growl at... Unfortunately, there isn't a better way to do that join that I know of (oh please let me be wrong), and yes, they can completely get off if someone changes the value. 

    The only fix I've found is to write a sql script that runs nightly and checks against modified values and then updates them with the proper one. 

    - Heather

  • Former Member
    Former Member $organization
    Hi Larry It's certainly correct that that's how it works - the values in T_KWCODED_VALUES are only used to populate the dropdown, and what goes into TX_CUST_KEYWORD is the literal value, rather than a reference back to T_KWCODED_VALUES, so there is no persistent link. But since that is the case, that means that it's fairly safe to update the values in TX_CUST_KEYWORD yourself, if you need to change them, and it would be a simple task to write an update utility that you could run manually whenver you changed a literal value in T_KWCODED_VALUES. ie this sort of thing: Update TX_CUST_KEYWORD set key_value = 'New Value' where keyword_no = my_keyword_no and keyvalue = 'Old Value'
  • $organization

    I built a SQL update trigger for the T_KWCODED_VALUES table to enforce the referential integrity. If anyone's interested, ping back and I'll forward.

  • Former Member
    Former Member $organization

    I would like a copy of that sql.

     

    Thanks, Trudy Guest,

    ArtTix Systems Administrator

    801.323.6969

    From: Tessitura Development Forum [mailto:forums-development@tessituranetwork.com] On Behalf Of Larry Brindise
    Sent: Wednesday, July 27, 2011 9:32 AM
    To: Trudy Guest
    Subject: Re: [Tessitura Development Forum] Referential Integrity for TX_CUST_KEYWORD values

     

    I built a SQL update trigger for the T_KWCODED_VALUES table to enforce the referential integrity. If anyone's interested, ping back and I'll forward.

    From: Larry Brindise <bounce-larrybrindise9173@tessituranetwork.com>
    Sent: 7/26/2011 4:25:53 PM

    I have to assume my discovery is by design.

    If I create an entry in "T_KWCODED_VALUES" and specify a value for the "key_value" field, then refer to that value by assigning it to a customer within the "TX_CUST_KEYWORD" table, then change the original value of the "key_value" field in "T_KWCODED_VALUES", then the "key_value" field in "TX_CUST_KEYWORD" is not automatically updated to match the modified value.

    I am wondering how this is typically handled?  I am building some custom web code, and I want to join these two tables with the join clause partially conditioned by the "key_value" field.  But if I go into the T_KWCODED_VALUES table and change the value, then the attributes on the customer are not automatically updated, and so my join "breaks".

    Is there a better way to do this?

    Thanks!




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Development Forum. You may reply to this message to post to the Development forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

  • $organization in reply to Larry Brindise

    Hi Larry - 

    Is this something that you've put on the t_kwcoded_value table itself? If so, just watch out for Tessi updates which *could* overwrite/drop and recreate the table/etc.

    Other than that, sounds like a great idea. Would you mind posting the sql to your profile for sharing?

    Thanks!

    Heather

  • $organization in reply to Former Member

    I don't want to post here yet because it is so new.  If you are comfortable with testing it a bit and assuming all risk, I'd be happy send it to you directly for your review.  Send me an e-mail to lbrindise@thephil.org and I'll reply with the code. ( I'm pretty sure that if I reply to the e-mail that the Tessitura Forum sent me, it will be posted here, which would be premature.)

    If you and any others review/test it, then I'll feel comfortable posting it here.  (Are there any Tessitura Network issues/rules with posting code?)

    I could put all the disclaimers in the world on it; "use at your own peril", etc, but even with that, I don't want to put something out there that does damage...not that I expect it to do damage...;)

    I will have this in production within a few days, so I'm confident I'll have something usable shortly. 



    [edited by: Larry Brindise at 10:57 AM (GMT -6) on 27 Jul 2011] Fixed my e-mail adderss.
  • $organization in reply to Heather Laidlaw Kraft (she/her)

    Yes, that's the table the trigger is on.  I've named it

    LT_T_KWCODED_VALUES_RefUpd_TX_CUST_KEYWORD

    "LT" for "local trigger".

  • $organization in reply to Larry Brindise

    Corrected e-mail

    lbrindise@thephil.org

    sorry for the extra quote in the link....I have a hair-trigger pinky on that single-quote button!

  • Former Member
    Former Member $organization

    Making changes to standard Tessitura tables or procedures, including adding local triggers, is not something that the Network endorses or recommends.  As Heather noted, these types of changes can be overwritten with a version patch or upgrade and such changes can lead to unexpected, and perhaps undesirable, results.  While we understand the reason you have created the local trigger described in this thread, I thought it best to reinforce the Network’s stance to ensure there was no confusion.

     

    Bob

     

     

    From: Tessitura Development Forum [mailto:forums-development@tessituranetwork.com] On Behalf Of Larry Brindise
    Sent: Wednesday, July 27, 2011 12:02 PM
    To: Robert Bell
    Subject: Re: [Tessitura Development Forum] Referential Integrity for TX_CUST_KEYWORD values

     

    Yes, that's the table the trigger is on.  I've named it

    LT_T_KWCODED_VALUES_RefUpd_TX_CUST_KEYWORD

    "LT" for "local trigger".

    From: Heather Kraft <bounce-heatherlaidlawkraft3507@tessituranetwork.com>
    Sent: 7/27/2011 10:52:11 AM

    Hi Larry - 

    Is this something that you've put on the t_kwcoded_value table itself? If so, just watch out for Tessi updates which *could* overwrite/drop and recreate the table/etc.

    Other than that, sounds like a great idea. Would you mind posting the sql to your profile for sharing?

    Thanks!

    Heather




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Development Forum. You may reply to this message to post to the Development forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!