Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2014
    Posts
    2

    Unanswered: best approach to update all dependent tables.

    Code:
    Table 1:-
    Cust_id,Cust_Nm,cust_sts
    10,        Sir,         A
    20,        Lady,      A
    
    Table 2:-
    Cust_id,rel_mgr,mgr_nm,status
    10,        HH,      Hou,      A
    20         AA,      Tee,       A
    
    Table 3:-
    Cust_id,Account_id,Acct_nm,Acct_sts
    10,       2000,         Savings,  A
    20,       3000,         Checking, A
    Above all three tables are dependent each other. Whenever a change happen(update) in one of the table then need to update the corresponding records in child and parent tables. Tried triggers to do this. due to performance issue I'm trying different approaches. Any ideas would be great.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Some issues.

    (1) You should normalize tables more, especially reconsider the design of Table 2 and 3.
    The table 2 and 3 might be able to decompose(normalize) more, then some update propagations might be removed.


    (2)
    Whenever a change happen(update) in one of the table then need to update the corresponding records in child and parent tables.
    A change of which column of which table should propagate to which column of which table?
    For example:
    I thoght that update of Cust_Nm or cust_sts in Table 1 need not to propagae to any other table.

    So, I want to recommend you to make a complete list of update propagations, like...
    Code:
    Whenever update   | then update 
    Table   |column   |Table1  |column1  |Table2  |column2  | so on...
    --------+---------+--------+---------+--------+---------+--------
    Table a |column b |Table x |column y |Table u |column v | 
    Table c |column d |Table j |column k | 
    ...
    then, you and others could share your requirements more exactly.


    (3)
    Tried triggers to do this.
    What triggers did you tried?
    Could you publish the codes(or commands?) by which you made the triggers?

  3. #3
    Join Date
    Oct 2014
    Posts
    2
    Yes. You are right. i missed a column in Table 3. it should be.
    Code:
    Table 3:-
    Cust_id, rel_mgr, Account_id,Acct_nm,Acct_sts
    10,        HH            2000,         Savings,  A
    20,        AA             3000,         Checking, A
    1.When cust_sts in table 1 updated with value 'I' then update the sts in table 2 and table 3 to 'W'
    2.When status in table 2 updated with value 'I' then update the sts in table 1 and table 3 to 'W'
    3.When acc_sts in table 3 updated with value 'I' then update the sts in table 1 and table 2 to 'W'

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Doesn't make sense as to why you would perform these updates, but they should be easily handled in after update trigger. As was already asked lets see your trigger attempt and tell us more than it didn't work.
    Dave

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •