How do you disable and enable the trigger on a table automatically at some
time daily?Hi,
Identify the triggers which needs to be disabled. Then use the below Alter
command to disable
1. Create a SQL Agent job to disable the trigger and schedule a time
ALTER TABLE <Table_name> Disable Trigger <Trigger_ name>
2. Create another SQL Agent job to enable the trigger and schedule a time
ALTER TABLE <Table_name> Enable Trigger <Trigger_ name>
Make sure data integrity is not lost when you disable the trigger.
Thanks
Hari
SQL Server MVP
"00KobeBrian" <a@.b.com> wrote in message
news:e2UoIgU0GHA.4264@.TK2MSFTNGP05.phx.gbl...
> How do you disable and enable the trigger on a table automatically at some
> time daily?
>|||Hi,
Thanks Hari for greate information. I just like to provide following link
for detailed information:
http://msdn2.microsoft.com/en-us/library/ms182706.aspx
http://msdn2.microsoft.com/en-us/library/ms189748.aspx
Hope this helps.
Have a good day!
Best regards,
Vincent Xu
Microsoft Online Partner Support
======================================================Get Secure! - www.microsoft.com/security
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties,and confers no rights.
======================================================>>From: "Hari Prasad" <hari_prasad_k@.hotmail.com>
>>References: <e2UoIgU0GHA.4264@.TK2MSFTNGP05.phx.gbl>
>>Subject: Re: disable and enable trigger on schedule
>>Date: Tue, 5 Sep 2006 20:56:58 -0500
>>Lines: 26
>>X-Priority: 3
>>X-MSMail-Priority: Normal
>>X-Newsreader: Microsoft Outlook Express 6.00.2900.2869
>>X-RFC2646: Format=Flowed; Response
>>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2869
>>Message-ID: <#WLdTeV0GHA.4448@.TK2MSFTNGP04.phx.gbl>
>>Newsgroups: microsoft.public.sqlserver.server
>>NNTP-Posting-Host: cpe-65-27-44-57.kc.res.rr.com 65.27.44.57
>>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTNGP04.phx.gbl
>>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:444272
>>X-Tomcat-NG: microsoft.public.sqlserver.server
>>Hi,
>>Identify the triggers which needs to be disabled. Then use the below
Alter
>>command to disable
>>1. Create a SQL Agent job to disable the trigger and schedule a time
>>ALTER TABLE <Table_name> Disable Trigger <Trigger_ name>
>>2. Create another SQL Agent job to enable the trigger and schedule a time
>>ALTER TABLE <Table_name> Enable Trigger <Trigger_ name>
>>Make sure data integrity is not lost when you disable the trigger.
>>Thanks
>>Hari
>>SQL Server MVP
>>"00KobeBrian" <a@.b.com> wrote in message
>>news:e2UoIgU0GHA.4264@.TK2MSFTNGP05.phx.gbl...
>> How do you disable and enable the trigger on a table automatically at
some
>> time daily?
>>
>>|||How can I know if the trigger is enabled or disabled? Thanks.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23WLdTeV0GHA.4448@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Identify the triggers which needs to be disabled. Then use the below Alter
> command to disable
> 1. Create a SQL Agent job to disable the trigger and schedule a time
> ALTER TABLE <Table_name> Disable Trigger <Trigger_ name>
> 2. Create another SQL Agent job to enable the trigger and schedule a time
> ALTER TABLE <Table_name> Enable Trigger <Trigger_ name>
> Make sure data integrity is not lost when you disable the trigger.
> Thanks
> Hari
> SQL Server MVP
> "00KobeBrian" <a@.b.com> wrote in message
> news:e2UoIgU0GHA.4264@.TK2MSFTNGP05.phx.gbl...
>> How do you disable and enable the trigger on a table automatically at
>> some time daily?
>|||Hi 00KobeBrian
Use the function OBJECTPROPERTY
SELECT OBJECTPROPERTY(object_id('name of trigger')',
'ExecIsTriggerDisabled')
--
HTH
Kalen Delaney, SQL Server MVP
"00KobeBrian" <a@.b.com> wrote in message
news:OOsLHBX0GHA.1256@.TK2MSFTNGP02.phx.gbl...
> How can I know if the trigger is enabled or disabled? Thanks.
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:%23WLdTeV0GHA.4448@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> Identify the triggers which needs to be disabled. Then use the below
>> Alter command to disable
>> 1. Create a SQL Agent job to disable the trigger and schedule a time
>> ALTER TABLE <Table_name> Disable Trigger <Trigger_ name>
>> 2. Create another SQL Agent job to enable the trigger and schedule a time
>> ALTER TABLE <Table_name> Enable Trigger <Trigger_ name>
>> Make sure data integrity is not lost when you disable the trigger.
>> Thanks
>> Hari
>> SQL Server MVP
>> "00KobeBrian" <a@.b.com> wrote in message
>> news:e2UoIgU0GHA.4264@.TK2MSFTNGP05.phx.gbl...
>> How do you disable and enable the trigger on a table automatically at
>> some time daily?
>>
>|||Hi,
If you are using SQL 2005, you can refer to following article:
<http://msdn2.microsoft.com/en-us/library/ms188746.aspx>
Thanks.
Best regards,
Vincent Xu
Microsoft Online Partner Support
======================================================Get Secure! - www.microsoft.com/security
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties,and confers no rights.
======================================================>>From: "00KobeBrian" <a@.b.com>
>>References: <e2UoIgU0GHA.4264@.TK2MSFTNGP05.phx.gbl>
<#WLdTeV0GHA.4448@.TK2MSFTNGP04.phx.gbl>
>>Subject: Re: disable and enable trigger on schedule
>>Date: Wed, 6 Sep 2006 12:52:34 +0800
>>Lines: 33
>>X-Priority: 3
>>X-MSMail-Priority: Normal
>>X-Newsreader: Microsoft Outlook Express 6.00.2900.2869
>>X-RFC2646: Format=Flowed; Response
>>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2962
>>Message-ID: <OOsLHBX0GHA.1256@.TK2MSFTNGP02.phx.gbl>
>>Newsgroups: microsoft.public.sqlserver.server
>>NNTP-Posting-Host: 202.40.134.130
>>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTNGP02.phx.gbl
>>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:444283
>>X-Tomcat-NG: microsoft.public.sqlserver.server
>>How can I know if the trigger is enabled or disabled? Thanks.
>>
>>"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
>>news:%23WLdTeV0GHA.4448@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> Identify the triggers which needs to be disabled. Then use the below
Alter
>> command to disable
>> 1. Create a SQL Agent job to disable the trigger and schedule a time
>> ALTER TABLE <Table_name> Disable Trigger <Trigger_ name>
>> 2. Create another SQL Agent job to enable the trigger and schedule a
time
>> ALTER TABLE <Table_name> Enable Trigger <Trigger_ name>
>> Make sure data integrity is not lost when you disable the trigger.
>> Thanks
>> Hari
>> SQL Server MVP
>> "00KobeBrian" <a@.b.com> wrote in message
>> news:e2UoIgU0GHA.4264@.TK2MSFTNGP05.phx.gbl...
>> How do you disable and enable the trigger on a table automatically at
>> some time daily?
>>
>>
>>|||Can you please give me an example? I am using SQL 2000. Thanks.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:eKMpdLX0GHA.5100@.TK2MSFTNGP05.phx.gbl...
> Hi 00KobeBrian
> Use the function OBJECTPROPERTY
> SELECT OBJECTPROPERTY(object_id('name of trigger')',
> 'ExecIsTriggerDisabled')
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "00KobeBrian" <a@.b.com> wrote in message
> news:OOsLHBX0GHA.1256@.TK2MSFTNGP02.phx.gbl...
>> How can I know if the trigger is enabled or disabled? Thanks.
>>
>> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
>> news:%23WLdTeV0GHA.4448@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> Identify the triggers which needs to be disabled. Then use the below
>> Alter command to disable
>> 1. Create a SQL Agent job to disable the trigger and schedule a time
>> ALTER TABLE <Table_name> Disable Trigger <Trigger_ name>
>> 2. Create another SQL Agent job to enable the trigger and schedule a
>> time
>> ALTER TABLE <Table_name> Enable Trigger <Trigger_ name>
>> Make sure data integrity is not lost when you disable the trigger.
>> Thanks
>> Hari
>> SQL Server MVP
>> "00KobeBrian" <a@.b.com> wrote in message
>> news:e2UoIgU0GHA.4264@.TK2MSFTNGP05.phx.gbl...
>> How do you disable and enable the trigger on a table automatically at
>> some time daily?
>>
>>
>|||I gave you an example of checking for whether a trigger is disabled. If the
SELECT OBJECTPROPERTY returns 1, the trigger is disabled, if it returns 0,
the trigger is enabled, if it returns NULL, you typed something incorrectly.
If you need more details than that, you'll have to be more specific about
what you don't understand.
--
HTH
Kalen Delaney, SQL Server MVP
"00KobeBrian" <a@.b.com> wrote in message
news:O5vt5%23X0GHA.2072@.TK2MSFTNGP06.phx.gbl...
> Can you please give me an example? I am using SQL 2000. Thanks.
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:eKMpdLX0GHA.5100@.TK2MSFTNGP05.phx.gbl...
>> Hi 00KobeBrian
>> Use the function OBJECTPROPERTY
>> SELECT OBJECTPROPERTY(object_id('name of trigger')',
>> 'ExecIsTriggerDisabled')
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>>
>> "00KobeBrian" <a@.b.com> wrote in message
>> news:OOsLHBX0GHA.1256@.TK2MSFTNGP02.phx.gbl...
>> How can I know if the trigger is enabled or disabled? Thanks.
>>
>> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
>> news:%23WLdTeV0GHA.4448@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> Identify the triggers which needs to be disabled. Then use the below
>> Alter command to disable
>> 1. Create a SQL Agent job to disable the trigger and schedule a time
>> ALTER TABLE <Table_name> Disable Trigger <Trigger_ name>
>> 2. Create another SQL Agent job to enable the trigger and schedule a
>> time
>> ALTER TABLE <Table_name> Enable Trigger <Trigger_ name>
>> Make sure data integrity is not lost when you disable the trigger.
>> Thanks
>> Hari
>> SQL Server MVP
>> "00KobeBrian" <a@.b.com> wrote in message
>> news:e2UoIgU0GHA.4264@.TK2MSFTNGP05.phx.gbl...
>> How do you disable and enable the trigger on a table automatically at
>> some time daily?
>>
>>
>>
>
