Updating Multiple Tables in a View

  • Article

You can update multiple base tables from a view. When your view combines two or more tables, you set properties to ensure that only the many side of the view query is updateable.

Views are updated on a table-by-table basis. You must ensure that for each table accessed in a view, the key field set is a unique key for both the view result set and the base table.

To make a multitable view updateable

  • In the View Designer, choose the Update Criteria tab, and then select the tables and field names you want to update.

    -or-

  • Use the DBSETPROP( ) function.

In most cases, the default values provided by Visual FoxPro prepare a multitable view to be updateable, even when you create the view programmatically. The following code example creates and explicitly sets properties to update a two-table view. You can use this example as a guide for customizing update property settings on a view.

Updating Multiple Tables in a View

Code Comments 
CREATE SQL VIEW emp_cust_view AS ;
   SELECT employee.emp_id, ;
   employee.phone, customer.cust_id, ;
   customer.emp_id, customer.contact, ;
   customer.company ;
   FROM employee, customer ;
   WHERE employee.emp_id = customer.emp_id
 Create a view that accesses fields from two tables. 
DBSETPROP('emp_cust_view', 'View', 'Tables',
'employee, customer')
 Set the tables to be updated. 
DBSETPROP('emp_cust_view.emp_id', 'Field', ;                'UpdateName', 'employee.emp_id')
DBSETPROP('emp_cust_view.phone', 'Field', ;                'UpdateName', 'employee.phone')
DBSETPROP('emp_cust_view.cust_id', 'Field', ;             'UpdateName', 'customer.cust_id')
DBSETPROP('emp_cust_view.emp_id1', 'Field', ;             'UpdateName', 'customer.emp_id')
DBSETPROP('emp_cust_view.contact', 'Field', ;             'UpdateName', 'customer.contact')
DBSETPROP('emp_cust_view.company', 'Field', ;             'UpdateName', 'customer.company')
 Set update names. 
DBSETPROP('emp_cust_view.emp_id', 'Field', ;                'KeyField', .T.)
 Set a single-field unique key for the Employee table. 
DBSETPROP('emp_cust_view.cust_id', 'Field', ;
            'KeyField', .T.)
DBSETPROP('emp_cust_view.emp_id1', 'Field', ;
            'KeyField', .T.)
 Set a two-field unique key for the Customer table. 
DBSETPROP('emp_cust_view.phone', 'Field', ; 
            'Updateable', .T.)
DBSETPROP('emp_cust_view.contact', 'Field', ;             'Updateable', .T.)
DBSETPROP('emp_cust_view.company', 'Field', ;             'Updateable', .T.)
 Set the updateable fields. Typically, key fields are not updateable. 
DBSETPROP('emp_cust_view', 'View', ;
            'SendUpdates', .T.)
 Activate the update functionality. 
GO TOP
REPLACE employee.phone WITH "(206)111-2222"
REPLACE customer.contact WITH "John Doe"
 Modify data in the view. 
TABLEUPDATE()
 Commit the changes by updating both the Employee and Customer base tables.

Customizing Views with the Data Dictionary

Because views are stored in a database, you can create:

  • Captions
  • Comments for the view and view fields
  • Default values for view fields
  • Field- and row-level rules and rule error messages

The data dictionary features for views are similar in function to their counterparts for database tables. However, you use the language rather than the Table Designer to create captions, comments, default values and rules for views.

See Also

Updating Data in a View | Creating Default Values for View Fields | Creating Rules on View Fields and Rows | View Designer | DBSETPROP( ) | Updating Data | Customizing Views