Db2 – Get current table name trigger was fired on

db2db2-luwtrigger

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.

Best Answer

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.

Related Question