Issuing 'alter' to add columns results in triggers to stop?

[Jesus Angeles]

I issued 2 commands. The first 'alter' is to increase the size of one column. The second 'alter' is to add 3 columns.

I noticed that the triggers on that table stopped.

Does those 'alter' cause stoppage of triggers?

[Martin Vajsar]

No, it shouldn't. A quick test on Oracle 11g shows all is nice and well:drop table log; drop table test; create table log(message varchar2(100)); create table test(a varchar2(10)); create trigger ti_test after insert on test for each row begin insert into log(message) values('trigger run: ' || :new.a); end; / show errors; insert into test(a) values('First run'); alter table test modify(a varchar2(20)); insert into test(a) values('Second run'); alter table test add(b number); insert into test(a) values('Third run'); select * from log;

Output:
SQL> select * from log; MESSAGE ---------------------------- trigger run: First run trigger run: Second run trigger run: Third run

What exactly does "stoppage" mean? Are the triggers invalid? Are they disabled?

[Jesus Angeles]

I think I should research further what caused it. It is probably not the 'alter' like you mentioned.

I think there is no log or way to find out what date/time the trigger stopped. Is that correct?

[James Boswell]

Can you not add logging to the trigger as demonstrated by Martin?

[Martin Vajsar]

I'm still baffled by 'stopped trigger'. Trigger can be disabled, or invalid. Use the USER_TRIGGERS view to inspect the state of the triggers.

In the USER_OBJECT view, there is the LAST_DDL_TIME column, but I don't thing that trigger invalidation or disabling would be reflected there. It could be still useful, though (perhaps the trigger was modified by someone).

If the trigger just does not do what is expected from it, it looks like there is a bug in it?

(Also, it is best not to use triggers at all. Triggers can give rise to very nasty and complicated bugs, most often when someone forgets about the magic done by the trigger behind the scenes. If a trigger can be replaced by a stored procedure, that would always be a better approach. Just saying. )

[chris webster]

Check the status of the triggers as Martin suggests. It's possible that your DDL might have invalidated the trigger, if the trigger was referring to the column you changed, but it should have sorted itself out afterwards:

Compiled triggers have dependencies. They become invalid if a depended-on object, such as a stored subprogram invoked from the trigger body, is modified. Triggers that are invalidated for dependency reasons are recompiled when next invoked.

Oracle 11g, Using Triggers

Also, as Martin points out, it's best to avoid using triggers e.g. see Oracle guru Tom Kyte's anti-trigger diatribe.