[teiid-issues] [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

[Christoph John (Jira)]

    [ https://issues.jboss.org/browse/TEIID-5782?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13759345#comment-13759345 ] 

Christoph John commented on TEIID-5782:
---------------------------------------

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)