Disable/Enable Triggers

I have stored procedure that disable triggers from another table then
completes the
body for the stored procedures then enables the triggers.
Is there an easy way to complete this task of disable and enable triggers
with very little overhead.
Thank You,
On Thu, 3 Nov 2005 14:30:03 -0800, Joe K. <Joe
K.@.discussions.microsoft.com> wrote:

>I have stored procedure that disable triggers from another table then
>completes the
>body for the stored procedures then enables the triggers.
>Is there an easy way to complete this task of disable and enable triggers
>with very little overhead.
>Thank You,
Hi Joe,
The good news: look up
ALTER TABLE table
{ ENABLE | DISABLE } TRIGGER { ALL | trigger_name [,..n] }
in Books Online.
The bad news: this is not a per-connection setting. If you disable the
trigger, it won't fire for ANY connection that's accessing the table.
Unless you can be sure that the stored proc is the only active process
at the time, I'd urge you to find another way to achieve what you want.
If you post more information about what business problem you're trying
to solve, we can help you find a better way.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Disable/Enable Triggers

I have stored procedure that disable triggers from another table then
completes the
body for the stored procedures then enables the triggers.
Is there an easy way to complete this task of disable and enable triggers
with very little overhead.
Thank You,On Thu, 3 Nov 2005 14:30:03 -0800, Joe K. <Joe
K.@.discussions.microsoft.com> wrote:
>I have stored procedure that disable triggers from another table then
>completes the
>body for the stored procedures then enables the triggers.
>Is there an easy way to complete this task of disable and enable triggers
>with very little overhead.
>Thank You,
Hi Joe,
The good news: look up
ALTER TABLE table
{ ENABLE | DISABLE } TRIGGER { ALL | trigger_name [,..n] }
in Books Online.
The bad news: this is not a per-connection setting. If you disable the
trigger, it won't fire for ANY connection that's accessing the table.
Unless you can be sure that the stored proc is the only active process
at the time, I'd urge you to find another way to achieve what you want.
If you post more information about what business problem you're trying
to solve, we can help you find a better way.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Disable/Enable Triggers

I have stored procedure that disable triggers from another table then
completes the
body for the stored procedures then enables the triggers.
Is there an easy way to complete this task of disable and enable triggers
with very little overhead.
Thank You,On Thu, 3 Nov 2005 14:30:03 -0800, Joe K. <Joe
K.@.discussions.microsoft.com> wrote:

>I have stored procedure that disable triggers from another table then
>completes the
>body for the stored procedures then enables the triggers.
>Is there an easy way to complete this task of disable and enable triggers
>with very little overhead.
>Thank You,
Hi Joe,
The good news: look up
ALTER TABLE table
{ ENABLE | DISABLE } TRIGGER { ALL | trigger_name [,..n] }
in Books Online.
The bad news: this is not a per-connection setting. If you disable the
trigger, it won't fire for ANY connection that's accessing the table.
Unless you can be sure that the stored proc is the only active process
at the time, I'd urge you to find another way to achieve what you want.
If you post more information about what business problem you're trying
to solve, we can help you find a better way.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Disable Job Still Executing

I have data archive process that disables the backup job then enables the
job when the process has completed. One thing the job still executes as
schedule. I tried disabling the schedule as well as the job which did not
help.
I'm running SQL Server 2000 SP 4 on Windows Server 2003 SP 1.
Thanks in advance
BruceHi Bruce
You should be ok with just disabling the schedule with a command such as
EXEC msdb..sp_update_jobschedule @.job_name = 'My Backup Job',
@.name ='Nighly
Backup @. 22:00',
@.enabled = 0
Make sure that the job history shows that the jobs were run and that when
you display the steps, the step to disable the job was run. You may also wan
t
to log this to a file.
John
"Bruce" wrote:

> I have data archive process that disables the backup job then enables the
> job when the process has completed. One thing the job still executes as
> schedule. I tried disabling the schedule as well as the job which did not
> help.
> I'm running SQL Server 2000 SP 4 on Windows Server 2003 SP 1.
> Thanks in advance
> Bruce
>
>

Disable Job Still Executing

I have data archive process that disables the backup job then enables the
job when the process has completed. One thing the job still executes as
schedule. I tried disabling the schedule as well as the job which did not
help.
I'm running SQL Server 2000 SP 4 on Windows Server 2003 SP 1.
Thanks in advance
BruceHi Bruce
You should be ok with just disabling the schedule with a command such as
EXEC msdb..sp_update_jobschedule @.job_name = 'My Backup Job',
@.name ='Nighly
Backup @. 22:00',
@.enabled = 0
Make sure that the job history shows that the jobs were run and that when
you display the steps, the step to disable the job was run. You may also want
to log this to a file.
John
"Bruce" wrote:
> I have data archive process that disables the backup job then enables the
> job when the process has completed. One thing the job still executes as
> schedule. I tried disabling the schedule as well as the job which did not
> help.
> I'm running SQL Server 2000 SP 4 on Windows Server 2003 SP 1.
> Thanks in advance
> Bruce
>
>