When I click on a trigger in the tree ... say an ISO trigger on a view that will clearly insert or update or delete from select underlying table ...
Under "References" tab ... I see nothing.
Under "Dependencies" tab ... I see mulitples of tons of things ... that to me ... have no relation atall_ to that trigger ... e.g. a table from a completely different group of tables.
But also ... a LOT of those wrong / odd relations ... like someone forgot a "select distinct" somewhere.
And wading thru this long list is ... VERY hard ... all I expected was ...
the view, maybe its synonym, its master table(s) to which it was going to write, and a few standard functions / package calls that we use in all our ISO triggers (and those items in turn do not reference the odd relations that are showing up instead).
Is this a bug perchance?
My SQL Dev version is ... 3.2.20.09 (downloaded 1/29/13).
Please advise. Thanks for your time.
(is there a way to post an image?)
Just did a quick check viewing a couple of triggers in the standard HR schema against 10g XE and 184.108.40.206. Both show the same dependencies, but no references. Are there any other details you can provide about your 10g case?
I was too quick by half ...
Against 10g ... if I start from a view with ISO trigger ...
Triggers tab has trigger, I right click, choose "Go To [trig name]" that is fine.
Once on trigger, References tab has nothing,
Dependencies tab has a zoo of stuff ... repeated over and over for no apparent reason.
IF I filter Dependencies on VIEW ... then I see all this stuff I would not have thought had ANY relation
to the trigger (to the caller view ... maybe, but its refs/dependencies appeared fine!).
And, after I filter, I can find the original starting point view.
Against 10g ... if I start from any trigger, and look at its Refs/Dependencies tabs ...
same deal: No Refs, tons of Deps, no clue as to why, must filter for sense.
Against 11g, same behaviors.
Cursory glance at select parts ...
Views have both Refs and Deps.
Tables have only Deps, no Refs.
Packages Specs have Table Deps, other action Refs.
Package Bodies have Synonym Dep, long long list of Refs (a la the Triggers).
Procedures and Functions seem to have both Refs and Deps in correct proportion.
Sequences have correct Refs, no Deps.
Types seem to be correct, we don't have a lot of these, so am not sure.
Public Synonyms shows ALL Pub Syns, not just those of the current schema.
All true for 10g and 11g ...
My conclusion: something is amiss with how Trigger and Package Body Refs / Dependencies are handled.
As in ... they are not to be trusted (at least, not thru SQL Developer) ... thus a bug.
That's all I know at moment, more later, if I see other things.
I suppose there is always the possibility of a bug, but in this case the object viewer definitely uses more than just one of the dependencies views to get its information. If you want to see exactly what the data dictionary says about such dependencies, go to View -> Reports -> Data Dictionary Reports -> Dependencies, get the report for the schema in question, then filter by the desired object name/type in the query result grid.
In the object viewer the SQL query is much more complex. It includes a join on the public_dependency view with a start with ... connect by nocycle prior. So the seemingly extra items under the trigger are due to
1. The view is dependent on the trigger which references (is dependent on) table(s).
2. The View is also dependent on the table(s)
3. The table(s), at the least, get double-counted.
4. The ISO case may be the "perfect storm" for this situation to become noticeable.
If views have multiple triggers, and triggers have multiple tables, the duplication, of course, is compounded.
Hope this helps,
Edited by: Gary Graham on Apr 12, 2013 5:24 PM
Very interesting Gary_Graham!
I will look at that ... I mostly find it interesting that ...
All those other "kinds of things" have no problem with their Refs / Deps.
(including Functions and Procedures)
BUT ... triggers and package body text ... THAT has a problem.
Which says to me that that "connect by" stuff (which I know is powerful, but which I am only learning (we use it in some places to flatten multi-record items to one row ... a bit of code we can ditch with new funcitonality in 11g, but ... we're not there yet)) ... is not quite right.
If there were just multiples of things "that I knew" were clearly related to all these triggers (and it is not just ISO triggers, that's just that's where I started looking) ... that would be one thing.
But no ... there stuff in there I have no idea how it's related to the trigger ... and since I vetted every inch of code in our database ... I really do have no idea (we're not talking Emp-Dept ... this is a calendaring / scheduling app of multi-related functionality (Oh, you have THAT calendar? can I use your database too, so my work can be shared with the already existing work? ... and so it has acreted purpose over the years)).
I will dig deeper into those things next week. Thanks for that info.
Gary, can images be posted here? Then you could see what I mean.
It's not related to synonyms, not that's obvious to me.
Tonight, I will post them to my Facebook page, and link them here ... unless there's another way.
I have no particular web-based drive space.
Thanks for your time,