2 Replies Latest reply: Mar 22, 2013 1:04 PM by Maxo RSS

    Triggers hiding when clause

    Maxo
      I have a when clause in the header of an insert trigger that does not show up when using SQL Developer to view the code.
      select
        text
      from
        user_source
      where
        name = 'FOO_INSERT_B_TRG'
      order by
        line;
      
      TEXT
      ----
      trigger bar.foo_insert_b_trg
        before insert
        on bar.foo
        for each row
        when (new.foo_id is null)
        declare
      begin
        :new.foo_id := key_pkg.create_key_f ('BAR', 'FOO');
      end foo_insert_b_trg;
      
      9 rows selected
      However, when I pull the code up by double-clicking the trigger I get this.
      create or replace 
      trigger bar.foo_insert_b_trg
        before insert
        on bar.foo
        for each row
        declare
      begin
        :new.foo_id := key_pkg.create_key_f ('BAR', 'FOO');
      end foo_insert_b_trg;
      Notice that line 5, which has my when statement, is missing. This is causing this line to actually be removed when we open the object up, edit it, and then save it.

      Edited by: Maxo on Mar 22, 2013 11:55 AM

      Edited by: Maxo on Mar 22, 2013 12:01 PM

      Edited by: Maxo on Mar 22, 2013 12:04 PM
        • 1. Re: Triggers hiding when clause
          Gary Graham-Oracle
          Hi Maxo,

          This issue has been reported before on this forum:
          Unable to see the WHEN clause for a trigger.

          The bug referenced in that thread is a regression from SQL Developer 3.1. and remains open. From the bug comments:
          With only minimal privileges, we query all_triggers to get only the package
          body (when-clause kept in a different column), whereas with
          SELECT_CATALOG_ROLE or DBA role we query dba_source that contains the full
          trigger source.
          So the alternatives for working around this issue are:
          1. Keep multiple versions of SQL Developer installed, using 3.1 when you need to work with triggers.
          2. Obtain additional privileges in order to avoid the regression in 3.2

          Hope this helps,
          Gary
          SQL Developer Team
          • 2. Re: Triggers hiding when clause
            Maxo
            Thank you for the response Gary, and I apologize for the duplicate report. I will see if our dba team is willing to grant us this privilege. Either way, I await the next release of SQLD.