[teiid-issues] [JBoss JIRA] (TEIID-5799) GENERATED_KEY cannot resolve primary key, if created via insert trigger on view

[Christoph John (Jira)]

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

Christoph John commented on TEIID-5799:
---------------------------------------

Hello Steven,
the relevant line with the missing END statement is between line 527 and 528. I attached the vdb file here again.

it is the ELSE IF path in the following sniped:

{code:java}
ELSE IF (new.fkDatabaseKey = 2)
		BEGIN 
			IF(CHANGING.fkProfile AND CHANGING.AddedDateTime AND CHANGING.MealNumber AND CHANGING.AmountInG AND
			   CHANGING.product_name AND CHANGING.energy_100g AND CHANGING.carbohydrates_100g AND 
			   CHANGING.proteins_100g AND CHANGING.fat_100g)
			BEGIN
				-- we first check if max amount of quickly added products for this user account is already reached
				vNumRecords = SELECT COUNT(*) FROM QuicklyAddedProducts WHERE fkProfile = new.fkProfile;
				IF(vNumRecords > 15000)
				BEGIN
					DECLARE EXCEPTION e = SQLEXCEPTION 'Limit for QuicklyAddedProducts user records reached' SQLSTATE '45000';
					RAISE e;
				END	

				-- create a new record in products, as this is this results in a common primary key 
				-- for all product databases
				INSERT INTO 
					Products(fkDatabaseKey)
				VALUES
					(new.fkDatabaseKey);

				-- retrieve generated key	
				vIdProduct = cast(generated_key('idProduct') as long);

				-- first we create a new record for the quickly added product
				INSERT INTO 
						QuicklyAddedProducts(fkProduct, fkProfile, product_name, energy_100g, carbohydrates_100g, proteins_100g, fat_100g)
				VALUES
						(vIdProduct, new.fkProfile, new.product_name, new.energy_100g, new.carbohydrates_100g, new.proteins_100g, new.fat_100g);

				-- finaly create the relevant Diary entry
				INSERT INTO
					Diary_SRC(fkProfile, AddedDateTime, fkProduct, MealNumber, AmountInG)
				VALUES
					(new.fkProfile, new.AddedDateTime, vIdProduct, new.MealNumber, new.AmountInG);	

				-- set primary key	
				vIdDiaryEntry = cast(generated_key('idDiaryEntry') as long);
				key.idDiaryEntry = vIdDiaryEntry;		
			END
		ELSE IF (new.fkDatabaseKey = 3)
{code}


 [^svc-vdb.ddl] 

> GENERATED_KEY cannot resolve primary key, if created via insert trigger on view
> -------------------------------------------------------------------------------
>
>                 Key: TEIID-5799
>                 URL: https://issues.jboss.org/browse/TEIID-5799
>             Project: Teiid
>          Issue Type: Bug
>    Affects Versions: 13.0
>            Reporter: Christoph John
>            Assignee: Steven Hawkins
>            Priority: Blocker
>         Attachments: svc-vdb-example.ddl, svc-vdb.ddl
>
>
> Hello Steven,
> I just ran into the next issue with the generated key functionality. Attached you find an example. The relevant section is  given in
> CREATE TRIGGER ON Diary INSTEAD OF INSERT AS
> with the block following:
> ELSE IF (new.fkDatabaseKey = 3)
> In the insert trigger I am trying to duplicate a record from table "UserDefinedProducts" and reference it in the row to be created from the trigger.
> I again the the error message:
> POST on 'Diary' failed; will be repeated automatically - Error: TEIID16016 Insert into Diary success, but failed to retrieve auto generated keys from source, thus failed to show result entity; Supply the key values.
> I am using the most recent sources checked out about an hour ago.
> Do you see a bug in my code, or is it again an issue I have hit? Thanks for your help!
> Let me know if I shall assemble a docker-compose file for you to debug the issue.
> Update: As I seem to be not able to delete attachments here. One further note. My example hat a permission error in one line where I have red from the wrong table. It should instead be:
> SELECT * INTO #tmpItem FROM UserDefinedProductsOfAllUsers WHERE fkProduct = new.fkProduct LIMIT 1;
> However, the previously described error stays the same.
> Best regards,
>  Christoph



--
This message was sent by Atlassian Jira
(v7.12.1#712002)