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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Alex [***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 ;-)
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

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?
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

ALad2005Author Commented:
Thanks Slighw you just answered my question to Alex
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?
ALad2005Author Commented:
meant to say Job_name='Daily'
in new.job_name = 'JOB_AUTOREN' THEN
Alex [***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

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');
Alex [***Alex140181***]Software DeveloperCommented:
Other job names or ok.
That's why you'll need 2 parts.
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.
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,
ALad2005Author Commented:
I am not sure how the point system is working,   but looks like I am no longer able to split awarding points.
Alex [***Alex140181***]Software DeveloperCommented:
I apologize for my rude behavior and I hope you can advance in your development process with our help.

Cheers
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.