[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... ] Christoph John edited comment on TEIID-5763 at 6/23/19 10:25 AM: ----------------------------------------------------------------- Hello Steven, I need to come back on you regarding the generated key topic. I tried now to use the generated key in the trigger function of a different table and always end up with the following error. TEIID31080 my_nutri_diary.UserDefinedProducts validation error: TEIID31119 Symbol key.fkProduct is specified with an unknown group context It does not matter if I take the shortcut generated key, or the explicit cast function. The difference on the current table to the other one is, that the table has a composite primary key. I was wondering if this might be the reason for the issue. I tried to set the second key explicitely on the "key.". Unfortunately, the error remains. The source table definition is as below: CREATE FOREIGN TABLE UserDefinedProducts_SRC ( fkProduct long NOT NULL OPTIONS(NAMEINSOURCE '"fkProduct"', NATIVE_TYPE 'BIGINT'), fkProfile long NOT NULL OPTIONS(NAMEINSOURCE '"fkProfile"', NATIVE_TYPE 'BIGINT'), ... CONSTRAINT "PRIMARY" PRIMARY KEY(fkProduct, fkProfile), CONSTRAINT fkUserDefinedProductToProduct FOREIGN KEY(fkProduct) REFERENCES Products(idProduct), CONSTRAINT fKUserDefinedProductToAccount FOREIGN KEY(fkProfile) REFERENCES Account(idProfile), CONSTRAINT fkProfile_fkProduct_idCode_InProducts_UNIQUE UNIQUE(fkProduct, fkProfile, idCode), CONSTRAINT idCodeInUserDefinedProducts_idx INDEX(idCode), CONSTRAINT fKUserDefinedProductToAccount_idx INDEX(fkProfile) ) OPTIONS(NAMEINSOURCE '"UserDefinedProducts"', UPDATABLE 'TRUE', CARDINALITY '4'); The view is: CREATE VIEW UserDefinedProducts ( fkProduct long NOT NULL AUTO_INCREMENT, fkProfile long NOT NULL, ... CONSTRAINT "PRIMARY" PRIMARY KEY(fkProduct, fkProfile), CONSTRAINT fkUserDefinedProductToProduct FOREIGN KEY(fkProduct) REFERENCES Products(idProduct), CONSTRAINT fKUserDefinedProductToAccount FOREIGN KEY(fkProfile) REFERENCES Account(idProfile), CONSTRAINT fkProfile_fkProduct_idCode_InProducts_UNIQUE UNIQUE(fkProduct, fkProfile, idCode), CONSTRAINT idCodeInUserDefinedProducts_idx INDEX(idCode), CONSTRAINT fKUserDefinedProductToAccount_idx INDEX(fkProfile) ) OPTIONS(UPDATABLE 'TRUE') AS SELECT udp.fkProduct as fkProduct, udp.fkProfile as fkProfile, ... FROM UserDefinedProducts_SRC as udp; And here is the relevant input trigger: CREATE TRIGGER ON UserDefinedProducts INSTEAD OF INSERT AS FOR EACH ROW BEGIN ATOMIC DECLARE long vIdProduct; INSERT INTO Products(fkDatabaseKey) VALUES (0); vIdProduct = cast(generated_key('idProduct') as long); key.fkProduct = vIdProduct; -- key.fkProfile = new.fkProfile; -- create a new record for the quickly added product INSERT INTO UserDefinedProducts_SRC(fkProduct, fkProfile, idCode, product_name, origins, brands, quantity, serving_quantity, nova_group, nutrition_grade_fr, energy_100g, carbohydrates_100g, sugars_100g, proteins_100g, fat_100g, saturated_fat_100g, saturated_fat_modifier, salt_100g, salt_modifier, fiber_100g) VALUES (vIdProduct, new.fkProfile, new.idCode, new.product_name, new.origins, new.brands, new.quantity, new.serving_quantity, new.nova_group, new.nutrition_grade_fr, new.energy_100g, new.carbohydrates_100g, new.sugars_100g, new.proteins_100g, new.fat_100g, new.saturated_fat_100g, new.saturated_fat_modifier, new.salt_100g, new.salt_modifier, new.fiber_100g); END; Do you have an idea what is going wrong? Maybe I am still misunderstanding the key group. My thinking is that it reflects the keys that are defined in the view. not these of the underlaying table. is this correct? Update: Interestingly, I found out that the procedure is working if I do not set the key explicitly. Hence, I simply removed the line key.fkProduct = vIdProduct; Would be great if you could explain me what is happening here with the error message and why I in the one case have to set the key. explicitly, and why it does not work in the other case. Still the topic has to much trial and error characteristic for me with my current understanding. Thanks for your help. was (Author: cjohn001): Hello Steven, I need to come back on you regarding the generated key topic. I tried now to use the generated key in the trigger function of a different table and always end up with the following error. TEIID31080 my_nutri_diary.UserDefinedProducts validation error: TEIID31119 Symbol key.fkProduct is specified with an unknown group context It does not matter if I take the shortcut generated key, or the explicit cast function. The difference on the current table to the other one is, that the table has a composite primary key. I was wondering if this might be the reason for the issue. I tried to set the second key explicitely on the "key.". Unfortunately, the error remains. The source table definition is as below: CREATE FOREIGN TABLE UserDefinedProducts_SRC ( fkProduct long NOT NULL OPTIONS(NAMEINSOURCE '"fkProduct"', NATIVE_TYPE 'BIGINT'), fkProfile long NOT NULL OPTIONS(NAMEINSOURCE '"fkProfile"', NATIVE_TYPE 'BIGINT'), ... CONSTRAINT "PRIMARY" PRIMARY KEY(fkProduct, fkProfile), CONSTRAINT fkUserDefinedProductToProduct FOREIGN KEY(fkProduct) REFERENCES Products(idProduct), CONSTRAINT fKUserDefinedProductToAccount FOREIGN KEY(fkProfile) REFERENCES Account(idProfile), CONSTRAINT fkProfile_fkProduct_idCode_InProducts_UNIQUE UNIQUE(fkProduct, fkProfile, idCode), CONSTRAINT idCodeInUserDefinedProducts_idx INDEX(idCode), CONSTRAINT fKUserDefinedProductToAccount_idx INDEX(fkProfile) ) OPTIONS(NAMEINSOURCE '"UserDefinedProducts"', UPDATABLE 'TRUE', CARDINALITY '4'); The view is: CREATE VIEW UserDefinedProducts ( fkProduct long NOT NULL AUTO_INCREMENT, fkProfile long NOT NULL, ... CONSTRAINT "PRIMARY" PRIMARY KEY(fkProduct, fkProfile), CONSTRAINT fkUserDefinedProductToProduct FOREIGN KEY(fkProduct) REFERENCES Products(idProduct), CONSTRAINT fKUserDefinedProductToAccount FOREIGN KEY(fkProfile) REFERENCES Account(idProfile), CONSTRAINT fkProfile_fkProduct_idCode_InProducts_UNIQUE UNIQUE(fkProduct, fkProfile, idCode), CONSTRAINT idCodeInUserDefinedProducts_idx INDEX(idCode), CONSTRAINT fKUserDefinedProductToAccount_idx INDEX(fkProfile) ) OPTIONS(UPDATABLE 'TRUE') AS SELECT udp.fkProduct as fkProduct, udp.fkProfile as fkProfile, ... FROM UserDefinedProducts_SRC as udp; And here is the relevant input trigger: CREATE TRIGGER ON UserDefinedProducts INSTEAD OF INSERT AS FOR EACH ROW BEGIN ATOMIC DECLARE long vIdProduct; INSERT INTO Products(fkDatabaseKey) VALUES (0); vIdProduct = cast(generated_key('idProduct') as long); key.fkProduct = vIdProduct; -- key.fkProfile = new.fkProfile; -- create a new record for the quickly added product INSERT INTO UserDefinedProducts_SRC(fkProduct, fkProfile, idCode, product_name, origins, brands, quantity, serving_quantity, nova_group, nutrition_grade_fr, energy_100g, carbohydrates_100g, sugars_100g, proteins_100g, fat_100g, saturated_fat_100g, saturated_fat_modifier, salt_100g, salt_modifier, fiber_100g) VALUES (vIdProduct, new.fkProfile, new.idCode, new.product_name, new.origins, new.brands, new.quantity, new.serving_quantity, new.nova_group, new.nutrition_grade_fr, new.energy_100g, new.carbohydrates_100g, new.sugars_100g, new.proteins_100g, new.fat_100g, new.saturated_fat_100g, new.saturated_fat_modifier, new.salt_100g, new.salt_modifier, new.fiber_100g); END; Do you have an idea what is going wrong? Maybe I am still misunderstanding the key group. My thinking is that it reflects the keys that are defined in the view. not these of the underlaying table. is this correct? -- This message was sent by Atlassian Jira (v7.12.1#712002)