1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

which CONSTRAINT is used

Discussion in 'SQL PL/SQL' started by kishore garlapati, Nov 5, 2010.

  1. kishore garlapati

    kishore garlapati Active Member

    Messages:
    15
    Likes Received:
    1
    Trophy Points:
    110
    I've table SALES with the columns PID(PK),ORDER_DATE(DATE),DELIVER_DATE(DATE)
    columns.
    I would like to apply a constraint on ORDER_DATE,DELIVER_DATE columns
    with the condition cases are:
    1.ORDER_DATE should be lessthan or equal to the SYSTEM DATE
    2.DELIVER_DATE should br greater than or equal to the SYSTEM DATE
    with which constraint i get the solution please help me.

    NOTE:-CHECK Constraints are not won't allow sysdate functions,is this correct
    please clarify it.
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You cannot use SYSDATE in a check constraint, although you can use a trigger:

    Code (SQL):
     
    SQL>
    SQL> --
    SQL> -- This won't work
    SQL> --
    SQL> CREATE TABLE sales(
      2  pid varchar2(12) NOT  NULL,
      3  order_date DATE,
      4  deliver_date DATE,
      5  CONSTRAINT sales_pk PRIMARY KEY(pid),
      6  CONSTRAINT ord_ck CHECK(order_date <= sysdate),
      7  CONSTRAINT del_ck CHECK(deliver_date >= sysdate)
      8  );
    CONSTRAINT ord_ck CHECK(order_date <= sysdate),
                                          *
    ERROR at line 6:
    ORA-02436: DATE OR system variable wrongly specified IN CHECK CONSTRAINT

    SQL>
    SQL> --
    SQL> -- This will
    SQL> --
    SQL> CREATE TABLE sales(
      2  pid varchar2(12) NOT  NULL,
      3  order_date DATE,
      4  deliver_date DATE,
      5  CONSTRAINT sales_pk PRIMARY KEY(pid)
      6  );
     
    TABLE created.
     
    SQL>
    SQL> CREATE TRIGGER ord_del_trg
      2  BEFORE INSERT OR UPDATE ON sales
      3  FOR each ROW
      4  DECLARE
      5          bad_ord_dt exception;
      6          bad_del_dt exception;
      7          pragma exception_init(bad_ord_dt, -20998);
      8          pragma exception_init(bad_del_dt, -20999);
      9  BEGIN
     10          IF :NEW.order_date > sysdate THEN
     11                  raise bad_ord_dt;
     12          elsif :NEW.deliver_date < sysdate THEN
     13                  raise bad_del_dt;
     14          END IF;
     15  exception
     16          WHEN bad_ord_dt THEN
     17                  raise_application_error(-20998, 'Order date must be less than or equal to the current date');
     18          WHEN bad_del_dt THEN
     19                  raise_application_error(-20999, 'Delivery date must be greater than or equal to the current date');
     20  END;
     21  /
     
    TRIGGER created.
     
    SQL>
    SQL> INSERT INTO sales
      2  VALUES('ABC123456789', sysdate+1, sysdate);
    INSERT INTO sales
                *
    ERROR at line 1:
    ORA-20998: ORDER DATE must be less than OR equal TO the CURRENT DATE
    ORA-06512: at "BING.ORD_DEL_TRG", line 14
    ORA-04088: error during execution OF TRIGGER 'BING.ORD_DEL_TRG'

    SQL>
    SQL> INSERT INTO sales
      2  VALUES('ABC123456789', sysdate, sysdate-1);
    INSERT INTO sales
                *
    ERROR at line 1:
    ORA-20999: Delivery DATE must be greater than OR equal TO the CURRENT DATE
    ORA-06512: at "BING.ORD_DEL_TRG", line 16
    ORA-04088: error during execution OF TRIGGER 'BING.ORD_DEL_TRG'

    SQL>

     
     
  3. kishore garlapati

    kishore garlapati Active Member

    Messages:
    15
    Likes Received:
    1
    Trophy Points:
    110
    SQL> CREATE TRIGGER ord_del_trg
    2 before INSERT OR UPDATE ON sales
    3 FOR each row
    4 declare
    5 bad_ord_dt exception;
    6 bad_del_dt exception;
    7 pragma exception_init(bad_ord_dt, -20998);
    8 pragma exception_init(bad_del_dt, -20999);
    9 begin
    10 IF :new.order_date > sysdate then
    11 raise bad_ord_dt;
    12 elsif :new.deliver_date < sysdate then
    13 raise bad_del_dt;
    14 end IF;
    15 exception
    16 when bad_ord_dt then
    17 raise_application_error(-20998, 'Order date must be less than or equal to the current date');
    18 when bad_del_dt then
    19 raise_application_error(-20999, 'Delivery date must be greater than or equal to the current date');
    20 end;
    21 /

    TRIGGER created.

    SQL>--with ur query there are some errors,please notify those and give correct solution

    SQL> sho err;
    Errors for TRIGGER ORD_DEL_TRG:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    7/6 PLS-00049: bad bind variable 'NEW.ORDER_DATE'
    9/9 PLS-00049: bad bind variable 'NEW.DELIVER_DATE'
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Post your table creation script; I suspect you used full quotes around lower-case or mixed-case column names.