Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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;

2 Answers
2

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

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.

hi,thanks for your reply. My new.sysid is a primary key(sysid+1 for new record) for MASTER.WF_INSTANCE_STEP table, Isn't new.sysid should always greater than 0? Except the error occur during MASTER.WF_INSTANCE_STEP insertion, in that case, my trigger should not run too.
–
50LV3ROct 21 '11 at 3:42

based on your reply, so the trigger will only stop working when there is an error occur. In my case, my trigger is actually run, but because of my bad practices and logic, thereby it did not execute the sql
–
50LV3ROct 21 '11 at 4:18

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.

In this case, do you mean i should create another trigger to keep track when is triggerA (assume the above is triggerA) has executed and the sql statement that the triggerA have run?
–
50LV3ROct 21 '11 at 4:20

It doesn't need to be a trigger ... you just write a timestamp somewhere, and you look to see if that timestamp is drifting too far from now() ... it could be a stored procedure, or just a simple query against a table: select now()-last_run from tracking_table. (ah ... date arithmetic is so much easier in oracle)
–
JoeOct 21 '11 at 14:27