Re: How to TRUNC ???

Yuval Sverdlik wrote:
>
> 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')));

Create a unique constraint on customer and order_date.

Then with an insert trigger, ensure that the order_date is truncated to be just the date component.

create or replace
trigger blah before insert or update on customer for each row
begin
 :new.order_date := trunc(:new.order_date); end;

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."