Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: Statement level trigger

    On our WANG system, we used a character-string as an argument to determine if a trigger should run through it's code. In Oracle, this argument has turned into a package specification variable.

    Our WANG code has this variable set back to NULL at the end of the on row trigger, and so the code converted by our vendor does as well. This does not work well when performing a multi-record update/insert/delete statement:

    update salary set income = income * (income * .10);

    So, one option we're considering is adding an AFTER STATEMENT trigger, and resetting this package spec variable back to NULL there. Because of our inexperience, though, we're unsure if there's any coding method which can update single records in a serial fashion without invoking the AFTER STATEMENT trigger until after all rows are updated. Perhaps something like an updatable cursor or something?

    Thanks,
    Chuck

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    CREATE TRIGGER SALARY_t1
    BEFORE
    INSERT OR UPDATE ON SALARY
    FOR EACH ROW
    BEGIN
    IF MY_VARIABLE IS NOT NULL THEN
    :NEW.income = :new.income * (:new.income * .10);
    end;


    CREATE TRIGGER SALARY_t2
    AFTER
    INSERT OR UPDATE ON SALARY
    BEGIN
    MY_VARIABLE := NULL;
    end;

    The first trigger would fire on every row before it is stored in the database. The second trigger would fire after the statement is completed.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    The hypothetical situation I was trying to look out for would be if something like the following would cause the after statement trigger to fire at an unexpected time, such as after the LOOP was executed (I was thinking that perhaps an updatable cursor might cause something like this):

    Code:
    DECLARE
    	   CURSOR c1 IS SELECT age, myname,  mod_id FROM DEBUG_TRIGGER FOR UPDATE NOWAIT;
    	   v_age DEBUG_TRIGGER.age%TYPE;
    	   v_myname DEBUG_TRIGGER.myname%TYPE;
    	   v_mod_id DEBUG_TRIGGER.mod_id%TYPE;
    BEGIN
    
    	 OPEN c1;
    	 LOOP
    	 	FETCH c1 INTO v_age, v_myname, v_mod_id;
    	 	UPDATE DEBUG_TRIGGER SET age = 21 WHERE CURRENT OF c1;
                  EXIT WHEN c1%NOTFOUND;
    
    	 END LOOP;
    END;
    If I had to guess would be that each UPDATE statement would be a separate statement, and so the after stmt trigger would fire for each row in the cursor. I just can't get this final piece of test code working in order to test it.

    It fails on the 'UPDATE' line with the error:
    Code:
    ERROR at line 1:
    ORA-01410: invalid ROWID
    ORA-06512: at line 11
    If someone has a suggestion on what I've done wrong, that'd be cool too.

    Thanks,
    Chuck

  4. #4
    Join Date
    Dec 2003
    Posts
    1,074
    The line

    Code:
                       EXIT WHEN c1%NOTFOUND;
    should have been immediately after the FETCH statement. Since I got it running, I could see that the AFTER STATEMENT trigger fired after each UPDATE statement was issued.

    Thanks for the help,
    Chuck

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Use cursor FOR loops and never have such issues again! :-
    Code:
    DECLARE
      CURSOR c1 IS SELECT age, myname, mod_id FROM DEBUG_TRIGGER FOR UPDATE NOWAIT;
    BEGIN
      FOR r1 IN c1 LOOP
        UPDATE DEBUG_TRIGGER SET age = 21 WHERE CURRENT OF c1;
      END LOOP;
    END;
    (Except that you can't use FOR with a ref cursor).

Posting Permissions

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