Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2004
    Location
    Dublin, Ireland
    Posts
    16

    Unanswered: Problem with trigger comparing dates

    I'm having trouble getting this trigger to insert anything:

    CREATE OR REPLACE TRIGGER ALM_CH_OUT_U_TITLE_TRG
    AFTER UPDATE OF TITLE ON AL_NAME
    FOR EACH ROW
    DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION;
    vdate_entered DATE;
    BEGIN
    -- Only send if this is the latest
    SELECT MAX(date_entered) INTO vdate_entered FROM al_name
    WHERE name_status = :new.name_status AND stud_no = :new.stud_no;
    IF :new.date_entered = vdate_entered THEN
    -- This is the latest
    IF :NEW.name_status IS NULL THEN
    -- This is the surname
    INSERT INTO alm_changes_out (alm_changes_out_id, date_in, stud_no, tx_type,
    alm_table, alm_column, field, old_data)
    VALUES (alm_changes_out_seq.nextval, TO_DATE(SYSDATE,'DD/MM/YYYY HH24:MIS'), ld.stud_no, 'U',
    'AL_NAME', 'TITLE', :new.title, ld.title);
    END IF;
    END IF;
    COMMIT;
    END;

    I think the problem is with this line:
    IF :new.date_entered = vdate_entered THEN
    I've tried:
    IF :new.date_entered like vdate_entered THEN
    but to no avail.

    name_status is either NULL or '1' and stud_no is an 8-digit number.

    Kevin

  2. #2
    Join Date
    Aug 2004
    Posts
    330
    Can you put a COMMIT into a trigger?

  3. #3
    Join Date
    Oct 2004
    Location
    Dublin, Ireland
    Posts
    16
    Without the COMMIT I get this error:
    ORA-06519: active autonomous transaction detected and rolled back

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    It seems that your "SELECT MAX(date_entered)" returns NULL and you don't even enter the IF block. What do you want to do if NULL is returned? Nothing? If so, that's what you're doing now.
    Show output value to the screen so that you'll know for sure (use "DBMS_OUTPUT.PUT_LINE(vdate_entered);" behind the SELECT statement). Let us know what happened.

  5. #5
    Join Date
    Oct 2004
    Location
    Dublin, Ireland
    Posts
    16
    The select returning NULL was the problem alright. Here is the solution:

    Code:
    CREATE OR REPLACE TRIGGER ALM_CH_OUT_U_TITLE_TRG
    AFTER UPDATE OF TITLE ON AL_NAME
    FOR EACH ROW
    DECLARE
      PRAGMA AUTONOMOUS_TRANSACTION;
      vdate_entered DATE;
    BEGIN
      -- Only send if this is the latest
      IF name_status IS NULL THEN
        SELECT MAX(date_entered) INTO vdate_entered FROM al_name
        WHERE name_status IS NULL AND stud_no = :new.stud_no;
        IF :new.date_entered like vdate_entered THEN
          -- This is the latest
          INSERT INTO alm_changes_out (alm_changes_out_id, date_in, stud_no, tx_type,
          alm_table, alm_column, field, old_data)
          VALUES (alm_changes_out_seq.nextval, TO_DATE(SYSDATE,'DD/MM/YYYY HH24:MI:SS'), :old.stud_no, 'U',
          'AL_NAME', 'TITLE', :new.title, :old.title);
        END IF;  --Is max date entered
      END IF: --Null name_status
      COMMIT;
    END;
    Thanks,

    Kevin

  6. #6
    Join Date
    Oct 2004
    Location
    Dublin, Ireland
    Posts
    16
    Sorry! THIS is the solution:

    Code:
    CREATE OR REPLACE TRIGGER ALM_CH_OUT_U_TITLE_TRG
    AFTER UPDATE OF TITLE ON AL_NAME
    FOR EACH ROW
    DECLARE
      PRAGMA AUTONOMOUS_TRANSACTION;
      vdate_entered DATE;
    BEGIN
      -- Only send if this is the latest
      IF :new.name_status IS NULL THEN
        SELECT MAX(date_entered) INTO vdate_entered FROM al_name
        WHERE name_status IS NULL AND stud_no = :new.stud_no;
    
        INSERT INTO TEST1 (ref, theDate) VALUES (1, vdate_entered);
        IF :new.date_entered like vdate_entered THEN
          -- This is the latest
          INSERT INTO alm_changes_out (alm_changes_out_id, date_in, stud_no, tx_type,
          alm_table, alm_column, field, old_data)
          VALUES (alm_changes_out_seq.nextval, TO_DATE(SYSDATE,'DD/MM/YYYY HH24:MI:SS'), :old.stud_no, 'U',
          'AL_NAME', 'TITLE', :new.title, :old.title);
        END IF;  --Is max date entered
      END IF; --Null name_status
      COMMIT;
    END;
    Kevin

  7. #7
    Join Date
    Oct 2004
    Location
    Dublin, Ireland
    Posts
    16
    Third time lucky. This is it:

    Code:
    CREATE OR REPLACE TRIGGER ALM_CH_OUT_U_TITLE_TRG
    AFTER UPDATE OF TITLE ON AL_NAME
    FOR EACH ROW
    DECLARE
      PRAGMA AUTONOMOUS_TRANSACTION;
      vdate_entered DATE;
    BEGIN
      -- Only send if this is the latest
      IF :new.name_status IS NULL THEN
        SELECT MAX(date_entered) INTO vdate_entered FROM al_name
        WHERE name_status IS NULL AND stud_no = :new.stud_no;
        IF :new.date_entered like vdate_entered THEN
          -- This is the latest
          INSERT INTO alm_changes_out (alm_changes_out_id, date_in, stud_no, tx_type,
          alm_table, alm_column, field, old_data)
          VALUES (alm_changes_out_seq.nextval, TO_DATE(SYSDATE,'DD/MM/YYYY HH24:MI:SS'), :old.stud_no, 'U',
          'AL_NAME', 'TITLE', :new.title, :old.title);
        END IF;  --Is max date entered
      END IF; --Null name_status
      COMMIT;
    END;
    K

Posting Permissions

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