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
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.