By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,851 Members | 1,746 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,851 IT Pros & Developers. It's quick & easy.

INSERT INTO ... FROM NEW TABLE

P: n/a
I try to retrieve the value of the identity-column of a table:

SELECT PLAYER_ID INTO O_PLAYER_ID
FROM NEW TABLE(INSERT INTO GAMESCORE.PLAYER (LAST_NAME, FIRST_NAME,
NICK_NAME, EMAIL) VALUES (I_LAST_NAME, I_FIRST_NAME, I_NICK_NAME,
I_EMAIL)) AS PL;

The table has a "NOT NULL"-Constraint on the column "NICK_NAME".
If I try to insert a record with an empty nick-name, I still get
an IDENTITY-VALUE returned.

It seems like that the IVAL will be returned before DB2 is rolling back
the insert operation, since it is violating a constraint.

Do I miss something or is this behavior purposely by design?

I am using DB2 UDB 8.2 / FP1.

Thank you,

Bernhard

Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Bernhard wrote:
I try to retrieve the value of the identity-column of a table:
SELECT PLAYER_ID INTO O_PLAYER_ID
FROM NEW TABLE(INSERT INTO GAMESCORE.PLAYER (LAST_NAME, FIRST_NAME,
NICK_NAME, EMAIL) VALUES (I_LAST_NAME, I_FIRST_NAME, I_NICK_NAME,
I_EMAIL)) AS PL;
The table has a "NOT NULL"-Constraint on the column "NICK_NAME".
If I try to insert a record with an empty nick-name, I still get
an IDENTITY-VALUE returned.
It seems like that the IVAL will be returned before DB2 is rolling back
the insert operation, since it is violating a constraint.
Do I miss something or is this behavior purposely by design?

Can you post a self contained example including a dump of the error
message. I suspect we will end up with a red-herring here.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.