Disable Trigger rules

I had created several Trigger rules in current database and it will be
active on 1st Nov,2005,
Can I disable them now ? and then make them active on that target date '
Thanks a lotThe only way to disable a trigger is to drop it (or comment out the
T-SQL code in it so it does nothing). If I were you I'd script them out
to a file (with SQLEM), drop them (thereby "disabling" them) and then
schedule the SQL script in the file containing all the trigger code to
run via SQLAgent on Nov 1st (thereby "re-enabling" them).
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Agnes wrote:

>I had created several Trigger rules in current database and it will be
>active on 1st Nov,2005,
>Can I disable them now ? and then make them active on that target date '
>Thanks a lot
>
>|||Thanks Mike, I try your method , script it out. (however, it seems I must sc
ript the table structure too.).Any Simple method, i can script trigger rule
only , and the drop them all '
Please be kind to provide some sample .. Thanks in advance. (Today is my 4th
day working about Trigger rule)
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> '?:%23B7WU132FHA.279
6@.tk2msftngp13.phx.gbl...
The only way to disable a trigger is to drop it (or comment out the T-SQL co
de in it so it does nothing). If I were you I'd script them out to a file (
with SQLEM), drop them (thereby "disabling" them) and then schedule the SQL
script in the file containing all the trigger code to run via SQLAgent on No
v 1st (thereby "re-enabling" them).
mike hodgson
blog: http://sqlnerd.blogspot.com
Agnes wrote:
I had created several Trigger rules in current database and it will be
active on 1st Nov,2005,
Can I disable them now ? and then make them active on that target date '
Thanks a lot|||Hey you have soemthing called
ALTER TABLE ENABLE/DISABLE TRIGGER Read in BOL
If you want to do it for all tables, use
sp_Msforeachtable "ALTER TABLE '?' DISABLE TRIGGER"
Regards
R.D
--Knowledge gets doubled when shared
"Agnes" wrote:

> Thanks Mike, I try your method , script it out. (however, it seems I must
script the table structure too.).Any Simple method, i can script trigger rul
e only , and the drop them all '
> Please be kind to provide some sample .. Thanks in advance. (Today is my 4
th day working about Trigger rule)
> "Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> '?:%23B7WU132FHA
.2796@.tk2msftngp13.phx.gbl...
> The only way to disable a trigger is to drop it (or comment out the T-SQL code i
n it so it does nothing). If I were you I'd script them out to a file (with SQLEM),
drop them (thereby "disabling" them) and then schedule the SQL script in the file c
ont
aining all the trigger code to run via SQLAgent on Nov 1st (thereby "re-enabling" them).[co
lor=darkred]
> --
> mike hodgson
> blog: http://sqlnerd.blogspot.com
>
> Agnes wrote:
> I had created several Trigger rules in current database and it will be
> active on 1st Nov,2005,
> Can I disable them now ? and then make them active on that target date '
> Thanks a lot
>
>[/color]|||> The only way to disable a trigger is to drop it (or comment out the
> T-SQL code in it so it does nothing).
This is simply not true. Disabling or enabling a trigger is done through the
ALTER TABLE statement.
- to disable a trigger:
ALTER TABLE <table>
DISABLE TRIGGER <trigger>
- to enable a trigger:
ALTER TABLE <table>
ENABLE TRIGGER <trigger>
More here:
http://msdn.microsoft.com/library/d...br />
3ied.asp
ML|||Well you learn something new every day (I don't claim to know
everything). Thanks for the tip - that one's filed away in permanent
cache now. You know, over 10 years working with MSSQL and I'd never
noticed that clause in the ALTER TABLE statement.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
R.D wrote:

>Hey you have soemthing called
>ALTER TABLE ENABLE/DISABLE TRIGGER Read in BOL
>If you want to do it for all tables, use
>sp_Msforeachtable "ALTER TABLE '?' DISABLE TRIGGER"
>
>|||I myself was reluctant to use these two methods at first since QA does not
recognize ENABLE and DISABLE as reserved keywords. :)
ML|||Maybe my brain's just not wired correctly, but wouldn't it make more
sense to have the ENABLED/DISABLED clause as part of the ALTER TRIGGER
statement rather than the ALTER TABLE statement? Something like "ALTER
TRIGGER MyTrig ON me.MyTable DISABLE"
*mike hodgson*
blog: http://sqlnerd.blogspot.com
ML wrote:

>I myself was reluctant to use these two methods at first since QA does not
>recognize ENABLE and DISABLE as reserved keywords. :)
>
>ML
>|||Quite the opposite! Wouldn't it make much more sense if triggers were added
to the objects:
alter table / view <name>
add trigger <trigger_name>
:)
After all - each trigger can only belong to a single table/view...
ML|||True, a trigger can only belong to a single table/view. But ALTER TABLE
is what you use to change the behaviour of an existing trigger (trigger
code, encryption, etc.), so it makes more sense to me that to change an
"attribute" of a trigger, which I would consider its "enabled/disabled"
status to be, you'd use ALTER TRIGGER.
Moot point anyway - purely a hypothetical, but I think the way they've
gone with T-SQL in SQL 2005 is better (with DISABLE TRIGGER being a DDL
statement all of its own).
*mike hodgson*
blog: http://sqlnerd.blogspot.com
ML wrote:

>Quite the opposite! Wouldn't it make much more sense if triggers were added
>to the objects:
>alter table / view <name>
> add trigger <trigger_name>
>:)
>After all - each trigger can only belong to a single table/view...
>
>ML
>