Sybase: Find all triggers on a table

In order to find all triggers on a table (select, delete, insert and update triggers), you can use the stored procedure sp_depends:

1> sp_depends tablename
2> go
Things inside the current database that reference the object.
 object                       type
 ---------------------------- ----------------
 dbo.sp1                      stored procedure
 dbo.sp2                      stored procedure
 dbo.sp3                      stored procedure
 dbo.my_del_trigger           trigger
 dbo.my_ins_trigger           trigger
 dbo.my_upt_trigger           trigger
 dbo.another_table_trg        trigger
...

There are two problems when using this procedure:

  • sp_depends relies on the sysdepends table and there were always problems with this table not being updated properly.
  • As you can see above it also shows you all triggers referencing this table even if it’s a trigger on another table.

A similar solution to this which also has the problem that it displays too many objects:

1> select distinct so.name from sysobjects so, syscomments sc where so.id=sc.id and so.type='TR' and sc.text like '%tablename%'
2> go

In order to reliably find only triggers on a given table, you have to check the sysobjects table:

1> select so2.name from sysobjects so1, sysobjects so2 where (so2.id = so1.deltrig or so2.id = so1.instrig or so2.id=so1.updtrig or so2.id=so1.seltrig) and so1.name='tablename'
2> go

This will return a list of the names of all triggers on the table.

Alternatively, you can also get the names of the triggers using the following statement (without the double join and with one column per trigger instead of one row per trigger):

1> select object_name(deltrig), object_name(instrig), object_name(updtrig), object_name(seltrig) from sysobjects where name='tablename'
2> go

If you need to also get the text of the triggers, you can use sp_helptext or use the method described here.

Note: There is a seltrig column in sysobjects but I’m not aware of a way to actually define such a trigger so it’s probably not used in ASE.

Leave a Reply

Your email address will not be published.