Get current table name trigger was fired on

Question

My system: Db2 11.1 on Linux.

1.Create some table:

create table admin.tab (a int not null primary key, b int) @

2.Create audit table:

create table admin.audit (table_name varchar(2000), datetime timestamp) @

3.Very very simple sample, when insert into source table admin.tab I would like to insert into admin.audit:

create trigger admin.mytrig
after insert on admin.tab
referencing new as new
for each row
begin
    insert into admin.audit (table_name, datetime) 
       values ('admin.tab', current_timestamp);
end
@

4.Above trigger works fine, but is there any way in above trigger I can replace 'admin.tab' constant with current table name that was fired using trigger, like CURRENT_TABLE_TRIGGER_WAS_FIRED_ON or something?

create trigger admin.mytrig
after insert on admin.tab
referencing new as new
for each row
begin
    insert into admin.audit (table_name, datetime) 
       values (CURRENT_TABLE_TRIGGER_WAS_FIRED_ON, current_timestamp);
end
@

I need this kind of solution, because I need to create hundreds of such triggers and to share as much code as possible saves a lot of time.

Answer 1

Is there any way I can obtain inside a trigger the name of the table on which the trigger is defined?

No, there is no such capability in any of the current (as of year 2020) Db2 versions, for reasons that I won't go into. For a comparison, consider why a C function cannot know its name or the name of the function that called it.

For each trigger that you write you must know the corresponding table name when you write it, simply for the fact that it is part of the trigger definition:

CREATE TRIGGER <name> ... ON <table name>

and it is trivial to also add that name, as a literal, to the INSERT statement.

If you face a tedious task of creating an inordinate amount of nearly identical triggers, use your favourite scripting language and one of the template engines available for it to define a trigger template and generate DDL statements for all your triggers from it.

Alternatively, consider using the Db2 audit facility, which is perfectly capable of doing what your triggers are meant to do.

Answer 2

You would be better served by generating your required triggers with a DB2 SQL compound statement code such as this

BEGIN
    FOR C AS cur CURSOR WITH HOLD FOR
        SELECT        'CREATE TRIGGER "'  || RTRIM(TABSCHEMA) || '"."' || TABNAME || '_TRIG"'
        || CHR(10) || 'AFTER INSERT ON "' || RTRIM(TABSCHEMA) || '"."' || TABNAME || '"'
        || CHR(10) || 'REFERENCING NEW AS NEW FOR EACH ROW'
        || CHR(10) || 'INSERT INTO ADMIN.AUDIT VALUES (''' || TABSCHEMA || ''',''' || TABNAME || ''', CURRENT_TIMESTAMP )'
            AS STMT
        FROM
            SYSCAT.TABLES
        WHERE
            TABSCHEMA = 'PAUL'
        AND TYPE      = 'T'
        AND TABLEORG  = 'R'
        AND (TABSCHEMA, TABNAME || '_TRIG') NOT IN (SELECT TRIGSCHEMA, TRIGNAME FROM SYSCAT.TRIGGERS)
        ORDER BY 
            TABSCHEMA
        ,   TABNAME
    DO
          EXECUTE IMMEDIATE C.STMT;
    END FOR;
END