Home » Developer & Programmer » Forms » how to stop from inserting if condition not met (forms 10g)
how to stop from inserting if condition not met [message #442428] Sun, 07 February 2010 23:57 Go to next message
nastyjillu
Messages: 211
Registered: February 2009
Senior Member
hi,

i have master detail datablocks.

master block is course and detail block is trainee.

students can schedule their courses in a location at particular time.

lets assume student1 has scheduled course A1 in location LA1 from 9am to 12 pm.

now second student shouldnt be able to schedule same course at same time in same location.

he can schedule at different time duration. or else same time duration but different location.

or same time, same location but different course.

so, what i did is, i wrote code in when-validate-record of trainee block to display a message saying " cannot schedule course.choose another time." if he trys to insert same time duration or same location

when i am trying to insert a record with time which is already scheduled and in same location, it displays the message but it is also saving the record. i dont want to save the record.

how should i do this? i tried to give ROLLBACK in this trigger but it doesnt accept.

i tried pre-insert trigger also but it is also displaying message but saving the record

my code in when-validate-record is:

declare
	cursor c1 is
	select start_time,end_time,loc 
	from trainee
	where cname=:course.cname;
	
	l_st number;
	l_et number;
	l_loc varchar2(20);
begin
	open c1;
	loop
		fetch c1 into l_st, l_et, l_loc;
		exit when c1%notfound;
	
	if :trainee.loc=l_loc and :trainee.start_time between l_st and l_et then
		
	message('course is registered at this time.choose other time');
	
	
	end if;
	
	end loop;
	close c1;
	
	end;



thanks
jillu
Re: how to stop from inserting if condition not met [message #442429 is a reply to message #442428] Mon, 08 February 2010 00:24 Go to previous messageGo to next message
nastyjillu
Messages: 211
Registered: February 2009
Senior Member
got it

i had to use

RAISE Form_Trigger_Failure;

in my code.

thanks
jillu
Re: how to stop from inserting if condition not met [message #442431 is a reply to message #442429] Mon, 08 February 2010 00:37 Go to previous messageGo to next message
tamzidulamin
Messages: 132
Registered: October 2009
Location: Dhaka
Senior Member
declare
    cursor c1 is
    select start_time,end_time,loc 
    from trainee
    where cname=:course.cname;
    
    l_st number;
    l_et number;
    l_loc varchar2(20);
begin
    open c1;
    loop
        fetch c1 into l_st, l_et, l_loc;
        exit when c1%notfound;
    
    if :trainee.loc=l_loc and :trainee.start_time between l_st and l_et then
        
    message('course is registered at this time.choose other time');
    [b]RAISE Form_Trigger_Failure;[/b]
    
    end if;
    
    end loop;
    close c1;
    
end;


Regards,
Tamzidul Amin.
Re: how to stop from inserting if condition not met [message #442452 is a reply to message #442428] Mon, 08 February 2010 03:56 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
That code would be more efficient without the loop:
DECLARE

  CURSOR c1 IS
  SELECT 1
  FROM trainee
  WHERE cname = :course.cname
  AND loc = :trainee.loc
  AND :trainee.start_time BETWEEN start_time AND end_time;
    
  n number;

BEGIN

  OPEN c1;
  FETCH c1 into n;
  IF c1%FOUND THEN 
    CLOSE c1;    
    message('course is registered at this time.choose other time');
    RAISE form_trigger_failure;
  
  END IF;
  CLOSE c1;
  
END;

The loop could loop over several rows you're not interested in. Only use loops when you're interested in more than one row from the query.
Re: how to stop from inserting if condition not met [message #442516 is a reply to message #442452] Mon, 08 February 2010 17:30 Go to previous messageGo to next message
nastyjillu
Messages: 211
Registered: February 2009
Senior Member
i think i have to use loop even if i follow cursor in your code
as there might be more than one record for given location, and course and start time, end time.

what do you say?
Re: how to stop from inserting if condition not met [message #442517 is a reply to message #442516] Mon, 08 February 2010 17:44 Go to previous message
nastyjillu
Messages: 211
Registered: February 2009
Senior Member
i dont need loop actually, because even if one record exists then i have to the message and close the cursor.

appreciate your help

thanks
jillu
Previous Topic: Deselect all checkboxes except the one which has been selected
Next Topic: Get URL value
Goto Forum:
  


Current Time: Fri Dec 09 15:09:57 CST 2016

Total time taken to generate the page: 0.21209 seconds