[teiid-issues] [JBoss JIRA] (TEIID-5072) Generated keys should be passed from triggers

[Steven Hawkins (JIRA)]

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

Steven Hawkins commented on TEIID-5072:
---------------------------------------

The proposal for this would look like:

{code}
create view v1 (i integer, k integer not null auto_increment primary key) OPTIONS (UPDATABLE true) 
   as select x, y from tbl;
create trigger on v1 instead of insert as 
   for each row begin atomic
      insert into tbl (x) values (new.i); key.k = cast(generated_key('y') as integer); 
   end;
{code}

This introduces a new variable group key, which would only be in scope for the insert trigger on a view with an auto_increment primary key - and only for the key columns.  There would be a new function introduced called generated_key(string) that could fetch the first generated key column value from the last insert statement or null if there were no matching columns, etc.

> Generated keys should be passed from triggers
> ---------------------------------------------
>
>                 Key: TEIID-5072
>                 URL: https://issues.jboss.org/browse/TEIID-5072
>             Project: Teiid
>          Issue Type: Sub-task
>          Components: Query Engine
>            Reporter: Steven Hawkins
>            Assignee: Steven Hawkins
>             Fix For: 10.0
>
>
> In the context of an update trigger we should be able to convey the generated keys to the caller.  In the case where only a single insert occurs, this should be automatic.  Otherwise we'll need functions/variables to get/set the generated keys in the procedure.  For example see the LAST_INSERT_ID function on mysql.



--
This message was sent by Atlassian JIRA
(v7.2.3#72005)