[JBoss JIRA] (TEIID-5782) When overwriting an Insert trigger with custom logic in a ddl file, the primary key is not correctly transferred back in the response of the odata layer with a dummy insert value
4:13 p.m.
[
https://issues.jboss.org/browse/TEIID-5782?page=com.atlassian.jira.plugin...
]
Christoph John edited comment on TEIID-5782 at 7/15/19 5:12 PM:
----------------------------------------------------------------
Hello Steven,
I am currently running in the next issue with the insert trigger. When trying to set the
key value, I run into the following error:
TEIID31080 my_nutri_diary.UserDefinedProducts validation error: TEIID31119 Symbol
key.fkProduct is specified with an unknown group context
Have you an idea what is going wrong here? Could it be related to the compound primary key
that I am using? The code:
{noformat}
-- ///////////
CREATE VIEW UserDefinedProducts (
fkProduct long NOT NULL AUTO_INCREMENT,
fkProfile long NOT NULL,
idCode long,
product_name string(48) NOT NULL,
origins string(64),
brands string(64) NOT NULL,
quantity string(64),
serving_quantity double,
nova_group char(1),
nutrition_grade_fr char(1),
energy_100g double NOT NULL,
carbohydrates_100g double NOT NULL,
sugars_100g double NOT NULL,
proteins_100g double NOT NULL,
fat_100g double NOT NULL,
saturated_fat_100g double NOT NULL,
saturated_fat_modifier char(1),
salt_100g double NOT NULL,
salt_modifier char(1),
fiber_100g double,
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,
udp.idCode as idCode,
udp.product_name as product_name,
udp.origins as origins,
udp.brands as brands,
udp.quantity as quantity,
udp.serving_quantity as serving_quantity,
udp.nova_group as nova_group,
udp.nutrition_grade_fr as nutrition_grade_fr,
udp.energy_100g as energy_100g,
udp.carbohydrates_100g as carbohydrates_100g,
udp.sugars_100g as sugars_100g,
udp.proteins_100g as proteins_100g,
udp.fat_100g as fat_100g,
udp.saturated_fat_100g as saturated_fat_100g,
udp.saturated_fat_modifier as saturated_fat_modifier,
udp.salt_100g as salt_100g,
udp.salt_modifier as salt_modifier,
udp.fiber_100g as fiber_100g
FROM UserDefinedProducts_SRC as udp;
CREATE TRIGGER ON UserDefinedProducts INSTEAD OF INSERT AS
FOR EACH ROW
BEGIN ATOMIC
DECLARE long vIdProduct;
DECLARE integer vNumRecords;
-- if a product code was given, check that the user has not already created a product
for this code
-- this situation should actualy never appear
IF (new.idCode IS NOT NULL)
BEGIN
vNumRecords = SELECT 1 FROM UserDefinedProducts_SRC WHERE
fkProfile = new.fkProfile AND idCode = new.idCode
LIMIT 1;
IF(vNumRecords > 0)
BEGIN
DECLARE EXCEPTION e = SQLEXCEPTION 'User defined product already exists'
SQLSTATE '45000';
RAISE e;
END
END
-- check if the user has already reached his MAX amount of UserDefinedProducts;
vNumRecords = SELECT COUNT(*) FROM UserDefinedProducts_SRC WHERE fkProfile =
new.fkProfile;
IF(vNumRecords >= 1000)
BEGIN
DECLARE EXCEPTION e = SQLEXCEPTION 'MAX amount of user defined products exceeded
for this account' SQLSTATE '45000';
RAISE e;
END
-- if all checks have been passed we can safely add the product to the database
INSERT INTO
Products(fkDatabaseKey)
VALUES
(0);
-- create a new record for the user defined product
vIdProduct = cast(generated_key('idProduct') as long);
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);
-- supply the key value
key.fkProduct = vIdProduct;
END;
{noformat}
was (Author: cjohn001):
Hello Steven,
I am currently running in the next issue with the insert trigger. When trying to set the
key value, I run into the following error:
TEIID31080 my_nutri_diary.UserDefinedProducts validation error: TEIID31119 Symbol
key.fkProduct is specified with an unknown group context
Have you an idea what is going wrong here? The code:
{noformat}
-- ///////////
CREATE TRIGGER ON UserDefinedProducts INSTEAD OF INSERT AS
FOR EACH ROW
BEGIN ATOMIC
DECLARE long vIdProduct;
DECLARE integer vNumRecords;
-- if a product code was given, check that the user has not already created a product
for this code
-- this situation should actualy never appear
IF (new.idCode IS NOT NULL)
BEGIN
vNumRecords = SELECT 1 FROM UserDefinedProducts_SRC WHERE
fkProfile = new.fkProfile AND idCode = new.idCode
LIMIT 1;
IF(vNumRecords > 0)
BEGIN
DECLARE EXCEPTION e = SQLEXCEPTION 'User defined product already exists'
SQLSTATE '45000';
RAISE e;
END
END
-- check if the user has already reached his MAX amount of UserDefinedProducts;
vNumRecords = SELECT COUNT(*) FROM UserDefinedProducts_SRC WHERE fkProfile =
new.fkProfile;
IF(vNumRecords >= 1000)
BEGIN
DECLARE EXCEPTION e = SQLEXCEPTION 'MAX amount of user defined products exceeded
for this account' SQLSTATE '45000';
RAISE e;
END
-- if all checks have been passed we can safely add the product to the database
INSERT INTO
Products(fkDatabaseKey)
VALUES
(0);
-- create a new record for the user defined product
vIdProduct = cast(generated_key('idProduct') as long);
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);
-- supply the key value
key.fkProduct = vIdProduct;
END;
{noformat}
When overwriting an Insert trigger with custom logic in a ddl file,
the primary key is not correctly transferred back in the response of the odata layer with
a dummy insert value
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Key: TEIID-5782
URL: https://issues.jboss.org/browse/TEIID-5782
Project: Teiid
Issue Type: Quality Risk
Components: OData
Affects Versions: 12.3
Reporter: Christoph John
Assignee: Steven Hawkins
Priority: Major
Hello,
in the past we had an issue in the odata layer, that the generated primary key of an
autoincrement key was not corretly transfered back in the odata response. This issue was
fixed.
However, the current issues derives from the discussion in
https://issues.jboss.org/browse/TEIID-5763
where we havewritten an INSTEAD OF INSERT trigger. According to my last comment to
https://issues.jboss.org/browse/TEIID-5763, there are two situations which can arrise. I
have to set the 1. key group explicitely or 2. it is set implicitely.
Now in both situations I observe the same result. When I POST an INSERT with a dummy
primary key, say key=0 (as it was NOT NULL in the past, and I am still using the old
sources) I retrieve key=0 in the odata response. Correct would be to get a response with
the generated key instead.
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
1987
days inactive
1987
days old
0 comments
1 participants
participants (1)
-
Christoph John (Jira)