Results 1 to 2 of 2

Thread: Trigger palava

  1. #1
    Join Date
    Oct 2005
    Posts
    4

    Unanswered: Trigger palava

    I have a trigger on my table client of which any client under 16
    cannot not be added on an attempt to insert and 'test fail' would be logged into table test_result.
    when the opposite is done to table client i.e (client over 16) the values should be inserted and 'test pass' should be logged into table test_result.

    The probelem is 'test pass' is logged on and 'test fail' doesn't or at least it is not displayed, I know this because the comment_seq.nextval is used up.

    create or replace trigger age_restriction_comment
    Before insert on client
    For each row
    Declare
    i_comment test_result.comments%type;
    Begin
    if
    ( add_months(:new.age,12*16) >= trunc(sysdate) )
    THEN
    i_comment := 'test fail';
    else
    i_comment := 'test pass';
    end if;
    insert into test_result (test_id, comments) values (comment_seq.nextval, i_comment);
    end age_restriction_comment;
    /

    below is what my tbl test_result looks like.


    TEST_ID COMMENTS
    comment_seq.nextval i_comment
    1 test pass
    4 test pass
    6 test pass
    8 test pass

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    As you didn't provide table description(s) nor sample input data, it is up to us to guess what went wrong. Did you, perhaps, enter all persons that are 16 and older?

    I was too lazy to simulate your complete situation, but the simplified example below shows that trigger *should* work:
    Code:
    SQL> CREATE TABLE CLIENT (age DATE, comments VARCHAR2(20));
    
    Table created.
    
    SQL> CREATE OR REPLACE TRIGGER age_restriction_comment
      2     BEFORE INSERT
      3     ON CLIENT
      4     FOR EACH ROW
      5  BEGIN
      6     IF ADD_MONTHS (:NEW.age, 12 * 16) >= TRUNC (SYSDATE)
      7     THEN
      8        :NEW.comments := 'test_fail';
      9     ELSE
     10        :NEW.comments := 'test_pass';
     11     END IF;
     12  END age_restriction_comment;
     13  /
    
    Trigger created.
    
    SQL> INSERT INTO CLIENT (age) VALUES ('05.12.97');
    
    1 row created.
    
    SQL> INSERT INTO CLIENT (age) VALUES ('30.09.68');
    
    1 row created.
    
    SQL> SELECT * FROM client;
    
    AGE      COMMENTS
    -------- --------------------
    05.12.97 test_fail
    30.09.68 test_pass
    
    SQL>
    Could you compare your solution with this one and see if it helps?

Posting Permissions

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