How to know if a trigger is enable ?

eldeleon · #1 (**permalink**) 09-07-11, 11:14

Hello

I have a problem in a sp that i am doing, because some times a trigger its running and its affecting my process, I want to know if there is a way with a code in my sp i can detect if the trigger its enable and then proceed to disable it.

I know that probably something its wrong because I start seeing and increment of looks, then I check with sp_help TRIGER and its enable.

Any help will be apprecieted.

Thank you

pradyut.dhara · #2 (**permalink**) 09-07-11, 12:49

Hi,
You can check the deltrig, instrig, updtrig, sysstat and sysstat2 columns in sysobjects table to see if a trigger is enabled or disabled.

eldeleon · #3 (**permalink**) 09-07-11, 14:11

Do you have an example of this?

Regards

pradyut.dhara · #4 (**permalink**) 09-08-11, 10:30

Hi,
Got the following information from another website. These values are not documented (at least i could not find

)
trigger status (enabled/disabled) based on sysstat2 column values for table
sysstat2 & 1048576 (0x100000) != 0 => insert trigger disabled
sysstat2 & 2097152 (0x200000) != 0 => delete trigger disabled
sysstat2 & 4194304 (0x400000) != 0 => update trigger disabled

It is also working in sybase 15.5 developer edition.

Code:

1> select * from sysobjects where (sysstat2 & 2097152)!= 0
2> go
 name                                                                                                                                                                                                                                                            id          uid         type userstat sysstat indexdel schemacnt sysstat2    crdate                     expdate                    deltrig     instrig     updtrig     seltrig     ckfirst     cache  audflags    objspare versionts                  loginame                       identburnmax                              spacestate erlchgts           sysstat3
 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ---- -------- ------- -------- --------- ----------- -------------------------- -------------------------- ----------- ----------- ----------- ----------- ----------- ------ ----------- -------- -------------------------- ------------------------------ ----------------------------------------- ---------- ------------------ --------
 titles                                                                                                                                                                                                                                                            768002736           1 U           0     115        2         4     2170880        Jun  8 2011  6:12PM        Jun  8 2011  6:12PM  1104003933           0           0           0           0      0           0        0 NULL                       NULL                                                                NULL       NULL NULL                      0

(1 row affected)
1> select name from sysobjects where id=1104003933
2> go
 name
 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 deltitle

(1 row affected)
1> sp_help deltitle
2> go
 Name     Owner Object_type Object_status Create_date
 -------- ----- ----------- ------------- -------------------
 deltitle dbo   trigger      -- none --   Jun  8 2011  6:12PM

(1 row affected)

Internet Services

Web Development

Digital Marketing

Consumer Tech