Trigger/Event Driven Update

[StevenChen]

Hi,

I have 2 tables, table A, and B. Primary key in table A, and foreign key is in table B. I have a situation where primary key in table A could be changed due to nature of its design, is there a way to automatically update the foreign keys in table B whenever I change an entry in the primary key field?

thanks,

 

[alansidman]

Primary keys should be arbitrary and used exclusively for identifying particular records. Why should you be changing this.? I prefer to make this an autonumber that has no bearing on anything else. If you need a special identifier, then make that another field that is not part of the primary key. Otherwise, you are looking for continuing issues and manual changes or running update queries.

 

[StevenChen]

Primary keys should be arbitrary and used exclusively for identifying particular records. Why should you be changing this.? I prefer to make this an autonumber that has no bearing on anything else. If you need a special identifier, then make that another field that is not part of the primary key. Otherwise, you are looking for continuing issues and manual changes or running update queries.

Thank you for the input. I am aware of what you have said above. I am not very sure how to tackle this issue i have at hand. Perhaps a little more clarifying would help what I am trying to achieve. The company I work at has this software that allows users to input data, however, it's more like a free field (essentially text field). They have been running reports based on these inputs by extracting the data out of the system, then manually adjust them to what they need before running the report. The information in table A is client information, the primary key is the client code which is related to the client name. Each month, there is always one client or another that decides to change their legal name which then requires us to change the client code internally as well. Table B, where the report data stored at is reference on the client code.

 

[alansidman]

I don't know of any automatic change that you can employ. Given your scenario, I would suggest you do an update Query. How many of these do you have each month?

 

[StevenChen]

tblClient
ClientCode --Primary Key
ClientName
...
...

tblPlanDetail
ClientCode --Foreign Key
...
...

Primary key is set so that it's the first (x) digit of the client name + numeric identifier (01...).
Problem arises when client changes their legal name, then client code needs to be changed to reflect that. Without manually changing the client code in the plan detail, existing join query will no longer include those plans.

I haven't inputted any data into the database yet, because i am trying to lay the groundwork as neatly as possible. Whenever there is a change, I am concerned with 2 problems.
1. Can Ichange the client code in the tblPlanDetail without changing the PK in tblClient first?
2. Can I even change the ClientCode in tblClient if it's already being used a foreign in another table?

so I was asking if there is a way to whenever an update occur on ClientCode, then set ClientCode in tblPlanDetail = New.ClientCode where ClientCode = Old.ClientCode?

Again, thanks for your input, appreciate you taking the time and effort to understand my situation.

 

[xenou]

1. Can Ichange the client code in the tblPlanDetail without changing the PK in tblClient first?

You can. Of course, this means you can change it to the right thing or the wrong thing, or even no thing at all (null). If it is a true foreign key it should reference the primary key of an existing record in tblClient, or be Null. By setting referential integrity you can ensure the rules that would make it a true foreign key.

2. Can I even change the ClientCode in tblClient if it's already being used a foreign in another table?

In most implementations this is rare, and it could corrupt your data by tampering with the relationships between records in tables. However, Access does have a feature if you *explicitly* set up a primary key - foreign key relationship between tables, so that you can ensure referential integrity and cascade updates, which is to say, you can propagate changes in just the way you are describing. I have rarely used this feature myself but it seems to describe exactly what you want. Note that his is an all or nothing thing - you must update all the foreign keys wherever they are used, which means you can't forget any tables that would require the cascading updates.

see:http://grok.lsu.edu/Article.aspx?articleId=4604
Enforce Referential Integrity | Database Solutions for Microsoft Access

Honestly, in my experience, most companies that use this kind of "first four letters of a company name" naming convention (which is probably a mistake to begin with) just leave it the way it is, even if the company changes. So for instance "T001" might refer to the Transcontinental Railroad originally, and to the United Transcontinental Railroad later on (even though now the name starts with "U" and not "T").

 

[StevenChen]

Thank you for your input, also thanks for the link you have provided. I am somewhat new to Access, so i am not sure what cascade does, so i will need to do some research and reading on this before I ask more questions that sound like i am too reliance on others.

On the other note, I do agree that using first x digit of the client name + some # is a very bad way to keep track of things. Unfortunately, the data needs to extracted and audited before loaded into Access on monthly basis, so all the updated clients will not find any match in the existing client listing which will seems like these accounts are new when they're really not. I have no control over leaving the codes the way it is, apparently it was decided decade ago, and still being process this way. Sometimes, it's hard to tell someone, who could fire me, that what he/she is doing is just plainly wrong.

 

[xenou]

No problem. That's a common situation. Trying to match data from external sources to keys in your own tables is sometimes quite tricky.