-1

I have a table with multiple triggers defined in different schemas. I want to know the enabled status of each trigger. Example, I created a table in s1 and created 2 triggers in s1 and s2 with same names. 

Create table s1.sometable (---);

create trigger s1.xyz on s1.sometable as -- trigger body--;

create trigger s2.xyz on s1.sometable as -- trigger body--;

alter table s1.sometable disable trigger s2.xyz ;

using sp_helptrigger does not help here since it does not give schemas and triggers in both schemas have same names. Is there any way to find the status of the trigger given its schema and name?

Saurabh Garg
  • 39
  • 11

1 Answers1

1

As I mentioned in reply to your other question about How to find triggers along with schemas in ASE 16.0, take a look at the source code for sp_helptrigger.

From the source code for sp_helptrigger we can see:

-- status for sysobjects.instrig
sysobjects.systat2 & 1048576 != 0 => disabled
sysobjects.systat2 & 1048576  = 0 => enabled

-- status for sysobjects.deltrig
sysobjects.systat2 & 2097152 != 0 => disabled
sysobjects.systat2 & 2097152  = 0 => enabled

-- status for sysobjects.updtrig
sysobjects.systat2 & 4194304 != 0 => disabled
sysobjects.systat2 & 4194304  = 0 => enabled

-- status for triggers stored in syscontraints
syscontraints.status &  4096 != 0 => disabled
syscontraints.status &  4096  = 0 => enabled
markp-fuso
  • 28,790
  • 4
  • 16
  • 36