Toad World® Forums

Testing INSERT with triggered ID field



I am new to Code Tester and I am trying to test if a procedure inserts a row correctly. My idea was to insert a row into the local table with the expected value, hence it fails. I guess the problem is, that this table has an ID field as primary key, which value increases dynamically with a trigger + sequence, so I cannot know which value is inserted into this field. If I leave this field blank, he says I cannot empty NULL into ID.

How can I resolve this issue and check if the correct values are inserted?

Thanks in advance,



This is what I would normally do:

  1. Make sure that you have full control over your database, such that no other Oracle sessions will access your sequence.

  2. Save the current value of the sequence. Then reset your sequence such that you know exactly which value it will return next. Eg if the largest value of your primary key in the table is currently 999, you could increase the sequence value to 100,000 by calling NEXTVAL on the sequence until it reaches that value (you can find PL/SQL code doing this via Google).

  3. Run the Program Under Test.

  4. Verify the data inserted with the primary key value expected.

  5. Reset sequence back to what it was before – again Google for PL/SQL code that does this.

  6. and 5. would be achieved through customized setup/teardowm code in Code Tester such that it’s part of running the unit test.

I recommend that unit testing is done in a database that is developer-specific, ie, each developer has their own development database instance, eg on their desktop.

Hope this helps.