You're trying to put 'OTC' into the column rate_code, and that looks okay, but it has nothing to do with the error you're getting. The error message says that you need a (non-NULL) value for the column resv_num., but you never gave one.
Ways to fix the problem include
YES Frank Kulash i see what you mean, i nee dto put all the values to i can add it to the table, i should have been more specific in the actuall problem
the actual problem is when i do choose "ntc" it does not save it will go back to the old one
but when i choose 'otc" first and then click on 'ntc' it does save it, so im wondering why
Sorry, I can't recreate the problem.
If you can post CREATE TABLE and INSERT statements for a little sample data in all the relevant tables (as they exist before the trigger fires), an INSERT statement that cause the error, and the results you'd like to see from that INSERT statement (that is, the state of the changed table after everything finishes) then I could try it. It would help a lot if you could post a much shorter version of the query that causes the same problem. I know that's a lot of work, and you may feel it's not worth your time.
Sheez. Bluntly put. Cr@p code.
Why? Severe and total lack of modularisation.
And modularised structured code is the very CORE of software engineering.
It is truly sad to see such code in production. (I long since ceased to be horrified by such code)
If you really want to call yourself a programmer and software engineer - fix it. Modularise it.
Or spend the rest of your career as developer dealing with miserable problems like you are doing at the moment.
I suspect that when NTC is used in certain cases, the drop down or something in the frontdesk screen is looking at rate code in p_resv_unit and it is NULL.
Not sure still able to talk about event programming but almost sure it has nothing to do with the procedure logic which simply takes the input values from application variables either null or not null.
When the sheet (screen) with drop down list is initiated and opened (check the application codes on these events), p_resv_unit should be assigned with the first value shown in the list or null if it is blank when opened. And p_resv_unit will be reassigned each time clicking event happens.
billyverreynne just so you know is not my code, and is not easy to fix.
but thanks for advice....
That does not change the need for the code to be refactored. And refactored mercilessly.
A code unit (procedure, function or method) needs to do ONE thing only, and do it well.
For example, this code (my comments as program comments in green):
begin -- here is modularisation resv_rec := dml_p_reservation.get_rec( pnResvNum 0: -- here is a module embedded in another module -why is this code block not a function?? begin select * into resvunit_rec from p_resv_unit where resv_eff_date = resv_rec.arrival_date and resv_num = resv_rec.resv_num and rownum < 2; exception when others then -- should be exception NO_DATA_FOUND and not generic resvunit_rec := null; end;
Also - what is the reason for the rownum criteria? Why is the reason for selecting a random row matching the criteria, not documented in program comments?
Code like thus MUST be fixed. It is not optional in my book. Unless you want it to grow worse, become more bug ridden, more unstable, more unmaintainable, and more of a liability to the business processes that the code is suppose to support? CODE LIKE THIS IS A RISK TO BUSINESS!
So I'll say it again. Code is cr@p. Fix it.