[teiid-issues] [JBoss JIRA] (TEIID-5763) GENERATED_KEY returns NULL if used in INSTEAD OF INSERT Triggers in DDL files
[
https://issues.jboss.org/browse/TEIID-5763?page=com.atlassian.jira.plugin...
]
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_Pr...
However there was a typo on
https://github.com/teiid/teiid-documents/blame/12.0.x/reference/Update_Pr...
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)