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: Critical Fix For: 12.3 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