Triggers

SQL Navigator
1

We are back to unable to see triggers. I turned Monitor on and here is the query:

DECODE(t.status, ‘DISABLED’, DECODE

DECODE(o.status, ‘VALID’, 0, ‘INVALID’, 1, 0)), o

FROM sys.all_triggers t, sys.all_objects o

AND t.trigger_name = o.object_name

I see it is using all_ instead of DBA even though my Enable DBA Views is on.

(In 6.3)

SELECT o.object_name , (o.status, ‘VALID’, 2, ‘INVALID’, 3, 0), .owner WHERE o.object_type = ‘TRIGGER’ AND o.owner = t.owner AND t.table_owner = ‘SSAC6004’ AND t.table_name = ‘CIC’ AND o.object_name NOT LIKE ‘BIN$%’ Also the search feature does a similar thing:
SELECT /+FIRST_ROWS/ OWNER,TRIGGER_NAME NAME, TRIGGER_TYPE, TRIGGERING_EVENT, TABLE_OWNER, TABLE_NAME, ‘TRIGGER’ TYPE,
TRIGGER_BODY T FROM sys.all_TRIGGERS WHERE OWNER LIKE ‘L%’

Message was edited by: DaleFromLozier

2

Hi Dale

We were able to reproduce your issue here when ‘enable dba’ was not selected in the login screen.
Can you check that you have selected DBA option via the login window, rather than the preferences.

regards
Lidia

3

This did fix the problem. I am able to see all triggers. Odd how I have to have that set for Triggers but not for other objects. It also fixed the search.

4

Ok, so we are getting close on these triggers. When I double click on a trigger and I am not the owner the “body” of the trigger is not shown, so I turned on SQL Monitoring and got this:
Timestamp: 8/19/2010 8:17:34 AM

select trigger_body from sys.all_triggers
where owner=‘LOZIERAPPS’ and trigger_name=‘TRGGR_ORHDRM_TRANS_AX’

owner=[‘LOZIERAPPS’]
name=[‘TRGGR_ORHDRM_TRANS_AX’]

Elapsed time: 0.000

All_Triggers to get the body is not going to work for my situation.

Below is a “summary” of the select statements what is using “all” and what is using “dba” I do not know if all are important, but I would guess the one above is…

.
select object_id,last_ddl_time,status from sys.dba_objects
select trigger_body from sys.all_triggers
BEGIN
SELECT debuginfo
INTO :result
FROM sys.all_probe_objects
WHERE owner = :owner
AND object_name = :name
AND object_type = :type
AND status = ‘VALID’;
END;
SELECT /*+ ALL_ROWS */
object_name, overload, argument_name,
DECODE(data_type,
‘BFILE’, 8, ‘BINARY_DOUBLE’, 2,
‘BINARY_FLOAT’, 2,
‘BINARY_INTEGER’, 2,
‘BLOB’, 7,
‘CHAR’, 1,
‘CLOB’, 6,
‘DATE’, 3,
‘FLOAT’, 2,
‘INTERVAL DAY TO SECOND’, 1,
‘INTERVAL YEAR TO MONTH’, 1,
‘LONG’, 18,
‘LONG RAW’, 18,
‘NCHAR VARYING’, 1,
‘NCHAR’, 1,
‘NCLOB’, 6,
‘NUMBER’, 2,
‘NVARCHAR2’, 1,
‘OBJECT’, 10,
‘PL/SQL BOOLEAN’, 5,
‘PL/SQL RECORD’, 15,
‘PL/SQL TABLE’, 16,
‘RAW’, 13,
‘REF CURSOR’, 4,
‘REF’, 12,
‘ROWID’, 17,
‘TABLE’, 11,
‘TIME’, 3,
‘TIMESTAMP WITH LOCAL TIME ZONE’, 1,
‘TIMESTAMP WITH TIME ZONE’, 1,
‘TIMESTAMP’, 1,
‘UROWID’, 17,
‘VARCHAR’, 1,
‘VARCHAR2’, 1,
‘VARRAY’, 14,
‘XML’, 9,
0)
type_id, position,
DECODE(in_out, ‘IN’, 1, ‘OUT’, 2, ‘IN/OUT’, 3, 0) io, data_type,
data_level, type_owner, type_name, type_subname, data_length,
data_precision, data_scale, default_value, pls_type
FROM sys.all_arguments
WHERE object_id = 290187
ORDER BY object_name, overload, sequence

select /*+ ALL_ROWS */ sequence,text,line,position from sys.all_errors
select owner,a.object_name,a.object_id,a.created,a.last_ddl_time,decode(a.status,‘VALID’,0,‘INVALID’,1,2) status
from sys.dba_objects a
select object_id,last_ddl_time,status from sys.dba_objects

5

This is still not working, I have tried to filter down the selects to show the difference…

select object_id,last_ddl_time,status from sys.dba_objects
select trigger_body from sys.all_triggers
SELECT debuginfo
FROM sys.all_probe_objects
SELECT /*+ ALL_ROWS /
FROM sys.all_arguments
select /+ ALL_ROWS */ sequence,text,line,position from sys.all_errors
select owner,a.object_name,a.object_id,a.created,a.last_ddl_time,
decode(a.status,‘VALID’,0,‘INVALID’,1,2) status
from sys.dba_objects a
select referencing_names,when_clause,description,trigger_body,column_name,action_type
from sys.dba_triggersselect object_id,last_ddl_time,status from sys.dba_objects

We still have the workaround of extract ddl, but it would be really nice if we could get this to work.

6

Not Fixed. I realize this is not a high priority, but I look forward tot he day when I can view a trigger without extracting it. It is still using:

select trigger_body from sys.all_triggers
where owner=‘xxxxx’ and trigger_name=‘xxxxx’

ALL Triggers will not work for us as we are not allowed to modify any triggers we are only allowed to view them, hence they are not available in the all triggers but we are allowed to see dba_triggers.

oh, and a new issue probably directly related, when the only thing open is the blank trigger editor (with the properties at the bottom) the x (close window in upper right) not entire navigator (x) is greyed out. If it is a trigger I can edit the x is fine and it will close the trigger and bring up the generic code editor…

Message was edited by: DaleFromLozier

7

Hi Dale,

I have escalated your request (CR 14411) and we should fix it as soon as possible. Hopefully you can see it in the first 6.7 beta.

Thanks,
Vincent

8

Fixed.