1 Reply Latest reply on Feb 8, 2018 8:30 PM by thatJeffSmith-Oracle

    How to export view with instead of trigger

    Marko Goricki



      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:

      -- view
      create or replace view v_dummy as
        select 1 a
          from dual;
      -- instead of trigger
      create or replace  trigger v_dummy_ioiu
        instead of insert or update on v_dummy
        referencing new as new old as old
        for each row


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



      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:


      Thanks in advance for help!


      I'm using SQL Developer on Mac.




        • 1. Re: How to export view with instead of trigger

          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.