Get Information About Triggers

As database objects, triggers are listed in sysobjects by name. The type column of sysobjects identifies triggers with the abbreviation “TR”.

This query finds the triggers that exist in a database:

select * 
from sysobjects 
where type = "TR"

In versions of SAP ASE earlier than 16.0, sysobjects saved the following:

deltrig column for delete triggers
instrig column for insert triggers
updtrig column for update triggers

In SAP ASE 16.0, the first trigger created on a table for delete, insert, and update operations, where the trigger is created without the order clause (or order 0) is associated with the table in one of the above columns in sysobjects.

The second and subsequent triggers created for a given action are associated with the table through a row in syscontraints. In addition, any trigger with order 1 or greater always uses sysconstraints for the table/trigger association.

The sysobjects row for the dependent table uses bits in the sysstat2 field to indicate that a trigger is disabled. In versions earlier than SAP ASE 16.0, there could be no more than one insert, delete, or upgrade trigger, which used these three bits:

disable_instrig 0x001000000
disable_deltrig 0x002000000
disable_updtrig 0x004000000

These bits exist in SAP ASE 16.0 and are used when a table's trigger ID is stored in sysobjects, as described above.

The @@trigger_name global variable returns the name of the trigger that is currently executing.

You can place the following in the body of a trigger or in the body of a stored procedure that is called (at any level of nesting) from a trigger:

select @@trigger_name

If nested triggers are fired, @@trigger_name holds the name of the most recently fired trigger.

The source text for each trigger is stored in syscomments. Execution plans for triggers are stored in sysprocedures. The system procedures described in the following sections provide information from the system tables about triggers.