Error 7628 happened in TRANSACTION mode whiling trying to insert a table that has "WITH CHECK OPTION" foreign key≈

Database
Enthusiast

Error 7628 happened in TRANSACTION mode whiling trying to insert a table that has "WITH CHECK OPTION" foreign key≈

Please let me know how to fix this error. How does the CHECK OPTION work in TRANSACTION mode?

Here is my scenario,

1. Say booking_number in table B is defined as foreign key which is the primary key of table A.

2. In my stored procedure, the same booking record is inserted into A and then B.

3. There is no error if the stored procedure is called without TRANSACTION mode.

4. There is also no error if the stored procedure is called with TRANSACTION mode and the foreign key was defined as "WITH NO CHECK OPTION"

5. The error only happens in TRANSACTION mode and  the foreign key is defined as "WITH CHECK OPTION". It looks like in TRANSACTION mode, the booking_number was not considered already inserted into A so the CHECK failed while the booking_number is being inserted into B.

Thanks!

3 REPLIES
Enthusiast

Re: Error 7628 happened in TRANSACTION mode whiling trying to insert a table that has "WITH CHECK OPTION" foreign key≈

Hi,

You have answered your question in the last line, when you use WITH CHECK OPTION , refrences are checked at transaction level. If you are inserting a row in the parent table and then the refrenced row in Child table in same transaction, it will not be succedded. as the the row inserted into A is not committed yet. To make this work, You will have to add two transactions to make this happen, or change it to soft RI WITH NO CHECK OPTION.

Khurram
Senior Apprentice

Re: Error 7628 happened in TRANSACTION mode whiling trying to insert a table that has "WITH CHECK OPTION" foreign key≈

Hi Khurram,

within the transaction the row will be visible without commiting it, the error must be caused by something else.#

@zhiyanshao:

What do you mean by TRANSACTION mode?

ANSI vs. Teradata session mode?

Which client/connection do you use to run the SP?

Could you show the SP source code?

Enthusiast

Re: Error 7628 happened in TRANSACTION mode whiling trying to insert a table that has "WITH CHECK OPTION" foreign key≈

@dnoeth:

sorry for the late response. I am using TRANSACTION in teradata session mode and I use Teradata Express Studio with TMODE=TERA connection on MAC. My stored procedure looks like this:

...

  BEGIN TRANSACTION;

  CALL DBName.DATA_IMPORT(dt, ErrorCode, ErrorMsg);

  IF (ErrorCode = 0) THEN

    END TRANSACTION;

  ELSE

    ROLLBACK;

  END IF;

...

 

REPLACE PROCEDURE DBName.DATA_IMPORT()

BEGIN

CALL DBName.IMPORT_A();

CALL DBName.IMPORT_B();

END

I have to either not use TRANSACTION or use WITH NO CHECK OPTION to make it work. You may create simple tables and try it out. Thanks!