|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Can a trigger be inadvertantly disabled?
A customer reported some odd behavior, and it turned out to be because a
trigger was disabled. Access to the SQL Server is quite limited, and it seems very unlikely that anyone intentionally disabled this trigger. Is there any way---like if the system has a problem with the trigger or something---that a trigger can become disabled? Or is the only way through deliberate and specific human action? Also, is there a quick way to see whether any other triggers in a database are disabled? TIA, Joel It is possible in a SP execution, the code in the SP needs to disable some
table's trigger, does something, then enable the trigger back at the end of execution. Now, if the SP's execution did not finish for some reason, the disabled trigger would be left disabled. Show quoteHide quote "JoelB" <Jo***@discussions.microsoft.com> wrote in message news:B7DEB15C-F0FB-4121-997B-A493E390A3DD@microsoft.com... >A customer reported some odd behavior, and it turned out to be because a > trigger was disabled. Access to the SQL Server is quite limited, and it > seems very unlikely that anyone intentionally disabled this trigger. > > Is there any way---like if the system has a problem with the trigger or > something---that a trigger can become disabled? Or is the only way > through > deliberate and specific human action? > > Also, is there a quick way to see whether any other triggers in a database > are disabled? > > TIA, > Joel Norman,
I'm confident that that's not what took place in this instance, as we don't have any SPs that take advantage of DISABLE TRIGGER. Thanks for the information though---it's good to know. Joel Show quoteHide quote "Norman Yuan" wrote: > It is possible in a SP execution, the code in the SP needs to disable some > table's trigger, does something, then enable the trigger back at the end of > execution. Now, if the SP's execution did not finish for some reason, the > disabled trigger would be left disabled. > > > "JoelB" <Jo***@discussions.microsoft.com> wrote in message > news:B7DEB15C-F0FB-4121-997B-A493E390A3DD@microsoft.com... > >A customer reported some odd behavior, and it turned out to be because a > > trigger was disabled. Access to the SQL Server is quite limited, and it > > seems very unlikely that anyone intentionally disabled this trigger. > > > > Is there any way---like if the system has a problem with the trigger or > > something---that a trigger can become disabled? Or is the only way > > through > > deliberate and specific human action? > > > > Also, is there a quick way to see whether any other triggers in a database > > are disabled? > > > > TIA, > > Joel > > This will tell you all triggers that are disabled:
SELECT o.[name] AS [Trigger], OBJECT_NAME(o.parent_object_id) AS [Table] FROM sys.objects o WHERE o.[type] = 'TR' AND OBJECTPROPERTY(o.[object_id], 'ExecIsTriggerDisabled') = 0; HTH Vern Rabe Show quoteHide quote "JoelB" wrote: > A customer reported some odd behavior, and it turned out to be because a > trigger was disabled. Access to the SQL Server is quite limited, and it > seems very unlikely that anyone intentionally disabled this trigger. > > Is there any way---like if the system has a problem with the trigger or > something---that a trigger can become disabled? Or is the only way through > deliberate and specific human action? > > Also, is there a quick way to see whether any other triggers in a database > are disabled? > > TIA, > Joel JoelB (Jo***@discussions.microsoft.com) writes:
> A customer reported some odd behavior, and it turned out to be because a The only way for a trigger to be disabled is through the execution of> trigger was disabled. Access to the SQL Server is quite limited, and it > seems very unlikely that anyone intentionally disabled this trigger. > > Is there any way---like if the system has a problem with the trigger or > something---that a trigger can become disabled? Or is the only way > through deliberate and specific human action? ALTER TABLE DISABLE TRIGGER, one way or another. > Also, is there a quick way to see whether any other triggers in a database SELECT name AS "trigger", object_id(parent_id) AS "table"> are disabled? FROM sys.triggers WHERE is_disabled = 1 -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Yes, much simpler than my solution.
Vern Rabe Show quoteHide quote "Erland Sommarskog" wrote: > JoelB (Jo***@discussions.microsoft.com) writes: > > A customer reported some odd behavior, and it turned out to be because a > > trigger was disabled. Access to the SQL Server is quite limited, and it > > seems very unlikely that anyone intentionally disabled this trigger. > > > > Is there any way---like if the system has a problem with the trigger or > > something---that a trigger can become disabled? Or is the only way > > through deliberate and specific human action? > > The only way for a trigger to be disabled is through the execution of > ALTER TABLE DISABLE TRIGGER, one way or another. > > > Also, is there a quick way to see whether any other triggers in a database > > are disabled? > > SELECT name AS "trigger", object_id(parent_id) AS "table" > FROM sys.triggers > WHERE is_disabled = 1 > > > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Links for SQL Server Books Online: > SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx > SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > > Well thank you both---that was just what I needed! (although Vern, I used
yours before Erland's post, and had to change the zero to a one...after that it worked great). Thanks also Erland for the definitive answer on how they can be disabled. It was the only one that was disabled, and it remains a mystery as to how it happened. Thanks all for your help. Joel Show quoteHide quote "Vern Rabe" wrote: > Yes, much simpler than my solution. > > Vern Rabe > > "Erland Sommarskog" wrote: > > > JoelB (Jo***@discussions.microsoft.com) writes: > > > A customer reported some odd behavior, and it turned out to be because a > > > trigger was disabled. Access to the SQL Server is quite limited, and it > > > seems very unlikely that anyone intentionally disabled this trigger. > > > > > > Is there any way---like if the system has a problem with the trigger or > > > something---that a trigger can become disabled? Or is the only way > > > through deliberate and specific human action? > > > > The only way for a trigger to be disabled is through the execution of > > ALTER TABLE DISABLE TRIGGER, one way or another. > > > > > Also, is there a quick way to see whether any other triggers in a database > > > are disabled? > > > > SELECT name AS "trigger", object_id(parent_id) AS "table" > > FROM sys.triggers > > WHERE is_disabled = 1 > > > > > > -- > > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > > > Links for SQL Server Books Online: > > SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > > SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx > > SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > > > > By default triggers do not fire during a bcp or BULK INSERT.
Show quoteHide quote "JoelB" wrote: > Well thank you both---that was just what I needed! (although Vern, I used > yours before Erland's post, and had to change the zero to a one...after that > it worked great). > > Thanks also Erland for the definitive answer on how they can be disabled. > It was the only one that was disabled, and it remains a mystery as to how it > happened. > > Thanks all for your help. > > Joel > > "Vern Rabe" wrote: > > > Yes, much simpler than my solution. > > > > Vern Rabe > > > > "Erland Sommarskog" wrote: > > > > > JoelB (Jo***@discussions.microsoft.com) writes: > > > > A customer reported some odd behavior, and it turned out to be because a > > > > trigger was disabled. Access to the SQL Server is quite limited, and it > > > > seems very unlikely that anyone intentionally disabled this trigger. > > > > > > > > Is there any way---like if the system has a problem with the trigger or > > > > something---that a trigger can become disabled? Or is the only way > > > > through deliberate and specific human action? > > > > > > The only way for a trigger to be disabled is through the execution of > > > ALTER TABLE DISABLE TRIGGER, one way or another. > > > > > > > Also, is there a quick way to see whether any other triggers in a database > > > > are disabled? > > > > > > SELECT name AS "trigger", object_id(parent_id) AS "table" > > > FROM sys.triggers > > > WHERE is_disabled = 1 > > > > > > > > > -- > > > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > > > > > Links for SQL Server Books Online: > > > SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > > > SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx > > > SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > > > > > > Damien (Dam***@discussions.microsoft.com) writes:
> By default triggers do not fire during a bcp or BULK INSERT. But running BCP or BULK INSERT does not disable the trigger for everyone.-- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx but I think you meant "object_name", not "object_id"
Vern Rabe Show quoteHide quote "Erland Sommarskog" wrote: > JoelB (Jo***@discussions.microsoft.com) writes: > > A customer reported some odd behavior, and it turned out to be because a > > trigger was disabled. Access to the SQL Server is quite limited, and it > > seems very unlikely that anyone intentionally disabled this trigger. > > > > Is there any way---like if the system has a problem with the trigger or > > something---that a trigger can become disabled? Or is the only way > > through deliberate and specific human action? > > The only way for a trigger to be disabled is through the execution of > ALTER TABLE DISABLE TRIGGER, one way or another. > > > Also, is there a quick way to see whether any other triggers in a database > > are disabled? > > SELECT name AS "trigger", object_id(parent_id) AS "table" > FROM sys.triggers > WHERE is_disabled = 1 > > > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Links for SQL Server Books Online: > SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx > SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > >  
Other interesting topics
Error logs enormous. Can I delete or save elsewhere?
MS Access, ODBC, SQL 2005, delays before data appears Script to delete records from a table older than N number of days. Server Disk Space in full recovery mode but log keeps self-truncating SQL SErver 64 bit use of memory Security Question how to store decimals in tables The time stamp counter of CPU on scheduler id X is not synchronized with other CPUs Cumulative package 2 for SQL 2008 |
|||||||||||||||||||||||
