[teiid-issues] [JBoss JIRA] Commented: (TEIID-1351) Replace translate criteria

[Steven Hawkins (JIRA)]

    [ https://jira.jboss.org/browse/TEIID-1351?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12564151#comment-12564151 ] 

Steven Hawkins commented on TEIID-1351:
---------------------------------------

The initial approach that I'm favoring is to add support for something like instead of trigger actions (fairly standard functionality), which will initially support row-by-row updates rather than the changeset described above.

So rather than entering an update procedure of the form:
CREATE PROCEDURE
BEGIN
  ...
END

The sql would instead look like:
FOR EACH ROW
BEGIN
  ...
END

The procedure block would execute for each updated row and supply NEW/OLD variable groups to reference the changing values.  I don't think that we will initially need support for the referencing clause to change the names of the new/old variables, but it could be added without much effort.

The engine would still allow for the old procedure form an behave according to the old rules.

> Replace translate criteria
> --------------------------
>
>                 Key: TEIID-1351
>                 URL: https://jira.jboss.org/browse/TEIID-1351
>             Project: Teiid
>          Issue Type: Feature Request
>          Components: Query Engine
>    Affects Versions: 7.3
>            Reporter: Steven Hawkins
>            Assignee: Steven Hawkins
>            Priority: Critical
>             Fix For: 7.3
>
>
> While we hardened the usage in 7.2, translate criteria has too many implied restrictions to reliably perform the update/delete the user query is specifying.
> It would be better to rely on better default handling (especially pass-through) TEIID-1349 and to remove the current restrictions on virtual update, such as requiring scalar set clause values and no subquery evaluation TEIID-1267.  
> This change should not remove the possibility of using TRANSLATE CRITERIA/CREATE PROCEDURE, as it will not be removed until Teiid 8 or later.  
> Beyond the default handling, one possible construct is to provide the user with an cursor that contains the update (or delete) set with the corresponding updated values.  Then leave it up to the user to iterate over the change set and process the changes.  One drawback with this approach is that it may be hard to decipher from the view output rows how to apply the updates to constituent tables when the view transformation is a set or join and branch descriminators or additional primary keys are not projected.  Of course those situations were already difficult to handle with translate criteria.   
> This will require coordinated designer changes.

-- 
This message is automatically generated by JIRA.
-
For more information on JIRA, see: http://www.atlassian.com/software/jira