2

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.

Improve this question
0

2 Answers 2

Reset to default
4

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.

Improve this answer
1
  • Thank for answer. Actually I don't need this triggers for pure audit sistem (so db2audit is not really an alternative). I need to save changed IDs of every table to one single table "admin.audit" and there is planned to be mechanism (third party software) to pull all "changes" from this table and transfer records to non-IBM database for some other purposes. To have such a function like CURRENT_TABLE_TRIGGER_WAS_FIRED_ON would just speedup my work. From my original post, it is really not clear what my original purpose is, because I tried to simplify my problem as much as possible. Commented Nov 27, 2020 at 13:48
0

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
Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.