Is it possible for me to be notified if an 'after insert or update trigger' stops working?

Question

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 > 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>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;

Answer 1

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.

Answer 2

It likely depends on why the trigger 'stops working'.

If it's being triggered, but doesn't run to completion, you can put something to trigger a message in the EXCEPTION block.

If it's been disabled entirely, you'd have to find some characteristic that's true when it's running (eg, I have some triggers that populate a sort of materialized view that's difficult to compute on the fly. I have a cron job that checks to see when the most recent record in that table is, and if it's too old, it reports it). If you don't have something obvious like that, you could always have a table that's just for tracking when triggers are last run, and update it w/ the current time.

Of course, neither of these really help the case that Justin Cave mentioned, where the commands are being misinterpreted because of variable name resolution. You could put an else on the if ( stepSysid>0) branch, and put reporting there if it's something that should never actually happen.