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.

0

2 Answers 2

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.

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.
    – folow
    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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.