You need to set the view to be "updatable", there is check box you need to select for updates to enable. Then based on the "select" transformation you provided for the view, it can automatically supply "insert/update/delete" operations in certain situations. As you know in your case the "file" source does not support updates, so you can provide your own "insert/update/delete" procedures for the view (see there are multiple tabs for this in Designer, where you provided the select transformation) using the procedure language using Update Procedures,that does involve the "file" source.
> Select statement is working fine in VDB but during update an error -> Update is not allowed. A procedure may be needed to handle the update since: the updatable view has no valid target for updates.
No valid targets usually means that the underlying sources are not marked as updatable.
> Then based on the "select" transformation you provided for the view, it can automatically supply "insert/update/delete" operations in certain situations.
Whenever a join is involved we will automatically determine an appropriate update only if the join is key preserving - Updatable Views - Teiid 8.5 (draft) - Project Documentation Editor
> Insert Procedure is giving syntax error that "INPUT.Emp_No is specified with an unknown group context"
The only insert procedure designer should allow is an INSTEAD OF FOR EACH ROW trigger - Update Procedures - Teiid 8.5 (draft) - Project Documentation Editor
It should probably provide you with a better error message to let you know what is expected.
The check box "Support Update" is already selected in database view created upon table and the view created over join between file and database view. Still I am getting an error that underlying sources are not marked as updatable.
Is there some other property which needs to be checked?
See my message above. It may be the case that key preservation is not satisfied. Ideally there would be a message about that in the Designer. Can you post your view definition and the DDL of the tables involved to see if Teiid or Designer is not providing you with enough information.
The view is based on join between file and database (mysql) view. Database view is marked as "Support Update" to allow updates on database.
View is also marked as "Support Update". Using VDB, I can update the base table through database view but not through the final join view. Is this the expected behavior?
DDL - The database table is having two columns as "Emp_No" and "Salary" with Emp No marked as primary key.
File - It has 2 columns as "Emp No" and "Name"
View - View is join between above on the basis of Emp No. Update procedure is also added as mentioned below.
FOR EACH ROW
UPDATE ptp.super_hero_salary SET salary = "NEW".salary WHERE FileDBAccessView.ptp.super_hero_salary.emp_no = "OLD".emp_no;
> I can update the base table through database view but not through the final join view. Is this the expected behavior?
It depends upon what you mean. As long as the Teiid view definition satisfies the requirements of Updatable Views - Teiid 8.5 (draft) - Project Documentation Editor and the update the you are running is valid, then the view should be inherently updatable. If you were not getting sufficient information in Designer when attempting to use inherent updates, then there may be an issue there.
Any view may be made updatable via INSTEAD OF triggers. There you assume full responsibility for the processing of the update. Is your statement that the update is not working based upon trying to do an inherent update or with using the INSTEAD OF trigger shown above?
Do I understand it correctly, that the tab "INSERT", "UPDATE", "DELETE" in teiid designer relational view transformation diagram stands for "CREATE TRIGGER ON name INSTEAD OF INSERT|UPDATE|DELETE AS" and the contents of the tab (ie the query) should have form "FOR EACH ROW block"?
Another question: how to switch the TRANSLATE CRITERIA for teiid 8 - ie. how should the following query look like?
VARIABLES.ShipId = SELECT SHIPPER_ID FROM PartsOne.SUPPLIER_PARTS WHERE TRANSLATE CRITERIA ON (TwoTransView.UpdateParts.SHIPPER_ID, TwoTransView.UpdateParts.SUPPLIER_ID, TwoTransView.UpdateParts.PART_ID);
DELETE FROM PartsOne.SUPPLIER_PARTS WHERE TRANSLATE CRITERIA ON (TwoTransView.UpdateParts.SUPPLIER_ID, TwoTransView.UpdateParts.PART_ID, TwoTransView.UpdateParts.SHIPPER_ID);
Yes, they are for creating the appropriate instead of triggers.
> VARIABLES.ShipId = SELECT SHIPPER_ID FROM PartsOne.SUPPLIER_PARTS WHERE TRANSLATE CRITERIA ON (TwoTransView.UpdateParts.SHIPPER_ID, TwoTransView.UpdateParts.SUPPLIER_ID, TwoTransView.UpdateParts.PART_ID);
There is no translate criteria concept, rather your insert/update/delete is effectively applied against the view and then you reference new/old/changing variables in your for each row block:
for each row
DELETE FROM PartsOne.SUPPLIER_PARTS WHERE SUPPLIER_ID = old.SUPPLIER_ID;
thank you, it works now.