Thursday, March 20, 2008

view trigger in MS SQL

Try the following statements in Query Analyzer:
use ;
sp_helptext ;
This is used to see the trigger content.
select * from sysobjects where name=;
This is used to see the trigger properties.

Here's a list of trigger status code:
- a value of 1610615808 when an INSERT trigger is disabled
- a value of 1610615040 when a DELETE trigger is disabled
- a value of 1610615296 when an UPDATE trigger is disabled.

If you want to get the trigger name related to a table, you can use entreprise manager to check dependencies for that table.

In order to check if a trigger is disabled, you can use the following statement:
select name, objectproperty(id, 'ExecIsTriggerDisabled') as Disabled from sysobjects where xtype='tr';

In order to enable a trigger, use the following statement:
alter table enable trigger ;
or enable all triggers:
alter table enable trigger all;


Anju said...

i was dealing with trigger.created a trigger and i was confused on the created trigger..your post helped me in handling trigger..thank you very much.

Anonymous said...

I think, that is not present.