This discussion is archived
10 Replies Latest reply: Jul 3, 2013 5:09 PM by Gary Graham RSS

Unable to see the WHEN clause for a trigger.

971113 Newbie
Currently Being Moderated
I have an existing trigger that I want to add a WHEN clause too. When I add the WHEN clause and compile the trigger everything compiles. However, the WHEN clause is not visible when trying to view the trigger either through the table and trigger tab or by going the the triggers node. Also the triggers displayed under the table tab no longer display the full trigger code only the pl/sql block. This appears to be a problem in this version of SQL developer (3.2.10.09 Build 09.57) I can see the complete trigger code in an older version of SQL developer (3.1.0.7).
Database version : 11.2

The when clause is visible when you query the user_triggers table.

Can someone confirm this is a problem or is there a setting I need to change??
Thanks.
  • 1. Re: Unable to see the WHEN clause for a trigger.
    Gary Graham Expert
    Currently Being Moderated
    Hi Barnesy,

    Here is a simple test case that seems to work fine on the SCOTT schema:
    //Without a WHEN-clause -- Paste into a worksheet and click on the Run Script icon
    create or replace TRIGGER Print_salary_chg
      BEFORE DELETE OR INSERT OR UPDATE ON Emp
      FOR EACH ROW
    DECLARE
        sal_diff number;
    BEGIN
        sal_diff  := :new.sal  - :old.sal;
        dbms_output.put('Old salary: ' || :old.sal);
        dbms_output.put('  New salary: ' || :new.sal);
        dbms_output.put_line('  Difference ' || sal_diff);
    END;
    
    //Open this new trigger from the Connections view navigator Triggers node.  May need to refresh the node first.  
    //Then edit it to add a WHEN-clause.  Compile.
    create or replace TRIGGER Print_salary_chg
      BEFORE DELETE OR INSERT OR UPDATE ON Emp
      FOR EACH ROW
      WHEN (new.Empno > 0)
    DECLARE
        sal_diff number;
    BEGIN
        sal_diff  := :new.sal  - :old.sal;
        dbms_output.put('Old salary: ' || :old.sal);
        dbms_output.put('  New salary: ' || :new.sal);
        dbms_output.put_line('  Difference ' || sal_diff);
    END;
    The change is obviously reflected in the Trigger editor since that is where you just edited. If the object viewer Trigger tab is already open for the Emp table on that trigger, just change the Refresh interval from 0 to 5 and back to 0 to get a refreshed view with the WHEN-clause visible. If the object viewer for the Emp table is not already open, then opening it to the Trigger tab should automatically show the WHEN-clause when clicking on that trigger.

    If instead you update the trigger DDL directly in the worksheet, the Trigger editor has no refresh button, so you must close and open it again to see the WHEN-clause.

    If you actually have a test case that does not work like this, please post the trigger and any necessary supporting DDL. If it is reproducible, I will log a bug for it.

    Regards,
    Gary
    SQL Developer Team
  • 2. Re: Unable to see the WHEN clause for a trigger.
    971113 Newbie
    Currently Being Moderated
    Hi,
    I tried your test case and it sort of worked. Here is what happened for me:
    1) I logged into Scott schema and pasted the trigger into the worksheet and ran it as a script. No problems.
    2) Went to the Navigator >Trigger Node ans selected the only trigger. No Problems displayed in full.
    3) I added the when clause to the trigger and compiled. No problems and the WHEN clause was still visible.
    4) I went to the tables Node and selected the EMP table and then the triggers tab. Problems!
    The trigger tab does not show the when clause nor does it show the complete trigger code all I see is nothing else:
    ====
    DECLARE
    sal_diff number;
    BEGIN
    sal_diff := :new.sal - :old.sal;
    dbms_output.put('Old salary: ' || :old.sal);
    dbms_output.put(' New salary: ' || :new.sal);
    dbms_output.put_line(' Difference ' || sal_diff);
    END;
    ====
    The previous version of sql developer shows me the complete trigger including the when clause.
    Please note I have refreshed the tables node and the table several times.
    5) I then went back and closed the tab displaying the details from the trigger node.
    6) I refreshed the trigger node by selecting the trigger node and clicking the refresh icon.
    7) I then selected that same trigger again and the WHEN clause had gone. All I see is:
    ====
    create or replace trigger Print_salary_chg
    BEFORE DELETE OR INSERT OR UPDATE ON Emp
    FOR EACH ROW

    DECLARE
    sal_diff number;
    BEGIN
    sal_diff := :new.sal - :old.sal;
    dbms_output.put('Old salary: ' || :old.sal);
    dbms_output.put(' New salary: ' || :new.sal);
    dbms_output.put_line(' Difference ' || sal_diff);
    END;
    ====
    8) I query the user_triggers table I see the following under the When_clause column:
    ====
    new.Empno > 0
    ====

    My system details are:
    Windows 7 Enterprise 64 bit
    sql developer details
    Java(TM) Platform     1.6.0_11
    Oracle IDE     3.2.10.09.57
    Versioning Support     3.2.10.09.57

    N.B. - Strange thing is that is that this could be related to permissions I notice that when I log in as SYSTEM and go down the other users tab and view the trigger everything is visible in both the table trigger tab and the trigger via the trigger node.

    I hope this helps.
  • 3. Re: Unable to see the WHEN clause for a trigger.
    971113 Newbie
    Currently Being Moderated
    Some additional info when I debug sql developer I can see the following queries issued by sql developer when viewing the triggers from the table node, trigger tab and also form the trigger node:

    When signed in as SCOTT

         Prepared: select /*'trigger '||description||chr(10)|| Long!->*/trigger_body from all_triggers
    where owner = :TRIGGER_OWNER and trigger_name = :TRIGGER_NAME
                             
         Prepared:select 'trigger '||description||chr(10) prefix, trigger_body from all_triggers
              where /*vacuous bind to match*/:TYPE='TRIGGER' and owner = :OWNER and trigger_name = :NAME
                             
                             
    When signed in as SYSTEM

                             
    Prepared:select text sql from dba_source
              where name=:TRIGGER_NAME
              and owner=:TRIGGER_OWNER;
                                       
    Prepared:SELECT TEXT FROM SYS.DBA_SOURCE
              WHERE TYPE = :TYPE AND OWNER = :OWNER AND NAME = :NAME
              ORDER BY LINE
                             
    I hope this provides some assistance in this matter.
  • 4. Re: Unable to see the WHEN clause for a trigger.
    Gary Graham Expert
    Currently Being Moderated
    Thanks for investigating further. I tested on 3.1.07.42 and got the same behavior, so this is not a regression. If you can get additional privileges, such as SELECT_CATALOG_ROLE, then you can work around the bug. In fact, my SCOTT has that additional role granted.

    Anyway, I logged a bug:
    Bug 14840619 - FORUM - TRIGGER WHEN-CLAUSE NOT SHOWN IF USER ONLY HAS BASIC PRIVILEGES

    Depending on your specific privileges, SQL Developer attempts to use the best performing view for such meta-data queries. I am not sure why the non-privileged query uses all_triggers rather than user_source (to match the privileged dba_source). Perhaps it was an over-sight, perhaps it was an attempt to improve performance.

    -Gary

    Edited by: Gary Graham on Oct 31, 2012 2:19 PM
    Actually, I meant to say all_source, not user_source. You may be looking at another user's triggers, if you have the privilege.
  • 5. Re: Unable to see the WHEN clause for a trigger.
    971113 Newbie
    Currently Being Moderated
    Thanks for that Gary, however this doesn't seem to make too much sense in my case as I can sign into the same database using the same user and see two different versions of the same trigger.
    -----
    In this version of SQL developer:

    Java(TM) Platform     1.6.0_11
    Oracle IDE     3.1.07.42
    Versioning Support     3.1.07.42

    I See the trigger with the when clause as shown below
    create or replace
    TRIGGER LMM_ISQU_PRE_ROW BEFORE
    UPDATE ON lmm_ISSUED_QUOTA FOR EACH ROW WHEN ( old.transaction_id <> new.transaction_id ) DECLARE

    --------
    In this version of SQL Developer:

    Java(TM) Platform     1.6.0_11
    Oracle IDE     3.2.10.09.57
    Versioning Support     3.2.10.09.57

    I See the trigger with no when clause
    create or replace trigger LMM_ISQU_PRE_ROW BEFORE
    UPDATE ON lmm_ISSUED_QUOTA FOR EACH ROW
    DECLARE
    ------

    This user does not have the select_catalog_role but displays differently for each version.

    Thanks.
  • 6. Re: Unable to see the WHEN clause for a trigger.
    971113 Newbie
    Currently Being Moderated
    Some Additional info:

    When signed into this sql developer version: 3.1.07.42 as the schema owner I see the following:

    I see this query issued against the table node, trigger tab.

    Prepared: SELECT DBMS_METADATA.GET_DDL('TRIGGER',:TRIGGER_NAME,:TRIGGER_OWNER) SQL FROM SYS.DUAL

    I see this query issued against the trigger node.

    Prepared:
    SELECT TEXT FROM SYS.ALL_SOURCE
    WHERE TYPE = :TYPE AND OWNER = :OWNER AND NAME = :NAME
    ORDER BY LINE


    Thanks.
  • 7. Re: Unable to see the WHEN clause for a trigger.
    Gary Graham Expert
    Currently Being Moderated
    Excellent, you are absolutely correct -- it is a regression. I updated the bug accordingly.

    Sorry abou that -- I have way too many versions installed, with at least as many shortcuts to pick from...

    -Gary
  • 8. Re: Unable to see the WHEN clause for a trigger.
    hyperspace Newbie
    Currently Being Moderated

    Hello! Any news on this regression? It still affects the latest version of SQL Developer as of today.

     

    Thank you in advance!

  • 9. Re: Unable to see the WHEN clause for a trigger.
    Gary Graham Expert
    Currently Being Moderated

    Hi hyperspace,

     

    Fortunately this bug has been fixed for the next release of SQL Developer.  Product management will make an announcement on this forum when the time comes. 

     

    -Gary

  • 10. Re: Unable to see the WHEN clause for a trigger.
    hyperspace Newbie
    Currently Being Moderated

    Thanks! Me and my team are looking forward to it!

Legend

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