| Pre-insert trigger [message #124775] |
Tue, 21 June 2005 07:52  |
hiredog
Messages: 2
Registered: June 2005
Location: Perth, Australia
|
Junior Member |
|
|
Hi all,
I have a pre-insert trigger which uses a sequence to populate the primary key of a table. This works on its own but when I add further code to use this number to also populate a child table I get an error, and the whole insert fails.
The code I have is:
select id_seq.nextval into s_order.id from dual; -- this works on its own
:s_orderdetails.order_id:= s_order.id; -- once i add this it fails
The error message is 0ra-01400 cannot insert null into ("FORMS"."s_orderdetails"."order_id")
As this is a pre-insert trigger I can't understand why the sequence number is not getting populated into the order_id field?!
Thanks in advance for any help.
Carl
|
|
|
|
| Re: Pre-insert trigger [message #124872 is a reply to message #124775] |
Tue, 21 June 2005 23:26  |
 |
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
|
Senior Member
Account Moderator |
|
|
| Quote: |
select id_seq.nextval into s_order.id from dual; -- this works on its own
:s_orderdetails.order_id:= s_order.id; -- once i add this it fails
|
For starters, 's_order.id' should be ':s_order.id' but I expect that you have the colon but didn't copy it to this thread.
I assume that you have a master detail relationship in your form between the 's_order' and 's_orderdetails' blocks. I believe that the default Oracle relationship code will populate the 's_orderdetails.order_id' field as it is the joining field in the relationship. If not, first try deleting the relationship and then putting it back using the Relationship Wizard. Alternatively, specify the 'Copy Value from Item' property for 's_orderdetails.order_id' to 's_order.id'.
David
PS. Your code will probably work if you put the second line into the 'Post-Insert' trigger.
[Updated on: Wed, 22 June 2005 02:07] Report message to a moderator |
|
|
|
| Re: Pre-insert trigger [message #124895 is a reply to message #124872] |
Wed, 22 June 2005 02:52 |
hiredog
Messages: 2
Registered: June 2005
Location: Perth, Australia
|
Junior Member |
|
|
Thanks for the response, i will try what you suggest. However I am not too sure why a post insert statement of the same effect would work. As I see it....
The transaction is writing to two tables, Orders, and OrderDetails, and I was trying to:
1. Generate a primary key --ok
2. use this to write to the parent table --ok
3. and then also use this to write to the child table --not okay
Would a post insert also fail as i would be trying to write to the child table after the whole transaction, and the post-insert trigger would not fire until after the transaction has committed or rolled back. In other words are you saying :s_orderdetails.order_id becomes populated after the entry to the parent table?? but before the child record commits?
Cheers,
Carl
|
|
|
|
| Re: Pre-insert trigger [message #124898 is a reply to message #124895] |
Wed, 22 June 2005 03:18 |
|
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
|
Senior Member
Account Moderator |
|
|
Basically, I would have to test the post-insert scenario. But you don't need it. Use triggers only when the default Oracle functionality isn't doing it for you. Yes, you populate your own ID in the first block, but I would do it with a database trigger so that I have the same functionality through Forms, TOAD, and SQL*Plus. With Oracle 10g (9i?) you can associate the sequence number code with the definition of the table and the database will handle it for you. Then you don't need the Pre-Insert trigger in your form at all, and TOAD and SQL*Plus will function happily.
David
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
