[JBoss JIRA] (TEIID-5763) GENERATED_KEY returns NULL if used in INSTEAD OF INSERT Triggers in DDL files
9:02 a.m.
[
https://issues.jboss.org/browse/TEIID-5763?page=com.atlassian.jira.plugin...
]
Christoph John edited comment on TEIID-5763 at 6/23/19 10:01 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?
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?
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
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
2480
days inactive
2480
days old
0 comments
1 participants
participants (1)
-
Christoph John (Jira)