Toad World® Forums

Open a trigger not owned by me.


#1

If I search and find a trigger not owned by my user (DBA Views needed to see it) and click on it in the search I get:

8:44:11 Oracle Dictionary: TRIGGER SSAC6004.SIFCCODES not found

If I go to the user and expand triggers (See picture expandtrigger.jpg) I get no Triggers, but if I go to the table the trigger is on directly and expand triggers they are there (See picture bytable_trigger.jpg).

I have checked this in 5.x and everything works as expected. When I search and double click it shows up, when I expand all triggers for the user they all show up and of course when I expand the table then triggers the triggers for that table show up.

bytable_trigger.jpeg

expandtrigger.jpeg


#2

Hi Dale,
Mine works fine so Im not sure why that happened to you. We haven’t make much changes in the DB Nav tree so it should behave the same as 5.5.
Please follow the steps below so we get more information to work on.

  • start Nav
  • Open the search object window
  • Before you click the search button, go to Start>All Programs > Quest Software > SQL Navigator 6.0 Beta > Sql Monitor
  • On the application tab, tick the sqlnav6.exe
  • Start searching, the tab on the right in SQL monitor will be populated with some queries.
  • Ctrl+A and save this to a file
  • Log onto the other use who owns the trigger
  • Before expand the trigger node, delete everything in the right tab in SQL Monitor
  • Although the trigger node cant be expanded, try to double click to see if anything get spit out in SQL monitor
  • if there is, please save it to a file and send to us

Gwen


#3

Ok, I did not search using object search, but here is what object search gave me:
select owner,object_id,created,nvl(last_ddl_time,created) last_ddl_time,decode(status,‘VALID’,0,‘INVALID’,1,2) status,
object_name,object_name,decode(object_type,‘TABLE’,1,‘VIEW’,2,‘INDEX’,3,‘SNAPSHOT LOG’,4,‘PROCEDURE’,5,‘FUNCTION’,6,‘PACKAGE’,7,
‘PACKAGE BODY’,8,‘TRIGGER’,9,‘SEQUENCE’,10,‘SYNONYM’,11,‘CLUSTER’,12,‘DATABASE LINK’,13,‘SNAPSHOT’,14,‘MATERIALIZED VIEW’,14,
‘TYPE’,15,‘TYPE BODY’,16,‘LIBRARY’,19,‘DIRECTORY’,20,‘QUEUE’,21,‘JAVA SOURCE’,22,‘JAVA CLASS’,23,‘JAVA RESOURCE’,24,‘INDEXTYPE’,
25,‘OPERATOR’,26,‘DIMENSION’,31,‘CONTEXT’,32,‘RESOURCE PLAN’,33,‘CONSUMER GROUP’,34,0) object_type from sys.all_objects where
upper(owner) like ‘SSAC6004’ and object_type in (‘TRIGGER’)

I’m pretty sure we will never find this in all objects. I do not have the login for the other user id, but when I search using Code Search the monitor window has this:

Timestamp: 07:08:27.166
SELECT /+FIRST_ROWS/ OWNER,TRIGGER_NAME NAME, TRIGGER_TYPE, TRIGGERING_EVENT, TABLE_OWNER, TABLE_NAME, ‘TRIGGER’ TYPE,
TRIGGER_BODY T FROM sys.dba_TRIGGERS WHERE TRIGGER_NAME LIKE ‘SIFCCODES%’ AND OWNER LIKE ‘SSA%’

No I don’t have the SQL Ran in 5.x, but if I run the same object search it does return and if I double click it it opens the trigger, but 6 still gives me the error when I find it in code search.

I’m sorry I’m not much help. If it is critical you get this additional information I can get the DBA’s over here and log in as that user, but they will not give me the password.


#4

If you haven’t found an answer to this, I think I know what it is, We have 3rd party software, that we have to put triggers on to send information to our legacy system. We are not authorized to access this data, so we have no permissions on the tables themselves, however, we are supposed to be able to support issues that arise (Potentially from the triggers), so to get around this, we have no grants to the tables themselves (Hence we can’t do anything), but to give us the ability to review the code that is on the data we have been granted permissions to dba views. So it seems when the search is done it is against DBA views, but when the attempt to launch is done it is done against all views. We need the ability to launch the search against dba_trigger (for triggers), dba_source for (Packages, procedures and functions) etc. However, we have numerous systems and some we have not been granted DBA views, so we do need on those to switch to all views, so I really think this needs to take the setting from the view preferences. Sorry for the annoyance, I know we do things a little strangly… (We do use this tool extensively mine is launched for most of the day. )…