| inserting ID, SYSDATE and USER automatically. [message #469632] |
Thu, 05 August 2010 06:15  |
ahazin
Messages: 39
Registered: August 2010
|
Member |
|
|
Hi guys, I have created a datablock and set up the user interface to allow the users to insert all the data they need, however my table has some fields which should be generated automatically and are not done through user input:
id - which is uniqe to each entry to the table so is gernerate by a sequence (which I created on the database) but when i put seq.nextval into the initial value on the datablock it says i cannot do this, so how do I use the sequence to insert the value into that field in the database. This is my primary key in my database whichobviosuly my users cant input data to, instead it is generated automatically.
User - similarly in the datablock is a user field which states the user that inserted that record, I know on the database i can put DEFAULT user for that field , but how do I make the user insert automatically through forms.
Date - Similarly again how do I set SYSDATE to be automatically input to the date field when the user submits the record?
Any help on this would be GREATLY appreciated.
Thanks in advance.
|
|
|
|
|
|
| Re: inserting ID, SYSDATE and USER automatically. [message #469652 is a reply to message #469632] |
Thu, 05 August 2010 06:47  |
ahazin
Messages: 39
Registered: August 2010
|
Member |
|
|
I dont have a pre insert trigger (probably because there isnt a text box visible on the screen to the users) but i do have a pre-text-item trigger?
I have the sequence stored on the database, so in forms am i still able to type NAM_SEQ.nextval in the appropriate trigger?
Also since the pre insert trigger isnt there where should i put the code.
Thanks for your help so far, greatly appreciated.
|
|
|
|
|
|
| Re: inserting ID, SYSDATE and USER automatically. [message #469675 is a reply to message #469632] |
Thu, 05 August 2010 07:49 |
ahazin
Messages: 39
Registered: August 2010
|
Member |
|
|
I done what you suggested and get this error now for some reason, have i left something important out?
ORA-00036: maximum number of recursive SQL levels (50) exceeded
ORA-06512: at "STU_TRIGGER", line 3
ORA-04088: error during execution of trigger STU_TRIGGER
ORA-06512: at "TASK_SETUP", line 3
The code in my trigger is:
CREATE OR REPLACE TRIGGER STU_TRIGGER
BEFORE INSERT ON ZZTEST FOR EACH ROW
BEGIN
INSERT INTO ZZTEST (ID, DATE_CREATED, USER_CREATED)
VALUES (MY_SEQ.nextval, SYSDATE, USER);
END;
Again thank you for your input so far.
|
|
|
|
| Re: inserting ID, SYSDATE and USER automatically. [message #469677 is a reply to message #469675] |
Thu, 05 August 2010 07:56 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Pre-insert is a form trigger (block level).
You create it in the form.
I said nothing about creating db triggers.
The error should be obvious, you've got a trigger that runs for every insert on that table in the DB.
It inserts into the same table.
Which fires the trigger again.
Which inserts into the same table.
Which fires the trigger again.
repeat until oracle realises you've done something stupid and errors out.
I think you need to spend some time reading the form builder documentation.
And the DB developer documentation since you don't understand how DB triggers work either.
|
|
|
|
| Re: inserting ID, SYSDATE and USER automatically. [message #469684 is a reply to message #469677] |
Thu, 05 August 2010 08:13 |
ahazin
Messages: 39
Registered: August 2010
|
Member |
|
|
sorry, i wasnt aware the pre insert was on the block level, i found that trigger now. I just input:
INSERT INTO ZZTEST (ID, DATE_CREATED, USER_CREATED)
VALUES (MY_SEQ.nextval, SYSDATE, USER);
into that trigger. It still says however that cannot insert null value into ID, is this due to the fact that i created the sequence on the database and im putting the above code into forms or does it not matter that the sequence is stored on the db? maybe im just doing something else stupid.
I know i should possibly read more but i seem to learn more from pradtical experiance and so far this has been the only thing causing me bother, granted I know its something simple but i just cant seem to get it to work.
|
|
|
|
| Re: inserting ID, SYSDATE and USER automatically. [message #469687 is a reply to message #469684] |
Thu, 05 August 2010 08:30 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Pre-insert doesn't fire instead of the default forms insert. It fires before it.
So coding the insert in there is a waste of time.
Use the trigger to assign the values to the datablock items.
The way you've coded it your insert will insert a record with values for those three columns but none of the values entered by the user.
Then forms default insert functionality will insert a row with the values specified by the user but null for the three columns you want to set - hence the error.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
