Invalid Triggers

We've got instances in our database where a trigger has a status of enabled in user_triggers and has a status of invalid in user_objects...has anyone run into this before?

The first thing I'm trying to figure out is how does a trigger get invalidated?? I thought a trigger becomes invalid when you modify the stored procedure it's calling...or when you modify the table it's stored procedure is referencing...but I can't seem to produce that result. In order to prove my theory... here's what I did...

1. Created a temp_table with the following values: SQL> desc temp_table; Name Null? Type
   • -------- ---- NUM_COL NUMBER CHAR_COL VARCHAR2(60)
2. Created a debug_table with the following values: SQL> desc debug_table Name Null? Type
   • -------- ---- LINECOUNT NUMBER DEBUG_STR VARCHAR2(1100) DATE_COL DATE
3. Created a stored proc that inserts a record into the debug table: create or replace procedure xxx as begin insert into debug_table (debug_str) values (to_char(sysdate, 'MM/DD/YY HH24:MI:SS') || ': row inserted into temp_table'); end; /
4. Created a trigger that calls xxx on insert of the temp_table: create or replace trigger tbi_temp_table before insert on temp_table for each row begin xxx; end; /
5. Inserted a record into temp_table...the result was as predicted...a record was inserted into the debug_table via the stored procedure via the trigger...

(Are ya with me so far??? Here's where stuff starts getting unpredictable...)

6. Now...in theory...if I add a column to debug_table then the stored proc should become invalid and the trigger should be invalid right? Okay...so I add a column to debug table...called date_col2...Now the debug table looks like this:
SQL> desc debug_table

 Name                            Null?    Type


 ------------------------------- -------- ----

 LINECOUNT                                NUMBER
 DEBUG_STR                                VARCHAR2(1100)
 DATE_COL                                 DATE
 DATE_COL2                                DATE

7. I do a select object_name, object_type from user_objects where status = 'INVALID' and my results are:
OBJECT_NAME OBJECT_TYPE

------------------------- -------------
XXX                       PROCEDURE

8. Just to make sure I didn't miss something, I did a select object_name, status from user_objects where object_type = 'TRIGGER' and my results are:
OBJECT_NAME STATUS

------------------------- -------
TBI_TEMP_TABLE            VALID

So let me reiterate, the question is..."how do triggers get invalid?"

Thanks in advance for any help!

Dara Fong

-- 


Talk to you later!

Dara 

                       __\/__
                      / ^  ^ \
                   (\| (o)(o) |/)
------------------oOOOo--oo--oOOOo-------------------------
|  Dara Fong             e-mail: fongda<at>netcom<dot>com |
 -----------------------------------------------------------
|  Intelligence is like underwear, everyone has it but    |
|  you don't have to show it off.                         |
 -----------------------------------------------------------
|  Any unsolicited commercial e-mail and/or the inclusion |
|  of my user-id in any mailing list without my express   |
|  prior written approval, including the receipt of a mass|
|  e-mail message and/or the unauthorized reselling of    |
|  this user-id to mailing list vendors, will be met with |
|  a complaint to your internet provider. It is           |
|  recommended you do not attempt these practices. Should |
|  you choose to ignore this warning, you will be subject |
|  to any remedy which may be exercised by your internet  |
|  provider. You have been warned.                        | 
-----------------------------Oooo.-------------------------
                   .oooO     (   )
                   (   )      ) /
                    \ (      (_/
                     \_)


-- 


Talk to you later!

Dara 

                       __\/__
                      / ^  ^ \
                   (\| (o)(o) |/)
------------------oOOOo--oo--oOOOo-------------------------
|  Dara Fong             e-mail: fongda<at>netcom<dot>com |
 -----------------------------------------------------------
|  Intelligence is like underwear, everyone has it but    |
|  you don't have to show it off.                         |
 -----------------------------------------------------------
|  Any unsolicited commercial e-mail and/or the inclusion |
|  of my user-id in any mailing list without my express   |
|  prior written approval, including the receipt of a mass|
|  e-mail message and/or the unauthorized reselling of    |
|  this user-id to mailing list vendors, will be met with |
|  a complaint to your internet provider. It is           |
|  recommended you do not attempt these practices. Should |
|  you choose to ignore this warning, you will be subject |
|  to any remedy which may be exercised by your internet  |
|  provider. You have been warned.                        | 
-----------------------------Oooo.-------------------------
                   .oooO     (   )
                   (   )      ) /
                    \ (      (_/
                     \_)