Win a copy of The Java Performance Companion this week in the Performance forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
Jesus Angeles
Ranch Hand
Posts: 2068
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
No, it shouldn't. A quick test on Oracle 11g shows all is nice and well:

Output:


What exactly does "stoppage" mean? Are the triggers invalid? Are they disabled?
 
Jesus Angeles
Ranch Hand
Posts: 2068
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Bartender
Posts: 1051
5
Chrome Eclipse IDE Hibernate
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Can you not add logging to the trigger as demonstrated by Martin?
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Bartender
Posts: 2407
33
Linux Oracle Postgres Database Python Scala
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic