[teiid-issues] [JBoss JIRA] (TEIID-5763) GENERATED_KEY returns NULL if used in INSTEAD OF INSERT Triggers in DDL files
Christoph John (Jira)
issues at jboss.org
Mon Jun 17 17:33:00 EDT 2019
[ https://issues.jboss.org/browse/TEIID-5763?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13748037#comment-13748037 ]
Christoph John edited comment on TEIID-5763 at 6/17/19 5:32 PM:
----------------------------------------------------------------
Hello Steven,
great. But I do have one further question regarding the key variable. Does it always represent the key value of the table on which the last insert was done? I mean, in case I have multiple inserts in an insert trigger on different tables, is then the key variable always associated with the record on the table of the last insert?
for example:
{code:java}
CREATE FOREIGN TABLE tableA(i integer ,k integer not null auto_increment primary key);
CREATE FOREIGN TABLE tableB(i integer ,k integer not null auto_increment primary key)
CREATE FOREIGN TABLE tableC(i integer ,k integer not null auto_increment primary key)
create view v1 (i integer, k integer not null auto_increment primary key)
OPTIONS (UPDATABLE true) as
select x, y from tableC;
create trigger on v1 instead of insert as
for each row begin atomic
insert into tableA (i) values (new.i);
--key.k references tableA.k?
insert into tableB (i) values (new.i);
--key.k references tableB.k?
insert into tableC (i) values(new.i);
--key.k references tableC.k?
{code}
was (Author: cjohn001):
Hello Steven,
great. But I do have one further question regarding the key variable. Does it always represent the key value of the table on which the last insert was done? I mean, in case I have multiple inserts in an insert trigger on different tables, is then the key variable always associated with the record on the table of the last insert?
for example:
CREATE FOREIGN TABLE tableA(i integer ,k integer not null auto_increment primary key);
CREATE FOREIGN TABLE tableB(i integer ,k integer not null auto_increment primary key)
CREATE FOREIGN TABLE tableC(i integer ,k integer not null auto_increment primary key)
create view v1 (i integer, k integer not null auto_increment primary key)
OPTIONS (UPDATABLE true) as
select x, y from tableC;
create trigger on v1 instead of insert as
for each row begin atomic
insert into tableA (i) values (new.i);
--key.k references tableA.k?
insert into tableB (i) values (new.i);
--key.k references tableB.k?
insert into tableC (i) values(new.i);
--key.k references tableC.k?
> 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: Blocker
>
> 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)
More information about the teiid-issues
mailing list