Disabling triggers with alter table, execute as?

I'm disabling some triggers in SQL Server 2000 with the following command
launched from another trigger:
ALTER TABLE PM00300 DISABLE TRIGGER insPM00300
I want normal users to be able to run that command. Is there a kind of
"execute as" I can use in SQL Server 2000 to do it?
Thanks.Before I answer: are you sure you want to do this? The entire point of a
trigger is to fire regardless of user control. Be very careful here. I'm
going to lay out an option in 2000, but I don't endorse it's usage (I'm
presenting it simply because I don't know exactly what you're business goal
is - it may be perfectly valid).
Technically you could create a job that disables the trigger, then give
users access to the job and let them call it from within a stored procedure
or such. Be sure not to create a schedule for the job. With a SQL Agent job,
you can control (somewhat) the execution authority.
"b3nny80y" <b3nny80y@.discussions.microsoft.com> wrote in message
news:FF0D789D-3DE5-4354-B5A2-0AAE27CAFFB7@.microsoft.com...
> I'm disabling some triggers in SQL Server 2000 with the following command
> launched from another trigger:
> ALTER TABLE PM00300 DISABLE TRIGGER insPM00300
> I want normal users to be able to run that command. Is there a kind of
> "execute as" I can use in SQL Server 2000 to do it?
> Thanks.|||Is that wise?
For the user to execute an ALTER table, they will have to be provided table
ownership privileges. That means that the users can alter the table anyway
they desire -DROP columns, ADD columns, DROP TABLE, etc.
If you are trying to stop nested Triggers, there is another way. Use
sp_configure to turn on/off the Using Nested Triggers configuration option.
(Or use Server properties in Enterprise Manager.)
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"b3nny80y" <b3nny80y@.discussions.microsoft.com> wrote in message
news:FF0D789D-3DE5-4354-B5A2-0AAE27CAFFB7@.microsoft.com...
> I'm disabling some triggers in SQL Server 2000 with the following command
> launched from another trigger:
> ALTER TABLE PM00300 DISABLE TRIGGER insPM00300
> I want normal users to be able to run that command. Is there a kind of
> "execute as" I can use in SQL Server 2000 to do it?
> Thanks.|||b3nny80y wrote:
> I'm disabling some triggers in SQL Server 2000 with the following command
> launched from another trigger:
> ALTER TABLE PM00300 DISABLE TRIGGER insPM00300
> I want normal users to be able to run that command. Is there a kind of
> "execute as" I can use in SQL Server 2000 to do it?
> Thanks.
Why would you want to do this? Please explain further what you're
trying to accomplish. If you're trying to disable the triggers for
specific users, you can accomplish the same effect by putting IF/ELSE
code in the trigger that checks the current username, and simply doesn't
execute the code in the trigger.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Disabling triggers with alter table, execute as?

I'm disabling some triggers in SQL Server 2000 with the following command
launched from another trigger:
ALTER TABLE PM00300 DISABLE TRIGGER insPM00300
I want normal users to be able to run that command. Is there a kind of
"execute as" I can use in SQL Server 2000 to do it?
Thanks.Before I answer: are you sure you want to do this? The entire point of a
trigger is to fire regardless of user control. Be very careful here. I'm
going to lay out an option in 2000, but I don't endorse it's usage (I'm
presenting it simply because I don't know exactly what you're business goal
is - it may be perfectly valid).
Technically you could create a job that disables the trigger, then give
users access to the job and let them call it from within a stored procedure
or such. Be sure not to create a schedule for the job. With a SQL Agent job,
you can control (somewhat) the execution authority.
"b3nny80y" <b3nny80y@.discussions.microsoft.com> wrote in message
news:FF0D789D-3DE5-4354-B5A2-0AAE27CAFFB7@.microsoft.com...
> I'm disabling some triggers in SQL Server 2000 with the following command
> launched from another trigger:
> ALTER TABLE PM00300 DISABLE TRIGGER insPM00300
> I want normal users to be able to run that command. Is there a kind of
> "execute as" I can use in SQL Server 2000 to do it?
> Thanks.|||Is that wise?
For the user to execute an ALTER table, they will have to be provided table
ownership privileges. That means that the users can alter the table anyway
they desire -DROP columns, ADD columns, DROP TABLE, etc.
If you are trying to stop nested Triggers, there is another way. Use
sp_configure to turn on/off the Using Nested Triggers configuration option.
(Or use Server properties in Enterprise Manager.)
--
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"b3nny80y" <b3nny80y@.discussions.microsoft.com> wrote in message
news:FF0D789D-3DE5-4354-B5A2-0AAE27CAFFB7@.microsoft.com...
> I'm disabling some triggers in SQL Server 2000 with the following command
> launched from another trigger:
> ALTER TABLE PM00300 DISABLE TRIGGER insPM00300
> I want normal users to be able to run that command. Is there a kind of
> "execute as" I can use in SQL Server 2000 to do it?
> Thanks.|||b3nny80y wrote:
> I'm disabling some triggers in SQL Server 2000 with the following command
> launched from another trigger:
> ALTER TABLE PM00300 DISABLE TRIGGER insPM00300
> I want normal users to be able to run that command. Is there a kind of
> "execute as" I can use in SQL Server 2000 to do it?
> Thanks.
Why would you want to do this? Please explain further what you're
trying to accomplish. If you're trying to disable the triggers for
specific users, you can accomplish the same effect by putting IF/ELSE
code in the trigger that checks the current username, and simply doesn't
execute the code in the trigger.
--
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Disabling triggers not involved in replication in SQL 2000

I am experimenting with replication and recently set up a prototype
transactional replication with about a dozen publications and perhaps twice
as many articles. All has gone smoothly so far. One item that is causing
much gnashing of teeth is the fact that I cannot disable a pre-existing
trigger that has nothing to do with the replication.
Msg 4929, Level 16, State 2, Line 1
Cannot alter the table 'XXXXX' because it is being published for replication.
Any help greatly appreciated.
Craig
Craig,
you won't be able to do this sort of statement: "alter table suppliers
disable trigger all" but you can issue an alter trigger statement. If there
are only a few triggers this might be practical for you.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Disabling triggers from stored procedures

Hi,
Is it possible to disable a trigger from a Stored Procedure? If it is, how do you do it?
Thanks,
FedericoALTER TABLE [MyTable] DISABLE TRIGGER trMyTrigger|||Just curious as to why you would want to do this?|||Yes, it is possible to disable the trigger, but keep in mind that you are disabling that trigger for everyone using the table, not just for what the trigger is doing. I don't know of any way to allow a trigger to function for some users/spids and not for others, except to include code within the trigger to make it decide that it shouldn't run under some conditions.

-PatP|||If SQL Server 2000 and one can alter the trigger then one can use SET CONTEXT_INFO in SP and then check master..sysprocesses from trigger.

Might also insert DBCC INPUTBUFFER(@.@.SPID) results into temporary table in trigger to check for proc name in EventInfo column. Would work in SQL Server 7 and would not require any additional code in SP, but would hurt trigger performance, be kludgy, and require hard-coding proc name(s) in trigger.

Disabling triggers

Hi,
Does ALTER TABLE ... DISABLE TRIGGER ALL guarantee that disabled triggers
won't fire under any circumstances?
MS SQL Server 2000 SP3.
-- Many thanks.Yes, did work for me all the time.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--

Disabling triggers

Hi,
Does ALTER TABLE ... DISABLE TRIGGER ALL guarantee that disabled triggers
won't fire under any circumstances?
MS SQL Server 2000 SP3.
-- Many thanks.Yes, did work for me all the time.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--

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 updates via triggers - how?

Hi,
We use push transactional replication successfully and are about to bring a
few remote on-sites into the fold. The connection speed will be low - in the
64k/128k range. As I understand it "Not for replication" means that a trigger
on the subcriber will not fire if the update is generated via a replication
process, i.e. all updates are pushed from the publisher. If this is correct
is there any way to reverse the logic, i.e. don't replicate updates on the
publisher that are caused by triggers and assume the subscriber will have the
appropriate triggers in place to do the update.
We are interested in doing this to reduce the amount of network bandwidth
consummed be replication (this will be critical when the remote sites come
on-line).
Can this be done?
Cheers, Peter
The not for replication switch means that replication related activity
will not cause the trigger, constraint, identity property to be enforced
if the triggering activity occurs by a replication process. This could
happen on the publisher or subscriber.
There is a way to bypass the replication process. What you do is add
filters to your tables which look like this
select * from published tables where 1=1.
A filter proc will be created which the log reader agent uses when
figuring out what commands are to be written to the distribution agent.
Change this filter to always return 0 when you want the commands to be
ignored. Change it back to 1 when you want the commands to be processed.
The problem is you can't alter procs via a trigger.
The way I handle things like this is to build the logic into the
replication proc that applies these transactions on the subscriber(s).
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
|||Hi Hillary,
Thanks for the response.
Where do I go to add the filter: "select * from published tables where 1=1."?
When and where would I change the filter?
I do have a copy of your fine book - what sections are relevent to this
particular issue.
I presume the following isn't relevent to my circumstance as what I'm trying
to do reduce network traffice. Or I have I misunderstood?

> The way I handle things like this is to build the logic into the
> replication proc that applies these transactions on the subscriber(s).
Cheers, Peter
"Hilary Cotter" wrote:

> The not for replication switch means that replication related activity
> will not cause the trigger, constraint, identity property to be enforced
> if the triggering activity occurs by a replication process. This could
> happen on the publisher or subscriber.
> There is a way to bypass the replication process. What you do is add
> filters to your tables which look like this
>
> A filter proc will be created which the log reader agent uses when
> figuring out what commands are to be written to the distribution agent.
> Change this filter to always return 0 when you want the commands to be
> ignored. Change it back to 1 when you want the commands to be processed.
> The problem is you can't alter procs via a trigger.
> The way I handle things like this is to build the logic into the
> replication proc that applies these transactions on the subscriber(s).
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
|||You are correct, making these change to the replication procs used to
synchronize your data will not minimize network traffic. Replicating the
execution of stored procedures will, if the majority of your transactions
occurring on the publisher affect more than one row.
Have a look at this post for an example of how to make the log reader agent
bypass transactions.
http://groups-beta.google.com/group/...c?dmode=source
"Peter Jones" <PeterJones@.discussions.microsoft.com> wrote in message
news:87E55BFF-BCB2-4C20-B5F5-9C48B3B8064D@.microsoft.com...[vbcol=seagreen]
> Hi Hillary,
> Thanks for the response.
> Where do I go to add the filter: "select * from published tables where
> 1=1."?
> When and where would I change the filter?
> I do have a copy of your fine book - what sections are relevent to this
> particular issue.
> I presume the following isn't relevent to my circumstance as what I'm
> trying
> to do reduce network traffice. Or I have I misunderstood?
>
> Cheers, Peter
>
> "Hilary Cotter" wrote:

Disable triggers only for active sessions

Hey,

is it possible to disable all DML triggers for the current session only?
This is for manual data correction sessions where no trigger should be fired but all other user connection still should have active triggers.

Cheers,I don't think you can do this without modifying your trigger. You can use SUSER_NAME() function inside your trigger and if it matches with the login user bypass the execution.

to be specific.

The first line of trigger code will be if SUSER_NAME() = 'Manuel' return|||I can second that, you will have to implement some additional logic to make this possible.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Disable triggers for a single update

Hello,
I have a trigger on a table (after update) which keeps track of modified
records / columns. I need to have a function wich can make updates to that
table without firing the trigger (i.e. I don't want to track changes made by
that function).
So, I need is to make the following statements "atomic" (otherwise I could
miss other updates I want to keep tracking)
- alter table disable trigger
- update table
- alter table enable trigger.
The first option I considered was using a lock on the whole table, which i'd
like to avoid due to the usual performance issues. Now I'm thinking about
using a transaction. I know that transactions ensure actions are atomic with
respecto to recovery, I'd like to know if they can solve this kind of issues
too.
Thank you,
Luca> The first option I considered was using a lock on the whole table, which
> i'd like to avoid due to the usual performance issues. Now I'm thinking
> about using a transaction. I know that transactions ensure actions are
> atomic with respecto to recovery, I'd like to know if they can solve this
> kind of issues too.
Wrapping the ALTER/UPDATE/ALTER in a transaction will ensure the operation
is atomic. However, the ALTER will acquire a Sch-M lock on the table so no
other users can access the table for the duration of the transaction.
If this is the sort of thing you do often, consider including code in your
trigger to conditionally bypass the update. This will improve concurrency
and provide an atomic transaction as well. The example below uses a temp
table existence check:
CREATE TRIGGER TR_MyTable
ON MyTable FOR UPDATE
AS
IF OBJECT_ID('tempdb..#BypassTrigger') IS NULL
BEGIN
UPDATE MyTable
SET MyAuditDate = CURRENT_TIMESTAMP
WHERE EXISTS
(
SELECT *
FROM inserted
WHERE MyTable.MyPK = inserted.MyPK
)
END
GO
You can then skip the trigger code for the current session by creating the
dummy temp table beforehand:
CREATE TABLE #BypassTrigger(Col1 int)
UPDATE MyTable
SET MyData = 'test'
WHERE MyPk = 1
DROP TABLE #BypassTrigger
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Luca" <luca@.none.net> wrote in message
news:Z3WXd.639929$b5.29135411@.news3.tin.it...
> Hello,
> I have a trigger on a table (after update) which keeps track of modified
> records / columns. I need to have a function wich can make updates to that
> table without firing the trigger (i.e. I don't want to track changes made
> by that function).
> So, I need is to make the following statements "atomic" (otherwise I could
> miss other updates I want to keep tracking)
> - alter table disable trigger
> - update table
> - alter table enable trigger.
> The first option I considered was using a lock on the whole table, which
> i'd like to avoid due to the usual performance issues. Now I'm thinking
> about using a transaction. I know that transactions ensure actions are
> atomic with respecto to recovery, I'd like to know if they can solve this
> kind of issues too.
> Thank you,
> Luca
>|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> ha scritto nel messaggio
news:eqTc$AXJFHA.2764@.tk2msftngp13.phx.gbl...
> --
> Hope this helps.
>
This was really helpful, thanks a lot.
Luca

Disable Triggers (using alter with public)

Hey folks,

Here's the skinny
I'm trying to run a stored procedure that requires triggers to be disabled in a table.

When I run the stored procedure (from the app or from the query analyzer) it works fine cause I'm setup as the owner/admin.
However, when users call the stored procedure from the application it gets executed as public which does not have alter persmissions.
I do not want to give alter permissions to public (for obvious reasons).

I was thinking of using SETUSER and SETOWNER but I don't know if it will work (I think because I cannot upgrade permissions using these just change to user of equal or lesser permissions).

Someone had mentioned to me to create a sysadmin user and just SETUSER to that then set it back to public, but again not sure if this will work because public has less permissions than the sysadmin.

I would like to have more info before I try this out and give it back to the client for testing.
Not sure if any of this will work for me, please help.
Thank you for your time.

By the way, I'm using SQL2000My suggestion would be to code an exemption for that stored procedure into your trigger, something like:IF 'myProc' = Object_Name(@.@.procid)
RETURN
-PatP|||Perfect, Exactly what I was looking for.
Thanks a million.

disable triggers

Hi,
How do I foind out whether the triggers in a sql table is enabled or not?
THanksAre you using SQL Server? What version? In SQL Server 2005 you can look at
the is_disabled column in sys.triggers
...
"mecn" <mecn2002@.yahoo.com> wrote in message
news:ud17l6t9HHA.5164@.TK2MSFTNGP05.phx.gbl...
> Hi,
> How do I foind out whether the triggers in a sql table is enabled or not?
> THanks
>|||Check out the metadata function OBJECTPROPERTY in SQL Server Books Online.
You can use the ExecIsTriggerDisabled for the property argument to find if a
trigger is enabled or not.
--
Anith|||I believe this is gonna work for you.
USE <your_database_name>
SELECT * FROM sys.triggers WHERE is_disabled = 1
--
Ekrem Önsoy
"mecn" <mecn2002@.yahoo.com> wrote in message
news:ud17l6t9HHA.5164@.TK2MSFTNGP05.phx.gbl...
> Hi,
> How do I foind out whether the triggers in a sql table is enabled or not?
> THanks
>

disable and enable triggers

hi,
I have 500-1000 records daily need to be updated in a heavy production
table. I have to disable the all triggers update records, enable all
triggers again.
My question is ...
1. Is it practicale in prod table?
2. Is table doing reindexing everytime I enable the triggers in that table.
3.What is the best way to update the records it if above is bad.
Thanks
mecn wrote:
> hi,
> I have 500-1000 records daily need to be updated in a heavy production
> table. I have to disable the all triggers update records, enable all
> triggers again.
> My question is ...
> 1. Is it practicale in prod table?
> 2. Is table doing reindexing everytime I enable the triggers in that table.
> 3.What is the best way to update the records it if above is bad.
> Thanks
>
>
Why do you need to disable the triggers to do this update?
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Yes,
I don't want to fire the triggers when I updating records
on that table
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45AE8D0F.5040007@.realsqlguy.com...
> mecn wrote:
> Why do you need to disable the triggers to do this update?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||http://msdn2.microsoft.com/en-us/library/ms189748.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"mecn" <mecn2002@.yahoo.com> wrote in message
news:OK8CXunOHHA.2468@.TK2MSFTNGP06.phx.gbl...
> Yes,
> I don't want to fire the triggers when I updating records
> on that table
>
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:45AE8D0F.5040007@.realsqlguy.com...
>
|||mecn,
Then why are the triggers there in the first place? If there is a business
rule as to which kinds of rows should fire the trigger then you might be
able to incorporate that into the trigger itself.
-- Bill
"mecn" <mecn2002@.yahoo.com> wrote in message
news:OK8CXunOHHA.2468@.TK2MSFTNGP06.phx.gbl...
> Yes,
> I don't want to fire the triggers when I updating records
> on that table
>
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:45AE8D0F.5040007@.realsqlguy.com...
>

disable and enable triggers

hi,
I have 500-1000 records daily need to be updated in a heavy production
table. I have to disable the all triggers update records, enable all
triggers again.
My question is ...
1. Is it practicale in prod table?
2. Is table doing reindexing everytime I enable the triggers in that table.
3.What is the best way to update the records it if above is bad.
Thanksmecn wrote:
> hi,
> I have 500-1000 records daily need to be updated in a heavy production
> table. I have to disable the all triggers update records, enable all
> triggers again.
> My question is ...
> 1. Is it practicale in prod table?
> 2. Is table doing reindexing everytime I enable the triggers in that table
.
> 3.What is the best way to update the records it if above is bad.
> Thanks
>
>
Why do you need to disable the triggers to do this update?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Yes,
I don't want to fire the triggers when I updating records
on that table
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45AE8D0F.5040007@.realsqlguy.com...
> mecn wrote:
> Why do you need to disable the triggers to do this update?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||http://msdn2.microsoft.com/en-us/library/ms189748.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"mecn" <mecn2002@.yahoo.com> wrote in message
news:OK8CXunOHHA.2468@.TK2MSFTNGP06.phx.gbl...
> Yes,
> I don't want to fire the triggers when I updating records
> on that table
>
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:45AE8D0F.5040007@.realsqlguy.com...
>|||mecn,
Then why are the triggers there in the first place? If there is a business
rule as to which kinds of rows should fire the trigger then you might be
able to incorporate that into the trigger itself.
-- Bill
"mecn" <mecn2002@.yahoo.com> wrote in message
news:OK8CXunOHHA.2468@.TK2MSFTNGP06.phx.gbl...
> Yes,
> I don't want to fire the triggers when I updating records
> on that table
>
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:45AE8D0F.5040007@.realsqlguy.com...
>

disable and enable triggers

hi,
I have 500-1000 records daily need to be updated in a heavy production
table. I have to disable the all triggers update records, enable all
triggers again.
My question is ...
1. Is it practicale in prod table?
2. Is table doing reindexing everytime I enable the triggers in that table.
3.What is the best way to update the records it if above is bad.
Thanksmecn wrote:
> hi,
> I have 500-1000 records daily need to be updated in a heavy production
> table. I have to disable the all triggers update records, enable all
> triggers again.
> My question is ...
> 1. Is it practicale in prod table?
> 2. Is table doing reindexing everytime I enable the triggers in that table.
> 3.What is the best way to update the records it if above is bad.
> Thanks
>
>
Why do you need to disable the triggers to do this update?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Yes,
I don't want to fire the triggers when I updating records
on that table
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45AE8D0F.5040007@.realsqlguy.com...
> mecn wrote:
>> hi,
>> I have 500-1000 records daily need to be updated in a heavy production
>> table. I have to disable the all triggers update records, enable all
>> triggers again.
>> My question is ...
>> 1. Is it practicale in prod table?
>> 2. Is table doing reindexing everytime I enable the triggers in that
>> table.
>> 3.What is the best way to update the records it if above is bad.
>> Thanks
>>
> Why do you need to disable the triggers to do this update?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||http://msdn2.microsoft.com/en-us/library/ms189748.aspx
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"mecn" <mecn2002@.yahoo.com> wrote in message
news:OK8CXunOHHA.2468@.TK2MSFTNGP06.phx.gbl...
> Yes,
> I don't want to fire the triggers when I updating records
> on that table
>
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:45AE8D0F.5040007@.realsqlguy.com...
>> mecn wrote:
>> hi,
>> I have 500-1000 records daily need to be updated in a heavy production
>> table. I have to disable the all triggers update records, enable all
>> triggers again.
>> My question is ...
>> 1. Is it practicale in prod table?
>> 2. Is table doing reindexing everytime I enable the triggers in that
>> table.
>> 3.What is the best way to update the records it if above is bad.
>> Thanks
>>
>>
>> Why do you need to disable the triggers to do this update?
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com
>|||mecn,
Then why are the triggers there in the first place? If there is a business
rule as to which kinds of rows should fire the trigger then you might be
able to incorporate that into the trigger itself.
-- Bill
"mecn" <mecn2002@.yahoo.com> wrote in message
news:OK8CXunOHHA.2468@.TK2MSFTNGP06.phx.gbl...
> Yes,
> I don't want to fire the triggers when I updating records
> on that table
>
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:45AE8D0F.5040007@.realsqlguy.com...
>> mecn wrote:
>> hi,
>> I have 500-1000 records daily need to be updated in a heavy production
>> table. I have to disable the all triggers update records, enable all
>> triggers again.
>> My question is ...
>> 1. Is it practicale in prod table?
>> 2. Is table doing reindexing everytime I enable the triggers in that
>> table.
>> 3.What is the best way to update the records it if above is bad.
>> Thanks
>>
>>
>> Why do you need to disable the triggers to do this update?
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com
>

disable all triggers

I have a startup stored procedure that dumps data from most tables and
repopulates some startup information. I'd like to disable all the triggers
at the start of this procedure and re-enable them at the end. Is there a
simple way to do this other than one disable line per trigger?
Thanks,
KeithIs there any danger in doing this? Is there a better way?
DECLARE
@.sTriggerName AS VARCHAR(200),
@.sTriggersTable AS VARCHAR(200),
@.sSQL AS VARCHAR(1000)
DECLARE curTriggers INSENSITIVE CURSOR FOR SELECT sysobjects.name AS
TriggerName, sysobjects_1.name AS TriggersTable FROM sysobjects INNER JOIN
sysobjects sysobjects_1 ON sysobjects.parent_obj = sysobjects_1.id WHERE
(OBJECTPROPERTY(sysobjects.id, N'IsTrigger') = 1)
OPEN curTriggers
FETCH NEXT FROM curTriggers INTO @.sTriggerName, @.sTriggersTable
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.sSQL = 'ALTER TABLE [dbo].' + @.sTriggersTable + ' DISABLE TRIGGER ' +
@.sTriggerName
EXEC(@.sSQL)
FETCH NEXT FROM curTriggers INTO @.sTriggerName, @.sTriggersTable
END
Keith|||you may want to try this
sp_msforeachtable 'Alter table ? disable trigger all'
"Keith G Hicks" wrote:

> Is there any danger in doing this? Is there a better way?
> DECLARE
> @.sTriggerName AS VARCHAR(200),
> @.sTriggersTable AS VARCHAR(200),
> @.sSQL AS VARCHAR(1000)
> DECLARE curTriggers INSENSITIVE CURSOR FOR SELECT sysobjects.name AS
> TriggerName, sysobjects_1.name AS TriggersTable FROM sysobjects INNER JOIN
> sysobjects sysobjects_1 ON sysobjects.parent_obj = sysobjects_1.id WHERE
> (OBJECTPROPERTY(sysobjects.id, N'IsTrigger') = 1)
> OPEN curTriggers
> FETCH NEXT FROM curTriggers INTO @.sTriggerName, @.sTriggersTable
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.sSQL = 'ALTER TABLE [dbo].' + @.sTriggersTable + ' DISABLE TRIGGER ' +
> @.sTriggerName
> EXEC(@.sSQL)
> FETCH NEXT FROM curTriggers INTO @.sTriggerName, @.sTriggersTable
> END
>
> Keith
>
>|||I can't find sp_msforeachtable in BOL for SQL 2000. I take it it's
undocumented. Anyway, I decided I'd rather only disable triggers that are
currently enabled so as not to enable any that should not be. I'm doing this
first:
CREATE TABLE #EnabledTriggerList (TriggerName VARCHAR(200), TriggerTable
VARCHAR(200))
DECLARE
@.sTriggerName AS VARCHAR(200),
@.sTriggersTable AS VARCHAR(200),
@.sSQL AS VARCHAR(1000)
DECLARE curTriggers INSENSITIVE CURSOR FOR
SELECT name AS TriggerName, OBJECT_NAME(parent_obj) AS TriggerTable
FROM sysobjects
WHERE xtype = 'TR'
AND OBJECTPROPERTY(id,'ExecIsTriggerDisabled
') = 0
OPEN curTriggers
FETCH NEXT FROM curTriggers INTO @.sTriggerName, @.sTriggersTable
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.sSQL = 'ALTER TABLE [dbo].' + @.sTriggersTable + ' DISABLE TRIGGER ' +
@.sTriggerName
EXEC(@.sSQL)
INSERT INTO #EnabledTriggerList (TriggerName, TriggerTable) VALUES
(@.sTriggersTable, @.sTriggerName)
FETCH NEXT FROM curTriggers INTO @.sTriggerName, @.sTriggersTable
END
CLOSE curTriggers
DEALLOCATE curTriggers
-- Then runnning all my table dump and repop code
-- then this
DECLARE curTriggers INSENSITIVE CURSOR FOR SELECT * FROM #EnabledTriggerList
OPEN curTriggers
FETCH NEXT FROM curTriggers INTO @.sTriggerName, @.sTriggersTable
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.sSQL = 'ALTER TABLE [dbo].' + @.sTriggersTable + ' ENABLE TRIGGER ' +
@.sTriggerName
EXEC(@.sSQL)
FETCH NEXT FROM curTriggers INTO @.sTriggerName, @.sTriggersTable
END
CLOSE curTriggers
DEALLOCATE curTriggers
It seems to work fine. Should this be safe?
Thanks,
Keith|||That's the way to do it. sp_msforeach* are undoc/supported and they use
cursor internally anyway.
-oj
"Keith G Hicks" <krh@.comcast.net> wrote in message
news:OvoiKjmQGHA.5808@.TK2MSFTNGP12.phx.gbl...
>I can't find sp_msforeachtable in BOL for SQL 2000. I take it it's
> undocumented. Anyway, I decided I'd rather only disable triggers that are
> currently enabled so as not to enable any that should not be. I'm doing
> this
> first:
> CREATE TABLE #EnabledTriggerList (TriggerName VARCHAR(200), TriggerTable
> VARCHAR(200))
> DECLARE
> @.sTriggerName AS VARCHAR(200),
> @.sTriggersTable AS VARCHAR(200),
> @.sSQL AS VARCHAR(1000)
> DECLARE curTriggers INSENSITIVE CURSOR FOR
> SELECT name AS TriggerName, OBJECT_NAME(parent_obj) AS TriggerTable
> FROM sysobjects
> WHERE xtype = 'TR'
> AND OBJECTPROPERTY(id,'ExecIsTriggerDisabled
') = 0
> OPEN curTriggers
> FETCH NEXT FROM curTriggers INTO @.sTriggerName, @.sTriggersTable
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.sSQL = 'ALTER TABLE [dbo].' + @.sTriggersTable + ' DISABLE TRIGGER ' +
> @.sTriggerName
> EXEC(@.sSQL)
> INSERT INTO #EnabledTriggerList (TriggerName, TriggerTable) VALUES
> (@.sTriggersTable, @.sTriggerName)
> FETCH NEXT FROM curTriggers INTO @.sTriggerName, @.sTriggersTable
> END
> CLOSE curTriggers
> DEALLOCATE curTriggers
> -- Then runnning all my table dump and repop code
> -- then this
> DECLARE curTriggers INSENSITIVE CURSOR FOR SELECT * FROM
> #EnabledTriggerList
> OPEN curTriggers
> FETCH NEXT FROM curTriggers INTO @.sTriggerName, @.sTriggersTable
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.sSQL = 'ALTER TABLE [dbo].' + @.sTriggersTable + ' ENABLE TRIGGER ' +
> @.sTriggerName
> EXEC(@.sSQL)
> FETCH NEXT FROM curTriggers INTO @.sTriggerName, @.sTriggersTable
> END
> CLOSE curTriggers
> DEALLOCATE curTriggers
> It seems to work fine. Should this be safe?
> Thanks,
> Keith
>