Subject Re: [IBO] Disable Stored Proc?
Author Helen Borrie
At 12:54 PM 21/11/2007, you wrote:
>> This isn't an IBO question.
>sorry, thought I was in the FB ng--my bad. I'll continue here, unless
>you direct otherwise.
>>If you want to disable the trigger for ever, you will need to
>>either drop the trigger, or alter it so that it doesn't call
>>the SP. If you want to disable the trigger temporarily, just
>>perform an 'ALTER TRIGGER triggername INACTIVE'; and don't
>>forget to do an 'ALTER TRIGGER triggername ACTIVE' again
>thanks... but I really want to do this to a stored proc, not the
>trigger because the stored proc is one location, the triggers are on a
>lot of tables. If I can do it on the proc level, it would be:
>Inactivate Poc
> Update Database
> Activate Proc

There is no such thing. A SP "just exists", ready to run when called.

Maybe you can have some kind of a routine where you do an 'alter procedure' to replace the existing one with a do-nothing one temporarily (or permanently, I still can't figure out what you're aiming for!) The trap with that of course is that you have to have the cache empty in order for it to "take". That means logging out all users (including sysdba), and doing the change in exclusive access mode before you put the database online again.

>if I have to do it on the trigger, it is going to be a lot more work,
>having to know the names of any before-update triggers each time I
>alter a table

Ummmm....if you are the DBA, don't you *know* the names of all your triggers and what they do?