create constraint using date ranges

how rigorous do you want your rule to be?

if you want multi-session blocking then it's more difficult because the only way to prevent it is to lock the table.

otherwise I could create the first row, you could create the second row and neither of us would have a violation until we committed.

or, are you just looking for something that blocks a single user from messing up the data?

Hello,

it is the latter. I don't need to worry about multiple people making updates. It's just blocking a single user from messing up the data.

Off the top of my head, a trigger?

It won't protect against what sdstuber mentioned with uncommitted transactions.

drop table tab1 purge;

create table tab1(HealthPlan char(4),
ServiceCode char(3),
StartDate	date,
EndDate	date
)
;

insert into tab1 values('BCBS','XXY',to_date('1/1/2009','MM/DD/YYYY'), to_date('12/31/2011','MM/DD/YYYY'));
commit;


create or replace trigger tab1_trig
before insert or update on tab1
for each row
declare
	junk number;
begin
	select 1 into junk from tab1 where
		(:NEW.startdate between startdate and enddate or :NEW.enddate between startdate and enddate)
		and rownum=1;

	raise_application_error(-20001, 'Cannot insert.  Would cause overlap.');
	exception
		when no_data_found then null;

end;
/

show errors

insert into tab1 values('BCBS','XXY',to_date('1/1/2011','MM/DD/YYYY'), to_date('12/31/2026','MM/DD/YYYY'));

Select allOpen in new window

also, an after statement allows you to adjust multiple rows at once to fix a problem
row-level trigger could make you move one row "out of the way" of an other row, fix the other row, then put the first row back.


if you use this,  I recommend creating a job that does something like this nightly

insert into yourtable select * from yourtable where 1=0;  


it's a silly query that shouldn't do anything,  but the fact that it is an insert statement will force my trigger to fire and notify you if there is any invalid data in the table caused by multi-session problem mentioned in the first post.

this doesn't prevent the problem but lets you find it easily and hopefully timely enough that it doesn't cause major issues elsewhere in the app

to demonstrate the problem with row level trigger
modify slightwv's example above

change the second insert to be this...

UPDATE yourtable  SET startdate  = startdate;

Select allOpen in new window

you will get a mutating table error even though there is only one row in the table and the update doesn't really do anything

experts,

Thanks. Both of these work great with my example.I have received help from both of you in the past and am very much appreciative. What is the best way to award points in this case? Both of you were prompt and accurate. Is splitting the points fair? At this very moment, I don't know exactly which trigger will be used until I've met with others in the group.   Thank you!

don't use the row trigger from slightwv

it is not reliable.

If you ran the modified test case I described above you should have gotten an error
if you did not, then you have a bug you need to file with oracle.

the row level trigger is wrong

oops,  I'm sorry, to modify slightwv's example, try this ...
 (yourtable is what I called the example table, he called it tab1)

 UPDATE tab1 SET startdate  = startdate; 

Select allOpen in new window

Ok, I did get that error. One last thing I'd like to get an opinion on is the PK. Before the date ranges came into play, we had a PK of HealthPlan & ServciceCode. Since we now have the date range issue, that is no longer an option.  Any suggestions?

healthplan, servicecode, startdate

that should be unique,  you could use enddate instead of startdate but don't use both