SQL Server 2000 Triggers

• thaya2002

  Old Hand

  Points: 393

  More actions

  April 12, 2010 at 11:23 pm

  #217744

  hi,

  I am wondering that SQL server 2000 has any facility to disable or enable triggers.

  how can we look at disabled triggers in sql server2000?

  How can we find which trigger is attached to which table?

  when i ran following TSQL in appropriate data base,

  SELECT * FROM SYSOBJECTS WHERE TYPE='TR'

  i can see the trigger that i am after,but can't see which table it's attached to.

  when I right click the table/all tasks/manage triggers and then clicked drop down box to see triggers,i could able to see before,but i can't see now for some known triggers.why?

  any help is much appreciated.

  thanks in advance

  regards,

  thaya

• arun.sas

  SSChampion

  Points: 11831

  More actions

  April 16, 2010 at 11:07 pm

  #1153272

  --==To find out the all triggers in the DB==--

  SELECT

  OBJECT_NAME(parent_obj)TbName ,

  [name] TrName,

  (CASE WHEN (STATUS & 2048) = 2048 THEN 'Disabled' ELSE 'Enabled' END) Status

  FROM sysobjects

  WHERE xtype = 'TR'

  order by 1, 2

  --==To find out a selected trigger status in the DB==--

  use DB

  GO

  IF (OBJECTPROPERTY (object_id(<TrName>),'ExecIsTriggerDisabled') = 1)

  PRINT 'Trigger disabled'

  ELSE

  PRINT 'Trigger enabled'