Select Query

See more:

Oracle

Let you guys please examine the following scenario:
I have a table called Salesman_customer and want to use this table for enter customers and their respective salesman, with a condition that. at a time a customer can belongs to only one salesman during a time period(eg:Customer A belongs to Salesman1 during 1-Oct-2010 to 30_Oct-2010, during this month(october) no other salesman can't Add A as his/her customer, but it is possible to add before October or after October )

Now please examine the condition:
1.A is a Customer of salesman1 From 1-Oct-2010 to ----

if the table contain record of case1. the users should not be able enter the record of following type

a. Can't enter A as customer during 1-Oct-2010 to -----
b.can't enter A as Customer during greater than October(becoz the TO_DATE) is null

I acheived this with the following query

AND ((MC_FM_DT >= :NEW.MC_FM_DT and MC_TO_DT <= :NEW.MC_TO_DT)
                        OR MC_TO_DT Is Null);

but my problem is am not able add A as a Customer for the period 1-Sep-2010 to 30-Sep-2010 with my query.when i try to enter like this it return message displaying record already exist but actually there is no such record.

Some one please help me to modify the query without violating the above condition(no change on the query but some addition).