File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Oracle/OAS and the fly likes Issuing 'alter' to add columns results in triggers to stop? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "Issuing Watch "Issuing New topic
Author

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

Jesus Angeles
Ranch Hand

Joined: Feb 26, 2005
Posts: 2049
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

Joined: Aug 22, 2010
Posts: 3606
    
  60

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

Joined: Feb 26, 2005
Posts: 2049
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

Joined: Nov 09, 2011
Posts: 1012
    
    5

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

Joined: Aug 22, 2010
Posts: 3606
    
  60

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

Joined: Mar 01, 2009
Posts: 1615
    
  13

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.


No more Blub for me, thank you, Vicar.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Issuing 'alter' to add columns results in triggers to stop?
 
Similar Threads
to run alter command in Access
Adding columns dynamically
how dynamic column display and also sorting?
adding image to JTable
Can we add a Field(column) in a table dynamically?