| how to stop from inserting if condition not met [message #442428] |
Sun, 07 February 2010 23:57  |
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 #442431 is a reply to message #442429] |
Mon, 08 February 2010 00:37  |
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 |
cookiemonster
Messages: 13938
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.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
