[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=13747726#comment-13747726 ] 

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

> I checked the documentation. but I could not find anything about this "key" variable. Is it a special variable like OLD and NEW? And for what is it good for in general? More use cases involved I should know of?

As you guessed it should documented on:  http://teiid.github.io/teiid-documents/master/content/reference/Update_Procedures_Triggers.html
However there was a typo on https://github.com/teiid/teiid-documents/blame/12.0.x/reference/Update_Procedures_Triggers.adoc#L100 such that the example block was not closed properly and the content after that was not rendered.  This is corrected now.

It is specific to the generated key case.  Since it is generally difficult to automatically determine the key value from a static analysis of the insert and it may not be correct to simply access the last generated key (in the case of a cascaded insert), we instead expect the procedure writer to explicitly set the key value.

> Please give me an explanation for the key variable, at the moment it looks like magic to me 

Think of it as an implicit OUT variable.  You can move it before the insert in your case because the value, vIdProduct, is already determined.

> Shall I add a further jira issue to enhance the documentation in respect to the topic?

The correction was checked in under this issue.

> 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)