RETURNING not working with view when the field's value is generated in table's
trigger
--------------------------------------------------------------------------------------
Key: CORE-3624
URL: http://tracker.firebirdsql.org/browse/CORE-3624
Project: Firebird Core
Issue Type: Bug
Components: Engine
Affects Versions: 2.5.0, 2.5.1
Reporter: Ain Valtin
Priority: Minor
When VIEW is not updatable unless you write "on insert" triggers then the
RETURNING doesn't work with values which are generated in table's trigger. Ie
consider the following setup:
CREATE TABLE Bar(
id INTEGER,
foo INTEGER
);
CREATE TABLE Foo(
id INTEGER,
bar INTEGER
);
CREATE SEQUENCE GEN_Foo;
CREATE VIEW vFoo AS SELECT Foo.* FROM Foo LEFT JOIN bar ON foo.bar = bar.id;
SET TERM ^ ;
CREATE TRIGGER Foo_I FOR Foo
ACTIVE BEFORE INSERT POSITION 10
AS
BEGIN
-- generate PK
IF(NEW.id IS NULL)THEN NEW.id = NEXT VALUE FOR GEN_Foo;
END^
-- this trigger is required to make view insertable
CREATE TRIGGER vFoo_I FOR vFoo
ACTIVE BEFORE INSERT POSITION 20
AS
BEGIN
INSERT INTO Foo(bar) VALUES(NEW.bar);
END^
SET TERM ; ^
Now when inserting record to the view:
INSERT INTO vFoo(bar) VALUES(10) RETURNING id;
the statement returns NULL for the id (but the PK is generated OK and record is
added to the table). Workaround is to generate the PK in view's trigger (code
dublication!) or use RETURNING in the view's trigger (kind of strange...), ie
CREATE TRIGGER vFoo_I FOR vFoo
ACTIVE BEFORE INSERT POSITION 10
AS
BEGIN
IF(NEW.id IS NULL)THEN NEW.id = NEXT VALUE FOR GEN_Foo;
INSERT INTO Foo(id, bar) VALUES(NEW.id, NEW.bar);
END^
or
CREATE TRIGGER vFoo_I FOR vFoo
ACTIVE BEFORE INSERT POSITION 10
AS
BEGIN
INSERT INTO Foo(bar) VALUES(NEW.bar) RETURNING id INTO NEW.id;
END^
With either of those triggers the ID is returned OK.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
All the data continuously generated in your IT infrastructure contains a
definitive record of customers, application performance, security
threats, fraudulent activity and more. Splunk takes this data and makes
sense of it. Business sense. IT sense. Common sense.
http://p.sf.net/sfu/splunk-d2dcopy1
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
