Trigger validation

From: Phil Kaufman <philk_at_dbcsmartsoftware.com>
Date: Tue, 20 May 2003 14:21:16 -0400
Message-ID: <W%uya.127$Fl3.55278659@mantis.golden.net>

Hi folks,

My environment is Oracle 8.0.5 db server.

I have a script which performs some basic ddl statements, which fails. Here's the basics of the SQL:

ALTER TABLE s1_vehicle_processing

ADD inventory_dollar_value number(18,6) DEFAULT 0 not null /

ALTER TABLE s1_vehicle_processing

ADD inventory_contract_exch_rate number(20,10) DEFAULT 1 not null /

the table s1_vehicle_processing has before update (statement level), before update (row level), and after update (statement level) triggers. Through SQL*Plus, the first alter table works, the second gives me an Oracle error:

Connected.

Table altered.

ALTER TABLE s1_vehicle_processing
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1 ORA-04045: errors during recompilation/revalidation of GRAINDBA.BUS_S1_VEHICLE_PROCESSING
ORA-04020: deadlock detected while trying to lock object GRAINDBA.S1_VEHICLE_PROCESSING

Interestingly, the BUS_S1_VEHICLE_PROCESSING trigger gets invalidated by the first alter, but doesn't seem to be automatically revalidated by the second alter. Upon further investigation, when I re-validate manually the trigger between running statements, all seems to be fine.

My question is, can anyone make sense what is happening exactly behind the scenes here? I'll double check this, but it looks like I'll need to disable this trigger(s) first, do my alters, then re-enable. But I would still like to understand what is happening (I hate the try again later messages as 04020 suggests).

Thanks in advance,

Phil Received on Tue May 20 2003 - 13:21:16 CDT