How do you identify the triggers associated with a table in a sybase database?

Question

I am using SQL Advantage and need to know what the SQL is to identify the triggers associated with a table. I don't have the option to use another tool so the good old fashioned SQL solution is the ideal answer.

Answer 1

select *
from sysobjects
where type = 'TR'

Taken from here.

Answer 2

I also found out that

sp_depends <object_name> 

will show you a lot of information about a table, including all triggers associated with it. Using that, along with Ray's query can make it much easier to find the triggers. Combined with this query from Ray's linked article:

sp_helptext <trigger_name>

and you can see the definition of the trigger:

sp_depends <trigger_name>

will also show you all tables related to a trigger

Answer 3

to show triggers and create sql for a table:

select so.name, text
from sysobjects so, syscomments sc
where type = 'TR'
and so.id = sc.id
and text like '%TABLENAME%'

Answer 4

SELECT 
    T.name AS TableName
    ,O.name  TriggerName  
    FROM sysobjects O 
    INNER JOIN sys.tables T ON T.object_id = O.parent_obj
    WHERE O.type = 'TR' AND T.name IN ('tableNames')
ORDER BY TableName

Answer 5

I believe there is (or at least 'was') some issue where dependency information is not always accurate. Therefore I would attempt to approach it like this :

select name
from sysobjects
where xtype='TR'
and id in (select id from syscomments where text like '%MY-TABLE-NAME%')

Good luck.

PS-This is untested code, leave a comment if it doesn't work, and I'll fix it.

Answer 6

I would use following code, to make sure you're getting the right objects. Since Sybase 16 this won't be complete anymore, as there might be more triggers of the same type on one table.

    select tr.id, tr.name, tr.type, tr.crdate, tr.loginame
from sysobjects u
  join sysobjects tr on tr.id in (u.instrig, u.deltrig, u.updtrig, u.seltrig)
where u.name = 'TABLENAME'

Answer 7

1. Open Sybase Central and navigate to the triggers view.
2. Click on the "Object Name" column to sort.

The "Object Name" column apparently shows the table associated with the trigger. Scroll down to the table you're interested in.

Answer 8

I am using SQL Anywhere 16 and it's easy to find the triggers of a specific table. When you open the table, there is a tab named 'Triggers'. But the query to find all the triggers is a bit different from the answers above:

select * from SYS.SYSTRIGGERS --where trigdefn like '%exec%'

Answer 9

SybaseASE:

SELECT so.name, Type=(CASE so.type WHEN 'V' Then 'View' WHEN 'P' THEN 'Procedure' WHEN 'TR' THEN 'Trigger' ELSE so.type END)
    FROM sysobjects so, sysdepends d
    WHERE
        d.depid = object_id('MyTblName')
        AND so.id =d.id 
        /* Just triggers 
        AND so.type = 'TR'
        */
    ORDER BY so.type,so.name

Answer 10

Use

sp_helptrigger <tableName>

It’s “new” in SAP Sybase ASE 16

Answer 11

Running a search for the string trigger against the latest SQL Advantage Database Server (ADS) documentation I can find ... I get hits on two system views that appear to be synonyms for the same info:

• system.triggers
• system.ansi_triggers

From the system.triggers link:

Contains one row for each trigger in the database.

Field Name              Field Type      Field Size      Description

Name                    Character       200             Trigger name.
Trig_TableName          Character       200             The table the trigger is assigned to.
Trig_Event_Type         Integer         4               The type of event that causes a trigger to fire.
Trig_Trigger_Type       Integer         4               The kind of event the trigger should fire on.
Trig_Container_Type     Integer         4               The type of container holding the trigger.
Trig_Container          Memo            variable        The name of the trigger container. This value varies depending on the container type.
Trig_Function_Name      Character       260             The name of the function called when the trigger is executed.
Trig_Priority           Integer         4               Determines when the trigger is fired in relation to other triggers.
Trig_Options            Integer         4               Options for the trigger in numeric format.
Comment                 Memo            variable        The description of the trigger.

While I know my way around Sybase ASE, and to a lesser extent Sybase SQLAnywhere and Sybase IQ, Sybase ADS is greek to me; I assume a user familiar with ADS triggers will understand the signficance of the various column names (and datatypes); I'm also guessing said user can figure out, where necessary, the appropriate joins with other ADS system tables and ADS system views.