Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
cmschick
Starting Member
20 Posts |
 Posted - 2006-01-13 : 15:15:15
|
| Does anyone know how I can do the following?Pull in related records with a common ID AND still be able to update the records in their respective tables?I can get the records in via a nested INNER JOIN but I cannot update the records with that join because of referential integrity rules. I need to be able to do this programmatically from VB 6.0... Can I do this with nest Select statements instead? Please help..Here is my nested inner join sql statement:"SELECT ApptFaxDetail.*, FaxDetail.*, Phone.PhoneNumber, Appointment.FacilityID, Appointment.ApptDate, Appointment.ApptTypeID, Phone.PhoneTypeID, Facility.FacilityNameFROM ((((FaxDetail INNER JOIN Phone ON FaxDetail.PhoneID = Phone.PhoneID) INNER JOIN ApptFaxDetail ON FaxDetail.FaxDetailID = ApptFaxDetail.FaxDetailID) INNER JOIN Appointment ON ApptFaxDetail.AppointmentID = Appointment.AppointmentID) INNER JOIN FacilityPhone ON Phone.PhoneID = FacilityPhone.PhoneID) INNER JOIN Facility ON Appointment.FacilityID = Facility.FacilityIDWHERE ApptFaxDetail.AppointmentID="My ref integrity relationships look like this...Table: Phone(PK = PhoneID)[Relationship: Phone.PhoneID - FacilityPhone.PhoneID]Table (Junction): FacilityPhone(FK = PhoneID)(FK = FacilityID)[Relationship: FacilityPhone.FacilityID - Facility.FacilityID]Table: Facility(PK = FacilityID)[Relationship: Facility.AppointmentID - Appointment.AppointmentID]Table: Appointment(PK = AppointmentID)[Relationship: Appointment.AppointmentID - ApptFaxDetail.AppointmentID}Table: ApptFaxDetail(PK = ApptFaxDetailID)[Relationship: ApptFaxDetail.FaxDetailID - FaxDetail.FaxDetailID](PK = FaxDetailID)Thanks!The Schickster |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-01-13 : 15:31:24
|
| Are you creating this as a view? Ideally, updates should be done through store procedures rather than directly to tables or views.If you insist on doing this, then try putting an INSTEAD OF trigger on your view that will distribute data inserts and updates to the base tables. |
 |
|
|
cmschick
Starting Member
20 Posts |
Posted - 2006-01-13 : 17:10:02
|
| Hi Blindman,Unfortunately this is dynamic sql from an Access front end. There is a large framework in place (Access classes) that bind SQL data to the form controls on the fly.Anyway, can you show me an example of how to do that? Thanks.The Schickster |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-01-15 : 11:33:16
|
quote:
Originally posted by cmschick
Unfortunately this is dynamic sql from an Access front end.
As my auto mechanic likes to say, "Well THERE'S your problem."Interfaces, whether asp, Access, VB, or whatever, should interact with the database through stored procedures, or failing that, through views. If you insist on by-passing best practices by sending dynamic SQL (not usually a good solution), then you will need to incorporate logic within your interface to intercept the action and update the appropriate underlying tables.I really encourage you to take your SQL statement and convert it to a view, and then you can query it from Access just as if it was a table. |
|
|
|
cmschick
Starting Member
20 Posts |
Posted - 2006-01-20 : 00:23:08
|
| Okay, I took your advice. I am using a newly created view. It was simple enough and it does not break the existing design, now what do we do?I have a multitable view that retrieves the data I need. Now, how do I update it? Thanks.The Schickster |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-01-20 : 10:48:08
|
| If it is not itself an updatable view (depends upon complexity), then create an INSTEAD OF trigger on the view that intercepts any data modifications and distributes the changes to the underlying tables. Look of triggers in Books Online. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-01-20 : 11:13:42
|
| What blindman said ...... probably not a problem, but note that if you have Computed columns in the underlying tables that the View query's there are some restrictions on what you can do. My aging brain is struggling to remember the grief we had when we did it, so let me know if you DO have computed columns and I'll do dig out the info, otherwise I'll just let it continue to rust!Kristen |
|
|
|
|
|
|
|
|
Cannot update records with nested inner joins