|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Disable Trigger rulesI 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 The 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). Show quoteHide quote >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 script 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.2***@tk2msftngp13.phx.gbl... The 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 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" -- Show quoteHide quoteRegards 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 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.2***@tk2msftngp13.phx.gbl... > The 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 > > > 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. Show quoteHide quote >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" Show quoteHide quote >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). Show quoteHide quote >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 > > > The only way to disable a trigger is to drop it (or comment out the This is simply not true. Disabling or enabling a trigger is done through the > T-SQL code in it so it does nothing). 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/default.asp?url=/library/en-us/tsqlref/ts_aa-az_3ied.asp ML  
Thread options
Other interesting topics
|
|||||||||||||||||||||||
