8 Replies Latest reply: Apr 17, 2013 3:41 PM by Gary Graham-Oracle RSS

    Trigger Dependencies / References view all out of whack ... is it me?

    SM_Work
      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?)
        • 1. Re: Trigger Dependencies / References view all out of whack ... is it me?
          SM_Work
          Wait! I notice this works correctly against an 11g database and incorrectly against a 10g database.

          Hmmm ... the thot plickens.
          • 2. Re: Trigger Dependencies / References view all out of whack ... is it me?
            Gary Graham-Oracle
            Hi,

            Just did a quick check viewing a couple of triggers in the standard HR schema against 10g XE and 11.2.0.1. Both show the same dependencies, but no references. Are there any other details you can provide about your 10g case?

            -Gary
            • 3. Re: Trigger Dependencies / References view all out of whack ... is it me?
              SM_Work
              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.
              • 4. Re: Trigger Dependencies / References view all out of whack ... is it me?
                Gary Graham-Oracle
                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,
                Gary

                Edited by: Gary Graham on Apr 12, 2013 5:24 PM
                • 5. Re: Trigger Dependencies / References view all out of whack ... is it me?
                  SM_Work
                  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.

                  Kind regards,

                  Suzanne Michelle
                  • 6. Re: Trigger Dependencies / References view all out of whack ... is it me?
                    Gary Graham-Oracle
                    stuff in there I have no idea how it's related to the trigger
                    Could those be synonyms? For Oracle DB 10 and higher, the query should also return any synonyms.

                    -Gary
                    • 7. Re: Trigger Dependencies / References view all out of whack ... is it me?
                      SM_Work
                      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,
                      Suzanne
                      • 8. Re: Trigger Dependencies / References view all out of whack ... is it me?
                        Gary Graham-Oracle
                        Suzanne,
                        can images be posted here?
                        No, but I'm hoping a soon-to-come upgrade to the external Oracle forums may permit it:
                        Re: We are upgrading OTN Forums!  This is the place for questions and comments.

                        the internal Oracle forums already do. You may want to search the web for some free service to host those images that is more anonymous than a Facebook post...

                        -Gary