[teiid-issues] [JBoss JIRA] (TEIID-5763) GENERATED_KEY returns NULL if used in INSTEAD OF INSERT Triggers in DDL files
Steven Hawkins (Jira)
issues at jboss.org
Sat Jun 15 14:42:00 EDT 2019
[ https://issues.jboss.org/browse/TEIID-5763?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13747509#comment-13747509 ]
Steven Hawkins commented on TEIID-5763:
---------------------------------------
An odata exception such as "{"error":{"code":null,"message":"TEIID16016 Insert into QuicklyAddedProducts success, but failed to retrieve auto generated keys from source, thus failed to show result entity; Supply the key values."}}" is what I was mentioning as issue 3.
It requires that the key value is explicitly set:
CREATE TRIGGER ON QuicklyAddedProducts INSTEAD OF INSERT AS
FOR EACH ROW
BEGIN ATOMIC
DECLARE long vIdProduct;
INSERT INTO
Products(fkDatabaseKey)
VALUES
(2);
vIdProduct = generated_key();--CONVERT(GENERATED_KEY('idProduct'),long);
-- create a new record for the quickly added product
INSERT INTO
QuicklyAddedProducts_SRC(fkProduct, fkProfile, product_name, energy_100g, carbohydrates_100g, proteins_100g, fat_100g)
VALUES
(vIdProduct, new.fkProfile, new.product_name, new.energy_100g, new.carbohydrates_100g, new.proteins_100g, new.fat_100g);
*key.fkProduct = vIdProduct;*
END;
And unintuitively for the view auto increment column to be not null:
CREATE VIEW QuicklyAddedProducts (fkProduct long *NOT NULL* AUTO_INCREMENT ...
After that things seem to work fine.
I'll make another commit under this issue to not require the not null on the auto_increment.
> 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)
More information about the teiid-issues
mailing list