Ensure two dates fields in an oracle record are within certain range, so it will not insert records beyond these two dates. A safe guard.

I have table BatchRun.  that has two date fields  lastrun_date and newrun_date.
Is there a way so I can set up this table so that  it will not allow any inserts where the difference between the two dates is greater than 5 days?
ALad2005Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Alexander Eßer [Alex140181]Software DeveloperCommented:
Depending on your needs, you could use a constraint:
alter table batchrun add constraint timediff check ((newrun_date - lastrun_date) < 6));

Open in new window

or maybe put that check inside a before insert trigger and raise an exception... There are quite a few possibilities ;-)
0
slightwv (䄆 Netminder) Commented:
I would go with the constraint method above.

I was already working on the trigger solution so here it is in case you prefer it over the constraint:
create or replace trigger batchrun_date_check_trig
before insert on batchrun
for each row
begin
 if :new.newrun_date-:new.lastrun_Date > 5 then
	raise_application_error(-20001, 'New date more than 5 days from last date.');
 end if;
end;
/

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ALad2005Author Commented:
Thanks Alexander.   How would the trigger look for this case?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

ALad2005Author Commented:
Thanks Slighw you just answered my question to Alex
0
ALad2005Author Commented:
I having difficulity with both Trigger and Constraint.

I should not be allowed to change the dates if the difference between two date is  greater than 5..
and if another field on the same table called Job_name='Daily'
Other job names or ok.
I guess  I missed the second condition.
If you can help with both cases (Trig and Contraints)

Will my trigger look like this?
CREATE OR REPLACE TRIGGER batchrun_date_check_trig
   BEFORE INSERT
   ON SIMDATA.BATCH_RUN_DATE
   FOR EACH ROW
BEGIN
   IF :new.job_name = 'JOB_AUTOREN' THEN
      IF :new.new_run_date - :new.last_run_Date > 5
      THEN
         raise_application_error (
            -20001,
            'New date more than 5 days from last date.');
      END IF;
    END IF;
END;
/


-----
constraint?
0
ALad2005Author Commented:
meant to say Job_name='Daily'
in new.job_name = 'JOB_AUTOREN' THEN
0
Alexander Eßer [Alex140181]Software DeveloperCommented:
alter table batchrun add constraint timediff check (job_name <> 'Daily' or (job_name = 'Daily' and (newrun_date - lastrun_date) < 6)));

Open in new window

0
slightwv (䄆 Netminder) Commented:
Why two IFs?


If :new.new_run_date - :new.last_run_Date > 5 and :new.job_name != 'Daily' then

Constraint:
alter table tab1 add constraint timediff check ((newrun_date - lastrun_date) < 6 and job_name != 'Daily');
0
Alexander Eßer [Alex140181]Software DeveloperCommented:
Other job names or ok.
That's why you'll need 2 parts.
0
slightwv (䄆 Netminder) Commented:
@ALad2005,

Please confirm the original answer you selected wasn't correct.  I believe the statement Alex challenged was a typographical error.

Based on the actual code block you posted, I still don't believe you need an OR statement.
0
ALad2005Author Commented:
Thank you both of you. I value both Alex and Slighw's expert knowledge,
I got answer constraint solution first from  Alex and
 Trigger solution first from Slightw,
Of course I asked further questions and condition, and later we got  final constraint and triggers.
You guys helped me with this two ideas and I am further modifying these two solutions as need from users change.
Bottom line,  I am using both solutions in a different context and I am thankful to you both,
0
ALad2005Author Commented:
I am not sure how the point system is working,   but looks like I am no longer able to split awarding points.
0
Alexander Eßer [Alex140181]Software DeveloperCommented:
I apologize for my rude behavior and I hope you can advance in your development process with our help.

Cheers
1
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.