Retrieving identity value

[gunnar.sigurjonsson]

I´m having some problem retrieving identity value from my newly
inserted row into a view.

I have two tables T1 and T2 which I define as following

CREATE TABLE T1
(
id BIGINT GENERATED ALWAYS AS IDENTITY
(
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
NO CYCLE
NO ORDER
CACHE 20
),
t1name VARCHAR(10),
CONSTRAINT c_k2_column_reference_id_pk PRIMARY KEY(id)
);

CREATE TABLE T2
(
id BIGINT GENERATED ALWAYS AS IDENTITY
(
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
NO CYCLE
NO ORDER
CACHE 20
),
t2name VARCHAR(10),
t1id BIGINT,
CONSTRAINT c_k2_column_reference_id_pk PRIMARY KEY(id)
);
ALTER TABLE T2
ADD CONSTRAINT t1id_fk
FOREIGN KEY( id )
REFERENCES t1 (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION ;
I created view V 1to combine those two table as following
CREATE VIEW V1 AS
SELECT t1.id,
t1.t1name,
t2.id,
t2.t2name
FROM t1

JOIN t2
ON t2.id = t1.id;

At last I created insert trigger T1 to be able to insert into my view
CREATE TRIGGER t1
INSTEAD OF INSERT ON v1
REFERENCING NEW AS v1new
FOR EACH ROW
MODE DB2SQL
BEGIN ATOMIC
INSERT INTO t1(t1name)
VALUES (v1new.t1name);

INSERT INTO t1( t2name, t1id)
VALUES (v1new.t2name, IDENTITY_VAL_LOCAL()) ;

END;

Now I can insert into my view by doing

INSERT INTO v1 (t1name, t2name) value('test1', 'test2');

but when I call IDENTITY_VALUE_LOCAL() i get null value but I´m
interested in to retrieve id value of table T2 which I´m interested
in.

Can anyone help me on how I can retriveve the identity value of newly
created row by the trigger outside the trigger scope?

regards,
Gunnar

[Knut Stolze]

gu*****************@gmail.com wrote:

I´m having some problem retrieving identity value from my newly
inserted row into a view.

I have two tables T1 and T2 which I define as following

CREATE TABLE T1
(
id BIGINT GENERATED ALWAYS AS IDENTITY
(
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
NO CYCLE
NO ORDER
CACHE 20
),
t1name VARCHAR(10),
CONSTRAINT c_k2_column_reference_id_pk PRIMARY KEY(id)
);

CREATE TABLE T2
(
id BIGINT GENERATED ALWAYS AS IDENTITY
(
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
NO CYCLE
NO ORDER
CACHE 20
),
t2name VARCHAR(10),
t1id BIGINT,
CONSTRAINT c_k2_column_reference_id_pk PRIMARY KEY(id)
);
ALTER TABLE T2
ADD CONSTRAINT t1id_fk
FOREIGN KEY( id )
REFERENCES t1 (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION ;
I created view V 1to combine those two table as following
CREATE VIEW V1 AS
SELECT t1.id,
t1.t1name,
t2.id,
t2.t2name
FROM t1

JOIN t2
ON t2.id = t1.id;

At last I created insert trigger T1 to be able to insert into my view
CREATE TRIGGER t1
INSTEAD OF INSERT ON v1
REFERENCING NEW AS v1new
FOR EACH ROW
MODE DB2SQL
BEGIN ATOMIC
INSERT INTO t1(t1name)
VALUES (v1new.t1name);

INSERT INTO t1( t2name, t1id)
VALUES (v1new.t2name, IDENTITY_VAL_LOCAL()) ;

END;

Now I can insert into my view by doing

INSERT INTO v1 (t1name, t2name) value('test1', 'test2');

but when I call IDENTITY_VALUE_LOCAL() i get null value but I´m
interested in to retrieve id value of table T2 which I´m interested
in.

Can anyone help me on how I can retriveve the identity value of newly
created row by the trigger outside the trigger scope?

How about this:

BEGIN ATOMIC
DECLARE new_value INT;
SELECT id
INTO new_value
FROM NEW TABLE ( INSERT
INTO t1(t1name)
VALUES ( v1new.t1name ) ) AS t;
INSERT
INTO t1(t2name, tlid)
VALUES v1new.t2name, new_value);
END

--
Knut Stolze
DB2 z/OS Admin Enablement
IBM Germany

[Serge Rielau]

Knut Stolze wrote:

gu*****************@gmail.com wrote:

>I´m having some problem retrieving identity value from my newly
inserted row into a view.

I have two tables T1 and T2 which I define as following

CREATE TABLE T1
(
id BIGINT GENERATED ALWAYS AS IDENTITY
(
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
NO CYCLE
NO ORDER
CACHE 20
),
t1name VARCHAR(10),
CONSTRAINT c_k2_column_reference_id_pk PRIMARY KEY(id)
);

CREATE TABLE T2
(
id BIGINT GENERATED ALWAYS AS IDENTITY
(
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
NO CYCLE
NO ORDER
CACHE 20
),
t2name VARCHAR(10),
t1id BIGINT,
CONSTRAINT c_k2_column_reference_id_pk PRIMARY KEY(id)
);
ALTER TABLE T2
ADD CONSTRAINT t1id_fk
FOREIGN KEY( id )
REFERENCES t1 (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION ;
I created view V 1to combine those two table as following
CREATE VIEW V1 AS
SELECT t1.id,
t1.t1name,
t2.id,
t2.t2name
FROM t1

JOIN t2
ON t2.id = t1.id;

At last I created insert trigger T1 to be able to insert into my view
CREATE TRIGGER t1
INSTEAD OF INSERT ON v1
REFERENCING NEW AS v1new
FOR EACH ROW
MODE DB2SQL
BEGIN ATOMIC
INSERT INTO t1(t1name)
VALUES (v1new.t1name);

INSERT INTO t1( t2name, t1id)
VALUES (v1new.t2name, IDENTITY_VAL_LOCAL()) ;

END;

Now I can insert into my view by doing

INSERT INTO v1 (t1name, t2name) value('test1', 'test2');

but when I call IDENTITY_VALUE_LOCAL() i get null value but I´m
interested in to retrieve id value of table T2 which I´m interested
in.

Can anyone help me on how I can retriveve the identity value of newly
created row by the trigger outside the trigger scope?

How about this:

BEGIN ATOMIC
DECLARE new_value INT;
SELECT id
INTO new_value
FROM NEW TABLE ( INSERT
INTO t1(t1name)
VALUES ( v1new.t1name ) ) AS t;
INSERT
INTO t1(t2name, tlid)
VALUES v1new.t2name, new_value);
END

I don't think that will work.
NEW TABLE contains NULL values for the identity column in the view.
Theory has it that NEW TABLE would pick up SET-ing of new transition
variables in the INSTEAD OF trigger. But that is yet to be implemented
(no plans so far).

Does anything speak against using a SEQUENCE instead of IDENTITY?

PREVIOUS VALUE FOR <sshould work where IDENTITY_VAL_LOCAL let's you down.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Brian Tkatch]

On Tue, 13 Mar 2007 07:21:28 -0400, Serge Rielau <sr*****@ca.ibm.com>
wrote:

>Knut Stolze wrote:

>gu*****************@gmail.com wrote:

>>I´m having some problem retrieving identity value from my newly
inserted row into a view.

I have two tables T1 and T2 which I define as following

CREATE TABLE T1
(
id BIGINT GENERATED ALWAYS AS IDENTITY
(
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
NO CYCLE
NO ORDER
CACHE 20
),
t1name VARCHAR(10),
CONSTRAINT c_k2_column_reference_id_pk PRIMARY KEY(id)
);

CREATE TABLE T2
(
id BIGINT GENERATED ALWAYS AS IDENTITY
(
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
NO CYCLE
NO ORDER
CACHE 20
),
t2name VARCHAR(10),
t1id BIGINT,
CONSTRAINT c_k2_column_reference_id_pk PRIMARY KEY(id)
);
ALTER TABLE T2
ADD CONSTRAINT t1id_fk
FOREIGN KEY( id )
REFERENCES t1 (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION ;
I created view V 1to combine those two table as following
CREATE VIEW V1 AS
SELECT t1.id,
t1.t1name,
t2.id,
t2.t2name
FROM t1

JOIN t2
ON t2.id = t1.id;

At last I created insert trigger T1 to be able to insert into my view
CREATE TRIGGER t1
INSTEAD OF INSERT ON v1
REFERENCING NEW AS v1new
FOR EACH ROW
MODE DB2SQL
BEGIN ATOMIC
INSERT INTO t1(t1name)
VALUES (v1new.t1name);

INSERT INTO t1( t2name, t1id)
VALUES (v1new.t2name, IDENTITY_VAL_LOCAL()) ;

END;

Now I can insert into my view by doing

INSERT INTO v1 (t1name, t2name) value('test1', 'test2');

but when I call IDENTITY_VALUE_LOCAL() i get null value but I´m
interested in to retrieve id value of table T2 which I´m interested
in.

Can anyone help me on how I can retriveve the identity value of newly
created row by the trigger outside the trigger scope?

How about this:

BEGIN ATOMIC
DECLARE new_value INT;
SELECT id
INTO new_value
FROM NEW TABLE ( INSERT
INTO t1(t1name)
VALUES ( v1new.t1name ) ) AS t;
INSERT
INTO t1(t2name, tlid)
VALUES v1new.t2name, new_value);
END

I don't think that will work.
NEW TABLE contains NULL values for the identity column in the view.
Theory has it that NEW TABLE would pick up SET-ing of new transition
variables in the INSTEAD OF trigger. But that is yet to be implemented
(no plans so far).

Does anything speak against using a SEQUENCE instead of IDENTITY?

PREVIOUS VALUE FOR <sshould work where IDENTITY_VAL_LOCAL let's you down.

Cheers
Serge

Do you think this is a good case for Oracle's RETURNING...INTO clause?
It should work in such a case, where NEW() won't.

B.

[Brian Tkatch]

On 13 Mar 2007 02:18:48 -0700, gu*****************@gmail.com wrote:

>I´m having some problem retrieving identity value from my newly
inserted row into a view.

I have two tables T1 and T2 which I define as following

CREATE TABLE T1
(
id BIGINT GENERATED ALWAYS AS IDENTITY
(
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
NO CYCLE
NO ORDER
CACHE 20
),
t1name VARCHAR(10),
CONSTRAINT c_k2_column_reference_id_pk PRIMARY KEY(id)
);

CREATE TABLE T2
(
id BIGINT GENERATED ALWAYS AS IDENTITY
(
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
NO CYCLE
NO ORDER
CACHE 20
),
t2name VARCHAR(10),
t1id BIGINT,
CONSTRAINT c_k2_column_reference_id_pk PRIMARY KEY(id)
);
ALTER TABLE T2
ADD CONSTRAINT t1id_fk
FOREIGN KEY( id )
REFERENCES t1 (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION ;
I created view V 1to combine those two table as following
CREATE VIEW V1 AS
SELECT t1.id,
t1.t1name,
t2.id,
t2.t2name
FROM t1

JOIN t2
ON t2.id = t1.id;

At last I created insert trigger T1 to be able to insert into my view
CREATE TRIGGER t1
INSTEAD OF INSERT ON v1
REFERENCING NEW AS v1new
FOR EACH ROW
MODE DB2SQL
BEGIN ATOMIC
INSERT INTO t1(t1name)
VALUES (v1new.t1name);

INSERT INTO t1( t2name, t1id)
VALUES (v1new.t2name, IDENTITY_VAL_LOCAL()) ;

END;

Now I can insert into my view by doing

INSERT INTO v1 (t1name, t2name) value('test1', 'test2');

but when I call IDENTITY_VALUE_LOCAL() i get null value but I´m
interested in to retrieve id value of table T2 which I´m interested
in.

Can anyone help me on how I can retriveve the identity value of newly
created row by the trigger outside the trigger scope?

regards,
Gunnar

Bear with me, i'm just thinking out loud.

At first i was thinking to try to figure out which was the new record.
Being the IDENTITY is always going up, a MAX() before and after the
INSERT might do it, but that might run into concurrency issues.

Ultimately, the IDENTITY is reliant on a SEQUENCE, and that value can
be grabbed from the SYSIBM VIEW, but that's pretty much unsupported,
but perhaps useful in a bind.

As Serge pointed out, CREATEing your own SEQUENCE would give complete
control over it. Then, the TRIGGER could pop the next value off the
sequence into a local variable, and use it for both TABLEs.

B.

[Serge Rielau]

Brian Tkatch wrote:

On Tue, 13 Mar 2007 07:21:28 -0400, Serge Rielau <sr*****@ca.ibm.com>
wrote:

>Knut Stolze wrote:

>>gu*****************@gmail.com wrote:

I´m having some problem retrieving identity value from my newly
inserted row into a view.

I have two tables T1 and T2 which I define as following

CREATE TABLE T1
(
id BIGINT GENERATED ALWAYS AS IDENTITY
(
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
NO CYCLE
NO ORDER
CACHE 20
),
t1name VARCHAR(10),
CONSTRAINT c_k2_column_reference_id_pk PRIMARY KEY(id)
);

CREATE TABLE T2
(
id BIGINT GENERATED ALWAYS AS IDENTITY
(
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
NO CYCLE
NO ORDER
CACHE 20
),
t2name VARCHAR(10),
t1id BIGINT,
CONSTRAINT c_k2_column_reference_id_pk PRIMARY KEY(id)
);
ALTER TABLE T2
ADD CONSTRAINT t1id_fk
FOREIGN KEY( id )
REFERENCES t1 (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION ;
I created view V 1to combine those two table as following
CREATE VIEW V1 AS
SELECT t1.id,
t1.t1name,
t2.id,
t2.t2name
FROM t1

JOIN t2
ON t2.id = t1.id;

At last I created insert trigger T1 to be able to insert into my view
CREATE TRIGGER t1
INSTEAD OF INSERT ON v1
REFERENCING NEW AS v1new
FOR EACH ROW
MODE DB2SQL
BEGIN ATOMIC
INSERT INTO t1(t1name)
VALUES (v1new.t1name);

INSERT INTO t1( t2name, t1id)
VALUES (v1new.t2name, IDENTITY_VAL_LOCAL()) ;

END;

Now I can insert into my view by doing

INSERT INTO v1 (t1name, t2name) value('test1', 'test2');

but when I call IDENTITY_VALUE_LOCAL() i get null value but I´m
interested in to retrieve id value of table T2 which I´m interested
in.

Can anyone help me on how I can retriveve the identity value of newly
created row by the trigger outside the trigger scope?
How about this:

BEGIN ATOMIC
DECLARE new_value INT;
SELECT id
INTO new_value
FROM NEW TABLE ( INSERT
INTO t1(t1name)
VALUES ( v1new.t1name ) ) AS t;
INSERT
INTO t1(t2name, tlid)
VALUES v1new.t2name, new_value);
END

I don't think that will work.
NEW TABLE contains NULL values for the identity column in the view.
Theory has it that NEW TABLE would pick up SET-ing of new transition
variables in the INSTEAD OF trigger. But that is yet to be implemented
(no plans so far).

Does anything speak against using a SEQUENCE instead of IDENTITY?

PREVIOUS VALUE FOR <sshould work where IDENTITY_VAL_LOCAL let's you down.

Cheers
Serge

Do you think this is a good case for Oracle's RETURNING...INTO clause?
It should work in such a case, where NEW() won't.

No. conceptually NEW TABLE/OLD TABLE is a true superset of RETURNING INTO.
This is about a pothole in INSTEAD OF triggers.

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Knut Stolze]

Serge Rielau wrote:

Knut Stolze wrote:

>gu*****************@gmail.com wrote:

>>I´m having some problem retrieving identity value from my newly
inserted row into a view.

I have two tables T1 and T2 which I define as following

CREATE TABLE T1
(
id BIGINT GENERATED ALWAYS AS IDENTITY
(
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
NO CYCLE
NO ORDER
CACHE 20
),
t1name VARCHAR(10),
CONSTRAINT c_k2_column_reference_id_pk PRIMARY KEY(id)
);

CREATE TABLE T2
(
id BIGINT GENERATED ALWAYS AS IDENTITY
(
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
NO CYCLE
NO ORDER
CACHE 20
),
t2name VARCHAR(10),
t1id BIGINT,
CONSTRAINT c_k2_column_reference_id_pk PRIMARY KEY(id)
);
ALTER TABLE T2
ADD CONSTRAINT t1id_fk
FOREIGN KEY( id )
REFERENCES t1 (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION ;
I created view V 1to combine those two table as following
CREATE VIEW V1 AS
SELECT t1.id,
t1.t1name,
t2.id,
t2.t2name
FROM t1

JOIN t2
ON t2.id = t1.id;

At last I created insert trigger T1 to be able to insert into my view
CREATE TRIGGER t1
INSTEAD OF INSERT ON v1
REFERENCING NEW AS v1new
FOR EACH ROW
MODE DB2SQL
BEGIN ATOMIC
INSERT INTO t1(t1name)
VALUES (v1new.t1name);

INSERT INTO t1( t2name, t1id)
VALUES (v1new.t2name, IDENTITY_VAL_LOCAL()) ;

END;

Now I can insert into my view by doing

INSERT INTO v1 (t1name, t2name) value('test1', 'test2');

but when I call IDENTITY_VALUE_LOCAL() i get null value but I´m
interested in to retrieve id value of table T2 which I´m interested
in.

Can anyone help me on how I can retriveve the identity value of newly
created row by the trigger outside the trigger scope?

How about this:

BEGIN ATOMIC
DECLARE new_value INT;
SELECT id
INTO new_value
FROM NEW TABLE ( INSERT
INTO t1(t1name)
VALUES ( v1new.t1name ) ) AS t;
INSERT
INTO t1(t2name, tlid)
VALUES v1new.t2name, new_value);
END

I don't think that will work.

NEW TABLE contains NULL values for the identity column in the view.
Theory has it that NEW TABLE would pick up SET-ing of new transition
variables in the INSTEAD OF trigger. But that is yet to be implemented
(no plans so far).

Does anything speak against using a SEQUENCE instead of IDENTITY?

PREVIOUS VALUE FOR <sshould work where IDENTITY_VAL_LOCAL let's you
down.

Cheers
Serge

Actually, it does because I was selecting the generated identity value from
the underlying base table. It's just that you can't use SELECT INTO in the
trigger body. Resorting to a SET ... = ( SELECT ... ) fixes that:

CREATE TABLE t1 (
a INT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
b INT
)@
CREATE TABLE t2 (
a INT NOT NULL,
c INT,
CONSTRAINT a_fk FOREIGN KEY(a) REFERENCES t1(a)
ON DELETE CASCADE
)@

CREATE VIEW v AS
SELECT b, c
FROM t1 JOIN t2 ON t1.a = t2.a@

CREATE TRIGGER t INSTEAD OF INSERT ON v
REFERENCING NEW AS n
FOR EACH ROW
BEGIN ATOMIC
DECLARE id INT;
SET id = ( SELECT a
FROM NEW TABLE ( INSERT INTO t1(b) VALUES (n.b) ) AS t );
INSERT INTO t2 VALUES (id, n.c);
END@

INSERT INTO v VALUES (2, 3)@

SELECT * FROM t1@

A B
----------- -----------
1 2

1 record(s) selected.

SELECT * FROM t2@

A C
----------- -----------
1 3

1 record(s) selected.

--
Knut Stolze
DB2 z/OS Admin Enablement
IBM Germany

[Serge Rielau]

Knut Stolze wrote:

Serge Rielau wrote:

>Knut Stolze wrote:

>>gu*****************@gmail.com wrote:

I´m having some problem retrieving identity value from my newly
inserted row into a view.

I have two tables T1 and T2 which I define as following

CREATE TABLE T1
(
id BIGINT GENERATED ALWAYS AS IDENTITY
(
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
NO CYCLE
NO ORDER
CACHE 20
),
t1name VARCHAR(10),
CONSTRAINT c_k2_column_reference_id_pk PRIMARY KEY(id)
);

CREATE TABLE T2
(
id BIGINT GENERATED ALWAYS AS IDENTITY
(
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
NO CYCLE
NO ORDER
CACHE 20
),
t2name VARCHAR(10),
t1id BIGINT,
CONSTRAINT c_k2_column_reference_id_pk PRIMARY KEY(id)
);
ALTER TABLE T2
ADD CONSTRAINT t1id_fk
FOREIGN KEY( id )
REFERENCES t1 (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION ;
I created view V 1to combine those two table as following
CREATE VIEW V1 AS
SELECT t1.id,
t1.t1name,
t2.id,
t2.t2name
FROM t1

JOIN t2
ON t2.id = t1.id;

At last I created insert trigger T1 to be able to insert into my view
CREATE TRIGGER t1
INSTEAD OF INSERT ON v1
REFERENCING NEW AS v1new
FOR EACH ROW
MODE DB2SQL
BEGIN ATOMIC
INSERT INTO t1(t1name)
VALUES (v1new.t1name);

INSERT INTO t1( t2name, t1id)
VALUES (v1new.t2name, IDENTITY_VAL_LOCAL()) ;

END;

Now I can insert into my view by doing

INSERT INTO v1 (t1name, t2name) value('test1', 'test2');

but when I call IDENTITY_VALUE_LOCAL() i get null value but I´m
interested in to retrieve id value of table T2 which I´m interested
in.

Can anyone help me on how I can retriveve the identity value of newly
created row by the trigger outside the trigger scope?
How about this:

BEGIN ATOMIC
DECLARE new_value INT;
SELECT id
INTO new_value
FROM NEW TABLE ( INSERT
INTO t1(t1name)
VALUES ( v1new.t1name ) ) AS t;
INSERT
INTO t1(t2name, tlid)
VALUES v1new.t2name, new_value);
END

I don't think that will work.

NEW TABLE contains NULL values for the identity column in the view.
Theory has it that NEW TABLE would pick up SET-ing of new transition
variables in the INSTEAD OF trigger. But that is yet to be implemented
(no plans so far).

Does anything speak against using a SEQUENCE instead of IDENTITY?

PREVIOUS VALUE FOR <sshould work where IDENTITY_VAL_LOCAL let's you
down.

Cheers
Serge

Actually, it does because I was selecting the generated identity value from
the underlying base table. It's just that you can't use SELECT INTO in the
trigger body. Resorting to a SET ... = ( SELECT ... ) fixes that:

Knut,

You are missing the point. The OP wants to retrieve the generated
identity value from the VIEW.
So in that example of yours "ID" needs to be the column in the new
transition variable which is not allowed.
We thought long and hard whether it's worth providing that support and
decided against it in the end... for now

Cheer
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Knut Stolze]

Serge Rielau wrote:

You are missing the point. The OP wants to retrieve the generated
identity value from the VIEW.

Right, I missed that.

--
Knut Stolze
DB2 z/OS Admin Enablement
IBM Germany

[gunnar.sigurjonsson]

Thanks for your replies. I´ll probably just use stored procedure
instead of trigger to insert directly into the tables behind the view
instead of using trigger. Then I retrieve the id as an OUT parameter
in the procedure. But it would have been much cleaner to make the
trigger do the job.

CREATE PROCEDURE v_instert
(
OUT out_t2id BIGINT,
IN in_t1name VARCHAR(10),
IN in_t2name VARCHAR(10)
)

LANGUAGE SQL
SPECIFIC v_instert
NOT DETERMINISTIC
MODIFIES SQL DATA
CALLED ON NULL INPUT

BEGIN

INSERT INTO t1(t1name)
VALUES (in_t1name);

INSERT INTO t1( t2name, t1id)
VALUES (in_t2name, IDENTITY_VAL_LOCAL()) ;

SET out_t2id = IDENTITY_VAL_LOCAL ();

END;

[Brian Tkatch]

On Tue, 13 Mar 2007 12:02:45 -0400, Serge Rielau <sr*****@ca.ibm.com>
wrote:

>Brian Tkatch wrote:

>On Tue, 13 Mar 2007 07:21:28 -0400, Serge Rielau <sr*****@ca.ibm.com>
wrote:

>>Knut Stolze wrote:
gu*****************@gmail.com wrote:

I´m having some problem retrieving identity value from my newly
inserted row into a view.
>
I have two tables T1 and T2 which I define as following
>
CREATE TABLE T1
(
id BIGINT GENERATED ALWAYS AS IDENTITY
(
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
NO CYCLE
NO ORDER
CACHE 20
),
t1name VARCHAR(10),
CONSTRAINT c_k2_column_reference_id_pk PRIMARY KEY(id)
);
>
CREATE TABLE T2
(
id BIGINT GENERATED ALWAYS AS IDENTITY
(
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
NO CYCLE
NO ORDER
CACHE 20
),
t2name VARCHAR(10),
t1id BIGINT,
CONSTRAINT c_k2_column_reference_id_pk PRIMARY KEY(id)
);
ALTER TABLE T2
ADD CONSTRAINT t1id_fk
FOREIGN KEY( id )
REFERENCES t1 (id)
ON DELETE NO ACTION
ON UPDATE NO ACTION ;
>
>
I created view V 1to combine those two table as following
CREATE VIEW V1 AS
SELECT t1.id,
t1.t1name,
t2.id,
t2.t2name
FROM t1
>
JOIN t2
ON t2.id = t1.id;
>
At last I created insert trigger T1 to be able to insert into my view
CREATE TRIGGER t1
INSTEAD OF INSERT ON v1
REFERENCING NEW AS v1new
FOR EACH ROW
MODE DB2SQL
BEGIN ATOMIC
INSERT INTO t1(t1name)
VALUES (v1new.t1name);
>
INSERT INTO t1( t2name, t1id)
VALUES (v1new.t2name, IDENTITY_VAL_LOCAL()) ;
>
END;
>
>
>
Now I can insert into my view by doing
>
INSERT INTO v1 (t1name, t2name) value('test1', 'test2');
>
but when I call IDENTITY_VALUE_LOCAL() i get null value but I´m
interested in to retrieve id value of table T2 which I´m interested
in.
>
Can anyone help me on how I can retriveve the identity value of newly
created row by the trigger outside the trigger scope?
How about this:

BEGIN ATOMIC
DECLARE new_value INT;
SELECT id
INTO new_value
FROM NEW TABLE ( INSERT
INTO t1(t1name)
VALUES ( v1new.t1name ) ) AS t;
INSERT
INTO t1(t2name, tlid)
VALUES v1new.t2name, new_value);
END

I don't think that will work.
NEW TABLE contains NULL values for the identity column in the view.
Theory has it that NEW TABLE would pick up SET-ing of new transition
variables in the INSTEAD OF trigger. But that is yet to be implemented
(no plans so far).

Does anything speak against using a SEQUENCE instead of IDENTITY?

PREVIOUS VALUE FOR <sshould work where IDENTITY_VAL_LOCAL let's you down.

Cheers
Serge

Do you think this is a good case for Oracle's RETURNING...INTO clause?
It should work in such a case, where NEW() won't.

No. conceptually NEW TABLE/OLD TABLE is a true superset of RETURNING INTO.
This is about a pothole in INSTEAD OF triggers.

It's not a bug, it's a feature! :)

So, conceptually, NEW is better.... well, yeah, i actually understand
that. But RETURNING...INTO is just more convenient and easy to
understand. I wonder then, if it would work here.

B.

[Brian Tkatch]

On 14 Mar 2007 03:07:57 -0700, gu*****************@gmail.com wrote:

>Thanks for your replies. I´ll probably just use stored procedure
instead of trigger to insert directly into the tables behind the view
instead of using trigger. Then I retrieve the id as an OUT parameter
in the procedure. But it would have been much cleaner to make the
trigger do the job.

CREATE PROCEDURE v_instert
(
OUT out_t2id BIGINT,
IN in_t1name VARCHAR(10),
IN in_t2name VARCHAR(10)
)

LANGUAGE SQL
SPECIFIC v_instert
NOT DETERMINISTIC
MODIFIES SQL DATA
CALLED ON NULL INPUT

BEGIN

INSERT INTO t1(t1name)
VALUES (in_t1name);

INSERT INTO t1( t2name, t1id)
VALUES (in_t2name, IDENTITY_VAL_LOCAL()) ;

SET out_t2id = IDENTITY_VAL_LOCAL ();

END;

Once you're going to do that, why not use a SEQUENCE and pop the value
yourself? That could even be done in a TRIGGER.

B.

[Serge Rielau]

Brian Tkatch wrote:

It's not a bug, it's a feature! :)

So, conceptually, NEW is better.... well, yeah, i actually understand
that. But RETURNING...INTO is just more convenient and easy to
understand. I wonder then, if it would work here.

You can see RETURNING INTO as SYNTACTIC SUGAR for a straight SELECT FROM
INSERT. As such it cannot do anything SELECT FROM INSERT can't.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Brian Tkatch]

On Fri, 23 Mar 2007 08:36:49 -0400, Serge Rielau <sr*****@ca.ibm.com>
wrote:

>Brian Tkatch wrote:

>It's not a bug, it's a feature! :)

So, conceptually, NEW is better.... well, yeah, i actually understand
that. But RETURNING...INTO is just more convenient and easy to
understand. I wonder then, if it would work here.

You can see RETURNING INTO as SYNTACTIC SUGAR for a straight SELECT FROM
INSERT. As such it cannot do anything SELECT FROM INSERT can't.

Cheers
Serge

Including bulk INSERTS? RETURNING INTO allows a host variable to
retrieve an array after a bulk INSERT. Very convenient for knowing the
new ids assigned in an operation.

B.

[Serge Rielau]

Brian Tkatch wrote:

Including bulk INSERTS? RETURNING INTO allows a host variable to
retrieve an array after a bulk INSERT. Very convenient for knowing the
new ids assigned in an operation.

That is not a feature of INSERT RETURNING that is a feature of having
ARRAYs.
The SQL would look like:
SELECT ARRAY_AGG(myphones) FROM NEW TABLE(INSERT ....)

It's all about orthogonality. Like playing LEGO (classic)

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Brian Tkatch]

On Fri, 23 Mar 2007 17:12:48 -0400, Serge Rielau <sr*****@ca.ibm.com>
wrote:

>Brian Tkatch wrote:

>Including bulk INSERTS? RETURNING INTO allows a host variable to
retrieve an array after a bulk INSERT. Very convenient for knowing the
new ids assigned in an operation.

That is not a feature of INSERT RETURNING that is a feature of having
ARRAYs.
The SQL would look like:
SELECT ARRAY_AGG(myphones) FROM NEW TABLE(INSERT ....)

It's all about orthogonality. Like playing LEGO (classic)

Cheers
Serge

Good point. :)

B.