List triggers by table name in Teradata database

Bart Gawrych - Dataedo Team Bart Gawrych 2019-10-31

Table of Contents:


    Article for: Teradata MySQL MariaDB IBM Db2

    Query below lists tables with their triggers in a database with details.

    Query

    SELECT  SubjectTableDatabaseName as TableDatabase,
            TableName as TableName,
            TriggerName,
            CASE ActionTime
                when 'A' then 'AFTER'
                when 'B' then 'BEFORE'
                when 'I' then 'INSTEAD OF'
                end,
            CASE Event
                WHEN 'U' THEN 'UPDATE'
                WHEN 'I' THEN 'INSERT'
                WHEN 'D' THEN 'DELETE'
                END,
            CASE EnabledFlag
                WHEN 'Y' THEN 'ENABLED'
                WHEN 'N' THEN 'DISABLED'
                END as Status,
            CASE Kind
                WHEN 'R' THEN 'ROW'
                WHEN 'S' THEN 'STATEMENT'
                end as TriggerKind,
            RequestText as Definition,
            CreateTimeStamp as CreateDate
    FROM    DBC.TriggersV
    ORDER BY    TableDatabase,
                TableName;
    

    Columns

    • TableDatabase - name of the trigger table database
    • TableName - name of the trigger table
    • TriggerName - name of the trigger
    • ActionTime - trigger activation time: before, after
    • Event - specific SQL operation: insert, update or delete
    • Status - trigger status
      • ENABLED
      • DISABLED
    • TriggerKind - trigger fires for each:
      • ROW - row modified in the subject table
      • STATEMENT - statement that modifies the subject table
    • Definition - SQL definiton of trigger
    • CreateDate - trigger creation time

    Rows

    • One row represents one trigger
    • Scope of rows: all triggers in a database
    • Ordered by table database name and table name

    Sample results

    0
    There are no comments. Click here to write the first comment.

    By continuing to use our website, you consent to the use of cookies in accordance with our Privacy Policy.
    Accept