[teiid-issues] [JBoss JIRA] (TEIID-5763) GENERATED_KEY returns NULL if used in INSTEAD OF INSERT Triggers in DDL files

[Steven Hawkins (Jira)]

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

Steven Hawkins commented on TEIID-5763:
---------------------------------------

> Does it always represent the key value of the table on which the last insert was done? 

It represents whatever the auto increment value for the view is, so v1.k in your example above - regardless of how many logical inserts you actually perform for that row of v1.



> GENERATED_KEY returns NULL if used in INSTEAD OF INSERT Triggers in DDL files
> -----------------------------------------------------------------------------
>
>                 Key: TEIID-5763
>                 URL: https://issues.jboss.org/browse/TEIID-5763
>             Project: Teiid
>          Issue Type: Bug
>    Affects Versions: 12.2
>            Reporter: Christoph John
>            Assignee: Steven Hawkins
>            Priority: Blocker
>
> The issue arrised in:
> https://developer.jboss.org/message/989700#989700
> Following sceanarios are  given:
> Variant 1:
> Table Product(id), primary key = autoincrement;
> Table QuicklyAddedProduct, primary key is foreign key on Product.id
> Table Diary_SRC
> View Diary on Diary_SRC
> INSTEAD OF INSERT Trigger on Diary should :
> -create new record on Product, 
> -get autoincremented Product.id of new record,
> - create new QuicklyAddedProduct with returned Product.id as primary key
> - create new Diary record with QuicklyAddedProduct 
> {
>    INSERT a new record on Product;
>    idProduct = CONVERT(GENERATED_KEY('idProduct'),long); // fails
> }
> Variant 2:
> INSTEAD OF INSERT trigger on Diary should :
> - create new QuicklyAddedProduct 
> - add Quickly added prodcut to Diary
> additionally a INSTEAD OF trigger on QuicklyAddedProduct exists which:
> - creates new Product record
> - uses returned GENERATED_KEY(Product.id) to add record on QuicklyAddedProduct with Product.id as primary key



--
This message was sent by Atlassian Jira
(v7.12.1#712002)