JavaScript Editor js editor     Web development 
↑


Main Page

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 updatable.

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 updatable

In most cases, the default values provided by Visual FoxPro prepare a multitable view to be updatable, 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', ;

'Updatable', .T.)

DBSETPROP('emp_cust_view.contact', 'Field', ; 'Updatable', .T.)

DBSETPROP('emp_cust_view.company', 'Field', ; 'Updatable', .T.)

Set the updatable fields. Typically, key fields are not updatable.

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.

See Also



JavaScript Editor js editor     Web development 
→