Is it possible for me to be notified if an 'after insert or update trigger' stops working? Last week, I found my after insert or update trigger wasn't working. After I disabled and enabled it, it started working again.

I do not yet know why it stopped working. Is there any way to deal with this?  Because this trigger is recording the value of daily jobs, and is used for report purposes. If this trigger goes dead in few days without my notice or error, I will be in hot water.

I am using Oracle 10g, access the db by using sqldeveloper

My Trigger

create or replace
TRIGGER MASTER.INSTANCE_STEP_TRG 
AFTER INSERT OR UPDATE OF SYSID,STEP_ID,INSTANCE_ID,PARENT_STEP_ID ON MASTER.WF_INSTANCE_STEP 
REFERENCING OLD AS old NEW AS new 
FOR EACH ROW 
WHEN (new.sysid &gt; 0) 
declare 
stepSysid number;
crCode varchar(50);
crDate date;
step_id number;
BEGIN
  step_id := :new.step_id;

  select ss.sysid into stepSysid from TEMPLATE.wf_step ws 
  inner join TEMPLATE.step_stage ss on ss.sysid=ws.stage_id
  where ws.sysid= step_id;


  if ( stepSysid&gt;0) then
    insert into MASTER.fact_cr_progress values(0,:new.instance_id,stepSysid,:new.create_dt);
  end if;

  dbms_output.enable(10000);
  dbms_output.put_line('start print');

END;
A trigger cannot simply stop working.

A trigger can be disabled.  A trigger can be dropped.  A trigger will be made invalid if DDL is done to one of the objects it references but it will still be executed if the triggering statement is executed.  If the trigger fails to recompile successfully, the triggering statement will get an error 

ORA-04098: trigger ' MASTER.INSTANCE_STEP_TRG' is invalid and failed re-validation


It also seems possible that the trigger was working correctly as declared but not as you intended.  For example, it is possible that the new.sysid was not greater than 0 (remember that NULL is not greater than 0) causing the WHEN clause to not be met.  It is possibly that your SELECT statement returned a value less than or equal to 0, thus causing your INSERT not to be executed.  If either TEMPLATE.wf_step or TEMPLATE.step_stage has a column STEP_ID, the WHERE clause

where ws.sysid= step_id;


will interpret STEP_ID as the column in the table, not your local variable STEP_ID.  One of the reasons that PL/SQL developers generally add prefixes to local variables (i.e. L_STEP_ID rather than STEP_ID) is to avoid inadvertently using a name that is also used by a column in a table because these sorts of scope resolution problems are notoriously difficult to debug.