This discussion is archived
1 2 Previous Next 24 Replies Latest reply: Feb 19, 2013 7:26 AM by Christine Schnittker RSS

Trigger Question - New to PL/SQL

991389 Newbie
Currently Being Moderated
I have a before trigger on a Oracle 11g table that allows me to audit the table to see who is changing data by saving the old value in a variable and logging it along with the new variable and more data into a table. My issue is sometimes_ the trigger does not fire when it meets the criteria. Does anyone have any idea why this would happen or not happen? Thanks in advance for your assistance.
  • 1. Re: Trigger Question - New to PL/SQL
    Centinul Guru
    Currently Being Moderated
    Welcome to the forums!

    Please see: {message:id=9360002}

    If you can provide a sample implementation to illustrate your problem that would be very helpful. It's hard to identify the problem without a working example or code to look at.

    Thanks!
  • 2. Re: Trigger Question - New to PL/SQL
    Justin Cave Oracle ACE
    Currently Being Moderated
    Realistically, I believe it is very unlikely that the trigger is not firing when it is supposed to.

    It is possible, of course, that you've found a bug in Oracle-- it's a large piece of software, after all, so there are bound to be bugs. But unless you have a test case that reproduces the problem reliably (in which case please post it), it seems more likely to me that you've made a mistake somewhere. Perhaps a DML statement that you expect to be executed is never getting called, for example. Perhaps an error is being raised that is being caught and ignored by an exception handler. Perhaps there is a bug in your trigger. Perhaps the problem is elsewhere in your system.

    Justin
  • 3. Re: Trigger Question - New to PL/SQL
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    Welcome to the forum!
    user2811127 wrote:
    I have a before trigger on a Oracle 11g table that allows me to audit the table to see who is changing data by saving the old value in a variable and logging it along with the new variable and more data into a table. My issue is sometimes_ the trigger does not fire when it meets the criteria.
    How do you know that the trigger is not firing? Couldn't it be firing, but failing to write to the log table?
    Does anyone have any idea why this would happen or not happen?
    Not without seeing the trigger.
    Whenever you have a problem, post a complete test script that people can run to re-create the problem and test their ideas. In this case, include the full code of the trigger, CREATE TABLE statements for whatever tables it uses, and some DML statements that cause the trigger to fire. If possible, post some DML statements that work correctly (i.e., that cause the trigger to fire and save data the way you want), and some that do not. explain what you would like to happen after each DML operation.
  • 4. Re: Trigger Question - New to PL/SQL
    991389 Newbie
    Currently Being Moderated
    My apologies, first post and I will read the how-tos. The audit table is only written to if a record is changed. The trigger is on another table and the results are written to my audit table. There are no errors being written out. The code is as follows:

    TRIGGER "SATURN"."FIT_SGBSTDN_RES"
    BEFORE INSERT OR UPDATE
    ON saturn.sgbstdn
    FOR EACH ROW
    WHEN (old.sgbstdn_resd_code != new.sgbstdn_resd_code)

    DECLARE
    errt VARCHAR2(200);
    hold_resd_code VARCHAR2(1);

    CURSOR c_old_rc IS
    SELECT sgbstdn_resd_code
    FROM saturn.sgbstdn
    WHERE sgbstdn_pidm = :new.sgbstdn_pidm
    AND sgbstdn_term_code_eff = (select max(a.sgbstdn_term_code_eff) from sgbstdn a
    where a.sgbstdn_pidm = :new.sgbstdn_pidm
    and a.sgbstdn_term_code_eff < :new.sgbstdn_term_code_eff);

    BEGIN
    dbms_output.ENABLE(1000000);
    IF INSERTING THEN
    OPEN c_old_rc;
    FETCH c_old_rc INTO hold_resd_code;
    CLOSE c_old_rc;
    INSERT INTO FIT_RESIDENCY_AUDIT
    ( pidm,
    term_code_eff,
    resd_code_before,
    resd_code_after,
    activity_date,
    user_id )
    VALUES
    ( :new.sgbstdn_pidm,
    :new.sgbstdn_term_code_eff,
    hold_resd_code,
    :new.sgbstdn_resd_code,
    :new.sgbstdn_activity_date,
    :new.sgbstdn_user_id );
    ELSIF UPDATING THEN
    INSERT INTO FIT_RESIDENCY_AUDIT
    ( pidm,
    term_code_eff,
    resd_code_before,
    resd_code_after,
    activity_date,
    user_id )
    VALUES
    ( :new.sgbstdn_pidm,
    :new.sgbstdn_term_code_eff,
    :old.sgbstdn_resd_code,
    :new.sgbstdn_resd_code,
    :new.sgbstdn_activity_date,
    :new.sgbstdn_user_id );
    END IF;

    EXCEPTION
         WHEN OTHERS THEN
         errt := substr(sqlerrm,1,190);
         dbms_output.put_line(errt);
    INSERT INTO fit_jean
    ( errt )
    VALUES
    ( errt );
    END;
  • 5. Re: Trigger Question - New to PL/SQL
    Justin Cave Oracle ACE
    Currently Being Moderated
    I would expect that this trigger would be throwing a mutating table exception. In general, a row-level trigger on a particular table (saturn.sgbstdn) cannot query that table. You can't look at other rows in the table in the C_OLD_RC cursor.

    A WHEN OTHERS exception handler that does not re-raise the exception is almost always a mistake. If you do not expect the exception and cannot meaningfully handle it, writing to a log table is unlikely to be helpful. Plus, you're throwing away valuable infomation like the stack trace of the error. You would almost certainly want to remove the exception handler here (and any other like it in your code). That should cause the errors that you're getting to propagate back up to the caller where they can alert you when things go wrong rather than silently failing.

    Once you've eliminated the exception handler and you're seeing the mutating table stack trace, the next question will be why you need the old sgbstdn_resd_code value. Presumably, that is also in the prior entry in the log table. I would think that you would just want to write the new values to the log table and if you need to report on the old and new value in a report, use an analytic LAG function in your query.

    Justin
  • 6. Re: Trigger Question - New to PL/SQL
    991389 Newbie
    Currently Being Moderated
    It doesn't appear to be mutating. The trigger does fire sometimes and I do get records inserted into my audit table when the value changes on the row. I do need to track both old and new values. The values from the audit table kick off a workflow that reports both old and new values to my users.
  • 7. Re: Trigger Question - New to PL/SQL
    Justin Cave Oracle ACE
    Currently Being Moderated
    user2811127 wrote:
    It doesn't appear to be mutating.
    Why do you say that?

    If I create a simple example of a table with a row-level trigger that queries the table
    SQL> drop table a;
    
    Table dropped.
    
    SQL> create table a( col1 number, col2 date );
    
    Table created.
    
    SQL> ed
    Wrote file afiedt.buf
    
      1  create or replace trigger mutating_trigger
      2    before insert or update on a
      3    for each row
      4  declare
      5    l_old_col1 number;
      6  begin
      7    begin
      8      select col1
      9        into l_old_col1
     10        from a
     11       where col2 = (select max(col2)
     12                       from a
     13                      where col2 < :new.col2);
     14    exception
     15      when no_data_found
     16      then
     17        l_old_col1 := -1;
     18    end;
     19    dbms_output.put_line( 'Old col1 = ' || l_old_col1 );
     20* end;
    SQL> /
    
    Trigger created.
    then anything that I do to the table other than an INSERT ... VALUES (which is a special case where Oracle knows in advance that only one row is changing) will throw a mutating table exception
    SQL> insert into a values( 1, sysdate );
    Old col1 = -1
    
    1 row created.
    
    SQL> insert into a values( 2, sysdate );
    Old col1 = 1
    
    1 row created.
    
    SQL> update a
      2     set col1 = col1 + 1;
    update a
           *
    ERROR at line 1:
    ORA-04091: table SCOTT.A is mutating, trigger/function may not see it
    ORA-06512: at "SCOTT.MUTATING_TRIGGER", line 5
    ORA-04088: error during execution of trigger 'SCOTT.MUTATING_TRIGGER'
    
    SQL> ed
    Wrote file afiedt.buf
    
      1  insert into a
      2    select 4, sysdate
      3*     from dual
      4  /
    insert into a
                *
    ERROR at line 1:
    ORA-04091: table SCOTT.A is mutating, trigger/function may not see it
    ORA-06512: at "SCOTT.MUTATING_TRIGGER", line 5
    ORA-04088: error during execution of trigger 'SCOTT.MUTATING_TRIGGER'
    Are you saying that this is not what you're seeing? Are you certain that you have removed all the WHEN OTHERS exception handlers that may be hiding the error?

    Justin
  • 8. Re: Trigger Question - New to PL/SQL
    991389 Newbie
    Currently Being Moderated
    Yes I removed the exception information. I changed a value in the sgbstdn table for sgbstdn_resd_code and it inserted into my audit table. No mutation, no error.

    TRIGGER "SATURN"."FIT_SGBSTDN_RES"
    BEFORE INSERT OR UPDATE
    ON saturn.sgbstdn
    FOR EACH ROW
    WHEN (old.sgbstdn_resd_code != new.sgbstdn_resd_code)

    DECLARE
    -- errt VARCHAR2(200);
    hold_resd_code VARCHAR2(1);

    CURSOR c_old_rc IS
    SELECT sgbstdn_resd_code
    FROM saturn.sgbstdn
    WHERE sgbstdn_pidm = :new.sgbstdn_pidm
    AND sgbstdn_term_code_eff = (select max(a.sgbstdn_term_code_eff) from sgbstdn a
    where a.sgbstdn_pidm = :new.sgbstdn_pidm
    and a.sgbstdn_term_code_eff < :new.sgbstdn_term_code_eff);

    BEGIN
    --dbms_output.ENABLE(1000000);
    IF INSERTING THEN
    OPEN c_old_rc;
    FETCH c_old_rc INTO hold_resd_code;
    CLOSE c_old_rc;
    INSERT INTO FIT_RESIDENCY_AUDIT
    ( pidm,
    term_code_eff,
    resd_code_before,
    resd_code_after,
    activity_date,
    user_id )
    VALUES
    ( :new.sgbstdn_pidm,
    :new.sgbstdn_term_code_eff,
    hold_resd_code,
    :new.sgbstdn_resd_code,
    :new.sgbstdn_activity_date,
    :new.sgbstdn_user_id );
    ELSIF UPDATING THEN
    INSERT INTO FIT_RESIDENCY_AUDIT
    ( pidm,
    term_code_eff,
    resd_code_before,
    resd_code_after,
    activity_date,
    user_id )
    VALUES
    ( :new.sgbstdn_pidm,
    :new.sgbstdn_term_code_eff,
    :old.sgbstdn_resd_code,
    :new.sgbstdn_resd_code,
    :new.sgbstdn_activity_date,
    :new.sgbstdn_user_id );
    END IF;

    -- EXCEPTION
    --     WHEN OTHERS THEN
    --     errt := substr(sqlerrm,1,190);
    --     dbms_output.put_line(errt);
    -- INSERT INTO fit_jean
    -- ( errt )
    -- VALUES
    -- ( errt );
    END;
  • 9. Re: Trigger Question - New to PL/SQL
    Justin Cave Oracle ACE
    Currently Being Moderated
    I posted a complete test case to show that you'll get a mutating table exception if you do anything other than an INSERT ... VALUES.

    If you are saying that you are seeing a different behavior, you'll need to post a complete test case that shows exactly what you are doing and exactly what you are seeing. You'd need to post the DDL to create whatever tables you need, the trigger code that you've already posted, and show us exactly what INSERT and/or UPDATE statements you are executing.

    Justin
  • 10. Re: Trigger Question - New to PL/SQL
    Christine Schnittker Explorer
    Currently Being Moderated
    Hi,

    WHEN (old.sgbstdn_resd_code != new.sgbstdn_resd_code)

    .. that will not fire when either one is NULL (e.g. on inserts ;) )

    BR
  • 11. Re: Trigger Question - New to PL/SQL
    rp0428 Guru
    Currently Being Moderated
    You never posted the sample code and data that you say should have fired the trigger but didn't.
    >
    TRIGGER "SATURN"."FIT_SGBSTDN_RES"
    BEFORE INSERT OR UPDATE
    ON saturn.sgbstdn
    FOR EACH ROW
    WHEN (old.sgbstdn_resd_code != new.sgbstdn_resd_code)
    >
    See if you can answer this question: What is the value of OLD.sgbstdn_resd_code when an INSERT is done?

    Give up? See the PL/SQL Language Reference doc
    http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/triggers.htm
    >
    Accessing Column Values in Row Triggers Within a trigger body of a row trigger, the PL/SQL code and SQL statements have access to the old and new column values of the current row affected by the triggering statement. Two correlation names exist for every column of the table being modified: one for the old column value, and one for the new column value. Depending on the type of triggering statement, certain correlation names might not have any meaning.

    •A trigger fired by an INSERT statement has meaningful access to new column values only. Because the row is being created by the INSERT, the old values are null.
    >
    See that last sentence?

    Second question: what happens when you try to compare a value to NULL in a '!=' condition?

    Now ask yourself if the answer to that 2nd question might be why you are seeing seeing your expected results.
  • 12. Re: Trigger Question - New to PL/SQL
    991389 Newbie
    Currently Being Moderated
    Let me try and explain what this table does, maybe that will help. If not then I will see if I can give you what you are asking for.

    The sgbstdn table already has a record in it, always, so there are no null values.

    The table is a student record table which stores information by term. If the record being changed is not the same term it inserts a new row into sgbstdn with the new term value. Essentially it copies everything from the record of the old term to the new term record except it changes the sgbstdn_resd_code.

    If the record being changed is in the same term it is a change to the existing term record and only changes the current rows value for sgbstdn_resd_code.

    Does this make sense? Sorry to be so confusing and frustrating.
  • 13. Re: Trigger Question - New to PL/SQL
    991389 Newbie
    Currently Being Moderated
    The old value of sgbstdn_resd_code = 'N' and the new value to sgbstdn_resd_code = 'R'. I just tested this in my development database without the exception and the trigger fired.
  • 14. Re: Trigger Question - New to PL/SQL
    Justin Cave Oracle ACE
    Currently Being Moderated
    As others have pointed out, if you INSERT a new row into the sgbstdn table, the :old values will all be NULL so the WHEN clause of your trigger will evaluate to FALSE.

    It sounds like you are saying that you are issuing an UPDATE that is, somehow, not generating a mutating table exception. If that is the case, all I can do is repeat my request that you post a complete test case that shows exactly what you are doing.

    Justin
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points