How to print firebird triggers?

Question

I would like to print the contents of all the triggers from a firebird database.

I have a firebird database with 20 fields and 56 triggers, and each trigger is about 10 to 20 lines of sql code. Is there any gui software able to do this, or an sql command able to SELECT all triggers and then export them as txt file?

Answer 1

All triggers can be found from RDB$TRIGGERS table.

You can use this SQL query to get information about triggers:

SELECT RDB$TRIGGER_NAME AS trigger_name,
       RDB$RELATION_NAME AS table_name,
       RDB$TRIGGER_SOURCE AS trigger_body,
       CASE RDB$TRIGGER_TYPE
        WHEN 1 THEN 'BEFORE'
        WHEN 2 THEN 'AFTER'
        WHEN 3 THEN 'BEFORE'
        WHEN 4 THEN 'AFTER'
        WHEN 5 THEN 'BEFORE'
        WHEN 6 THEN 'AFTER'
       END AS trigger_type,
       CASE RDB$TRIGGER_TYPE
        WHEN 1 THEN 'INSERT'
        WHEN 2 THEN 'INSERT'
        WHEN 3 THEN 'UPDATE'
        WHEN 4 THEN 'UPDATE'
        WHEN 5 THEN 'DELETE'
        WHEN 6 THEN 'DELETE'
       END AS trigger_event,
       CASE RDB$TRIGGER_INACTIVE
        WHEN 1 THEN 0 ELSE 1
       END AS trigger_enabled,
       RDB$DESCRIPTION AS trigger_comment
  FROM RDB$TRIGGERS

Source

Answer 2

You can create view like this:

create view TriggerSourceView
as
select
    trim(RDB$RELATION_NAME) as tableName,
    trim(RDB$TRIGGER_NAME) as triggerName,
    'create or alter trigger ' || trim(RDB$TRIGGER_NAME) || case RDB$TRIGGER_INACTIVE when 1 then ' inactive ' else ' active ' end ||
    case RDB$TRIGGER_TYPE
        when 1 then  'before insert'
        when 2 then  'after insert'
        when 3 then  'before update'
        when 4 then  'after update'
        when 5 then  'before delete'
        when 6 then  'after delete'
        when 17 then  'before insert or update'
        when 18 then  'after insert or update'
        when 25 then  'before insert or delete'
        when 26 then  'after insert or delete'
        when 27 then  'before update or delete'
        when 28 then  'after update or delete'
        when 113 then  'before insert or update or delete'
        when 114 then  'after insert or update or delete'
        when 8192 then  'on connect'
        when 8193 then  'on disconnect'
        when 8194 then  'on transaction start'
        when 8195 then  'on transaction commit'
        when 8196 then  'on transaction rollback'
    end || ' position ' || RDB$TRIGGER_SEQUENCE || ' on ' || trim(RDB$RELATION_NAME) || ' ' || RDB$TRIGGER_SOURCE as source
from RDB$TRIGGERS where RDB$TRIGGER_SOURCE is not null;

and then use it like:

select * from TriggerSourceView where tableName = 'MyTable'

I'm using this when working with Intellij Idea since it doesn't support Firebird.