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

[Christoph John (Jira)]

Christoph John created TEIID-5763:
-------------------------------------

             Summary: 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


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)