Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2003
    Posts
    2,296

    Unanswered: check constraint or trigger?

    I am pretty sure I cannot use a CHECK constraint but thought I would ask.

    I have a date column and I don't want any dates inserted that are greater than sysdate.

    it doesn't seem like I can do this with a CHECK:
    PHP Code:
    ALTER TABLE gateway_logging_gap 
    ADD CONSTRAINT check_gwg_end_dt
    CHECK 
    end_dt to_date(to_char(sysdate+(5/24),'MM-DD-YYYY HH24:MI:SS'),'MM-DD-YYYY HH24:MI:SS'));
                                     *
    ERROR at line 3:
    ORA-02436date or system variable wrongly specified in CHECK constraint 
    then if I write a trigger for it not to insert, I just write an exception clause and it won't insert?
    PHP Code:
    create or replace trigger check_gwg_end_dt
        before insert on GATEWAY_LOGGING_GAP
        
    for each row
            
    declare
                
    v_date   date;
                        
    bad_date EXCEPTION;
            
    begin
                select sysdate
    +(5/24into v_date from dual;
            if 
    INSERTING then
                
    if :new.end_dt >  v_date then RAISE bad_date
                end 
    if;
            
    EXCEPTION
                when bad_date THEN
                RAISE_APPLICATION_ERROR 
    (-20001'Date in future is not allowed');
            
    end if;
    end;

    - The_Duck
    you can lead someone to something but they will never learn anything ...

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    I run this and I get an error other than my exception.
    ANyone know why?

    The trigger works however.
    PHP Code:

    kod
    :Platformcreate or replace trigger check_gwg_end_dt
      2   before insert 
    or update on GATEWAY_LOGGING_GAP
      3   
    for each row
      4      when 
    (new.end_dt sysdate+(5/24))
      
    5    declare
      
    6    begin
      7    
      8     RAISE_APPLICATION_ERROR 
    (-20001'Date in future is not allowed');
      
    9  
     10  end
    ;
     
    11  /

    Trigger created.

     
    kod:Platform
     
    kod:Platforminsert into gateway_logging_gap values (
       
    2  '4','0002E31C5FDE',sysdate,sysdate+4);
    insert into gateway_logging_gap values (
                 *
    ERROR at line 1:
    ORA-20001Date in future is not allowed
    ORA
    -06512at "PLATFORM.CHECK_GWG_END_DT"line 4
    ORA
    -04088error during execution of trigger 'PLATFORM.CHECK_GWG_END_DT' 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi J,

    You've done a to_char on sydate but I would guess (if end_dt is a date type) that you don't need it...

    CHECK ( end_dt < (sysdate+(5/24));

    I'm not sure what the 5/24 is for though. I would suggest end_dt < sysdate is fine. I haven't checked or tried it though.

    If the condition for the constraint won't accept sysdate, maybe it will accept a function call which returns sysdate?

    If none of the above, then yes, you are into a trigger.

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    bah!
    it doesn't work.

    Code:
    09:38:57 kod:platform> 
    09:38:57 kod:platform> ALTER TABLE gateway_logging_gap 
    09:39:20   2  ADD CONSTRAINT check_gwg_end_dt
    09:39:20   3  CHECK ( end_dt < sysdate);
    CHECK ( end_dt < sysdate)
                     *
    ERROR at line 3:
    ORA-02436: date or system variable wrongly specified in CHECK constraint
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi J,

    Yes, the docs aren't clear on whether you can use sysdate in a constraint, the error message seems to suggest so but no idea what exactly the rules are.

    Also, for some reason it doesn't seem to like having sysdate in the when clause of the trigger.

    I know this isn't quite what you wanted, but maybe will help..

    PHP Code:
    CREATE OR REPLACE TRIGGER mytrg 
     BEFORE INSERT ON mytable
     REFERENCING 
    NEW AS NEW OLD AS OLD
     
    FOR EACH ROW
    BEGIN
       
    IF :NEW.datecolumn SYSDATE THEN
          raise_application_error
    (-20001,'Invalid date');
       
    END IF;
    END mytrg
    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    Thanks for the help Bill.

    As a follow up, I did some research and found that you will always get those two extra errors when the exception of a trigger fires. Don't know if someone can confirm this or not but that is what my research found.

    It is definitely confusing for the coder since it suggests something wrong in the trigger code.

    the errors I am referring to:
    PHP Code:
    ERROR at line 1:
    ORA-20001Date in future is not allowed
    ORA
    -06512at "PLATFORM.CHECK_GWG_END_DT"line 4
    ORA
    -04088error during execution of trigger 'PLATFORM.CHECK_GWG_END_DT' 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  7. #7
    Join Date
    Dec 2003
    Location
    Wuhan,China
    Posts
    4

    Re: check constraint or trigger?

    what is your oracle version?
    i think the trigger bill given you is right.
    the ora-06512 is memory error,
    it clauses by some reasons,ie it si ora 8.1.7's bug.
    you can see it on oracle's web site.

  8. #8
    Join Date
    Jul 2003
    Posts
    2,296
    my version is 9.2.0.4 on Solaris

    the trigger works.
    I have to add 5 hours to account for GMT
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •