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.

Vernavernacular

Answer 1

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

Vernavernacular

sp_helptrigger what could be easier? It’s “new” in Sybase ASE 16 – Mondrian

Sybase ASE 16.0: sp_depends didn't show me triggers, but sp_helptrigger did. sp_helptext did work to show the definition of the trigger once I had the name. – Wanderoo

Answer 2

select *
from sysobjects
where type = 'TR'

Taken from here.

Premillennial

Thanks Ray, I searched for along time yesterday but never found that article. It has a bounty of useful information in there. – Vernavernacular

I would have just added my answer clarification to yours but I can't edit an answer. I think because I didn't have community wiki checked when I created the question. – Vernavernacular

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%'

Involucrum

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

Warlord

Answer 5

Use

sp_helptrigger <tableName>

It’s “new” in SAP Sybase ASE 16

Mondrian

also worth noting that sp_depends mentioned in the accepted answer doesn't work in Sybase ASE 16.0 so this is the new correct way. – Wanderoo

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'

Kucik

Answer 7

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.

Jerryjerrybuild

Answer 8

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.

En

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

Whosoever

Answer 10

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%'

Continuation

Answer 11

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.

Salzman