Forum Stats

  • 3,853,749 Users
  • 2,264,264 Discussions
  • 7,905,440 Comments

Discussions

How to export view with instead of trigger

Marko Goricki
Marko Goricki Member Posts: 413 Gold Badge
edited Feb 9, 2018 1:44AM in SQL Developer

Hi,

is there an easy way to export view together with instead of trigger created on the same view?

For example, if you create view and instead of trigger like this:

-- viewcreate or replace view v_dummy as  select 1 a    from dual;-- instead of triggercreate or replace  trigger v_dummy_ioiu  instead of insert or update on v_dummy  referencing new as new old as old  for each rowbegin  null;end;

If you go to the export option (right click on View > Export) with this properties:

pastedImage_3.png

you will get only view code (create or replace view...). If you select option Dependents you'll get all depended object, but no trigger.

The code that I want is visible if you right click on view > Open > tab SQL:

pastedImage_4.png

Thanks in advance for help!

I'm using SQL Developer 17.4.0.355 on Mac.

Br,

Marko

Tagged:

Best Answer

  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 8,771 Employee
    edited Feb 8, 2018 3:30PM Answer ✓

    The SQL page does more work, it also asks

      union all

      select dbms_metadata.GET_DDL ('TRIGGER', trigger_name ,owner ) from Dba_triggers where table_owner = :OWNER and table_name = :NAME

      union all

    which finds the trigger attached to the table (which in this case, is the view)

    The Export feature is NOT making this call, which would get the trigger.

    SELECT dbms_metadata.get_dependent_ddl('TRIGGER', 'V_DUMMY') from dual;

    I'd call this a bug.

Answers

  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 8,771 Employee
    edited Feb 8, 2018 3:30PM Answer ✓

    The SQL page does more work, it also asks

      union all

      select dbms_metadata.GET_DDL ('TRIGGER', trigger_name ,owner ) from Dba_triggers where table_owner = :OWNER and table_name = :NAME

      union all

    which finds the trigger attached to the table (which in this case, is the view)

    The Export feature is NOT making this call, which would get the trigger.

    SELECT dbms_metadata.get_dependent_ddl('TRIGGER', 'V_DUMMY') from dual;

    I'd call this a bug.

This discussion has been closed.