Re: DBA_SOURCE vs DBA_TRIGGERS

"Michael J. Moore" wrote:

> The following shows a join between dba_triggers and dba_source
> (well you can see what it does for yourself) there was actually a lot more
> but I snipped it short.
>
> 1 select a.trigger_name,b.type,b.line,b.text
> 2 from dba_triggers a, dba_source b
> 3 where a.trigger_name = b.name and b.line < 2
> 4* order by a.trigger_name,b.line
> SQL> /
>
> TRIGGER_NAME TYPE LINE
> ------------------------------ ------------ ----------
> TEXT
> ----------------------------------------------------------------------------
> ------------------------
> AURORA$SERVER$SHUTDOWN TRIGGER 1
> trigger aurora$server$shutdown before shutdown on database call
> dbms_java.server_shutdown
>
> AURORA$SERVER$STARTUP TRIGGER 1
> trigger aurora$server$startup after startup on database call
> dbms_java.server_startup
>
> CDC_ALTER_CTABLE_BEFORE TRIGGER 1
> TRIGGER sys.cdc_alter_ctable_before
>
> CDC_CREATE_CTABLE_AFTER TRIGGER 1
> TRIGGER sys.cdc_create_ctable_after
>
> Also, check this out ..........
>
> 1 SELECT a.trigger_name
> 2 FROM dba_triggers a
> 3 WHERE a.trigger_name not in
> 4* (SELECT name FROM dba_source WHERE text is not null)
> SQL> /
>
> no rows selected
>
> So, unless I'm not thinking straight, this shows that EVERY dba_triggers row
> has at
> least one corresponding row in dba_source.
>
> Right?
> Mike
>
> "Daniel Morgan" <dmorgan_at_exesolutions.com> wrote in message
> news:3D88A680.88F8440A_at_exesolutions.com...
> > "Michael J. Moore" wrote:
> >
> > > For trigger source I understand that the source code can be found in
> > > DBA_TRIGGERS.
> > > Also, I thought that the same trigger source could be found in
> DBA_SOURCE.
> > > However,
> > > this does not seem to be always the case. So, the question is .. Under
> what
> > > conditions should
> > > one expect to find ( or not find) trigger source in DBA_SOURCE.
> > >
> > > Does source being in either of these tables contengent on a clean
> compile?
> > > Mike
> >
> > I would never expect to find trigger source code in dba_source. dba_source
> is
> > the code repository for procedures, functions, and packages. If it is
> there I
> > would be surprised.
> >
> > Daniel Morgan
> >

One of us is missing something. Try this instead:

SELECT trigger_name
FROM user_triggers
INTERSECT
SELECT name
FROM user_source;

I get no corresponding rows from 15 triggers and 2536 rows in user_source.

Daniel Morgan Received on Wed Sep 18 2002 - 13:24:45 CDT