This discussion is archived
2 Replies Latest reply: Mar 22, 2013 11:04 AM by Maxo RSS

Triggers hiding when clause

Maxo Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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.

Legend

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