Maybe use a insert table trigger and search for that user and trunc(
sysdate )?
"Yuval Sverdlik" <ysverdlik_at_nj.rr.com> wrote in message
news:VDKy7.13196$T5.2327301_at_typhoon.nyc.rr.com...
> I have a table that I'm creating. (a customer table) I need to figure
out
> how to get a constraint where a customer can have only one order per day.
> Here is the table below. I know that if I TRUNC the date it will make the
> time portion of the date to 12:00am and therefore if sysdate is used the
> date will still be the same throughout the day.
>
> Now if I use sysdate I can insert two of the same customers with sysdate
> because the date is really not the same even if i did it at the same time.
>
> Please help this beginner:
>
> Thanks
>
> CREATE table Ordr_Data
> (
> Order_id NUMBER(10) CONSTRAINT pk_order_data_order_id PRIMARY KEY,
> Cust_id NUMBER(5) NOT NULL,
> Sales_rep_id NUMBER(5) NOT NULL,
> Prod_id NUMBER(10) NOT NULL CONSTRAINT uq_ordr_data_prod_id UNIQUE,
> Quantity NUMBER NOT NULL CONSTRAINT ck_quantity CHECK(Quantity <= 1000),
> Order_date DATE NOT NULL CONSTRAINT uq_order_date UNIQUE,
> Order_Stat VARCHAR2(10) NOT NULL,
> Ship_Date DATE NOT NULL,
> Org_User_id NUMBER(5) NOT NULL,
> Org_User_Date DATE NOT NULL,
> Upd_User_id NUMBER(5),
> Upd_User_Date DATE,
> CONSTRAINT fk_ordr_data_cust_id FOREIGN KEY (Cust_id) REFERENCES
Cust_Data,
> CONSTRAINT fk_ordr_data_Sales_rep_id FOREIGN KEY (Sales_rep_id) REFERENCES
> Emp_data,
> CONSTRAINT fk_ordr_data_Prod_id FOREIGN KEY (Prod_id) REFERENCES
Prod_data,
> CONSTRAINT uq_Ordr_Data_Custid_ordrdt UNIQUE (cust_id,Order_date),
> CONSTRAINT ck_Order_date CHECK(order_date>
> to_date('01/01/2001','dd/mm/yyyy')));
>
>
>
Received on Mon Oct 15 2001 - 20:36:34 CDT
