Triggers on Views in SQLDeveloper

So, you’ve noticed that SQLDeveloper ( any version prior to 2.1.1) doesn’t show triggers on views. At the same time SQLDeveloper 2.1.1 doesn’t show package bodies unless you have a very highly privileged account.
If you’re determined to press-on with 2.1.1, you can see a workaround for the package body problem here.
If however, you’d prefer to wait until they’ve ironed out this particular kink before taking the plunge, but want a solution to the invisible triggers, read on …

One of the really cool things about SQLDeveloper is the relative ease with which you can extend it, even adding custom tabs to various views.
The good news is that you don’t need any 3GL expertise for this. You don’t even have to know that much about XML. You just need to work out the query you need to run and drop it into the relevant XML template.
First off, let’s find an illustration of the problem.

For the purposes of this article, I’m going to refer to two schemas that come pre-loaded with XE.
Let’s start by firing up SQLDeveloper and connecting as the HR user.

Now, let’s see if we can find the triggers on the PATH_VEIW view in the XDB schema :

SELECT trigger_name, trigger_type
FROM all_triggers
WHERE owner = 'XDB'
AND table_name = 'PATH_VIEW'
/

Yep, there’s the trigger XDB_PV_TRIG large as life. But if we now navigate the object tree to the same view in the XDB schema and click on the Triggers tab we see…nix, nada, nothing, not a sausage.

Fortunately, we can drop the appropriate query into the appropriate XML format and, hey presto, a Custom Extension which displays a tab with the missing trigger information.
First off, the XML :

<items> 
<item type="editor" node="ViewNode" vertical="true"> 
<title><![CDATA[Triggers - Custom]]></title> 
<query> 
<sql><![CDATA[SELECT owner, trigger_name, trigger_type, triggering_event, status, 
trigger_body as Body
FROM all_triggers 
WHERE table_owner = :OBJECT_OWNER 
AND table_name = :OBJECT_NAME 
ORDER BY trigger_name]]></sql> 
</query> 
</item> 
</items>

Three things to note here.
First, in the item tag, the node is specified as ViewNode, which means that this tab will appear when we’re looking at views in the navigator.
Next, the title of the tab will be Triggers – Custom.
Finally, the query tag is where we put the SQL. Note that :OBJECT_NAME and :OBJECT_OWNER are pre-defined variables that automatically pick up the name and owner of the object in context.

Of course, if you have the appropriate privileges, you could replace the trigger_body with a call to DBMS_METADATA.GET_DDL. This has the advantage of retrieving the complete DDL to create the trigger, not just the body. However, you will run into problems – in the shape of ORA-31603 – if you don’t have the SELECT_CATALOG_ROLE role granted. Furthermore, you’ll only be able to see triggers owned by the user who also owns the view as the only SQL variables available in this XML file are :OBJECT_OWNER and :OBJECT_NAME. These refer to the object that you’re viewing at the time ( in this case, the view).
All in all, it’s probably safer not to use DBMS_METADATA in this instance.

Now it’s just a case of adding the file as a custom extension.
In the SQLDeveloper Tools menu, select Preferences.

Open up the Database node in the tree and click on User Defined Extensions.

Click Add Row

Select Editor from the drop-down.

Click the Browse button and select the location of the XML file.

Finally, click OK.

Close and re-start SQLDeveloper. The new tab should now show for all views. More to the point, you’ll be able to see details of the triggers on the views.

If you want to see the code of the trigger body, then the best way is probably to right-click on the trigger and select Single Record View from the pop-up menu.