Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2012
    Posts
    24

    Unanswered: How to dynamically choose column when referencing to OLD/NEW row in a trigger?

    Hi,

    I am working with DB2 9.7 on Windows 7.

    I have a trigger that I will insert into hundreds of tables. I want the trigger to be generic, so that it is independent on what table it is working on. The trigger is created into all tables using a stored procedure. The SP also dynamically gives the table name into the trigger.

    My problem is when referencing to update trigger OLD and NEW columns. A static approach would be the following:


    AFTER UPDATE ON EMPLOYEE
    BEGIN ATOMIC
    REFERENCING OLD AS DELETED NEW AS INSERTED
    FOR EACH ROW

    IF(INSERTED.NAME <> DELETED.NAME) THEN ...


    However as I stated, the trigger will be inserted to different tables, so we actually don't know what the column names will be in INSERTED and DELETED. Therefore, I first retrieve all column names from the table and loop through the cursor, as below:

    FOR C1 AS
    SELECT COLNAME
    FROM syscat.columns as a
    WHERE a.TABNAME = 'EMPLOYEE'
    DO
    IF (DELETED.'C1.COLNAME' <> INSERTED.'C1.COLNAME') THEN ...


    The above does of course not work because "DELETED.'C1.COLNAME'" is not valid syntax, but maybe you see what I am trying to do? The "C1.COLNAME" contains a column name in table 'EMPLOYEE' (or any table it is working on). The table "EMPLOYEE" is inserted there by the stored procedure.

    Any ideas on how to correctly do what the syntax "DELETED.'C1.COLNAME'" attempts to do? Thanks!

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Removed incorrect idea.
    Last edited by tonkuma; 08-08-12 at 09:06.

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You cannot do what you are trying to do.

    First of all you cannot have a stored procedure call a trigger. Triggers are database objects whose execution is controlled by the databases and only execute at the time indicated in the trigger (e.g. AFTER UPDATE).

    Second, the Correlation names for OLD and NEW have no scope outside the trigger, so all work using them (like comparing the OLD and NEW values of a column has to be done in the trigger.

    So you cannot do this generically.

    What I would suggest is that you write a utility to create the triggers for you. You want them all to be basically the same algorithm but they all have to be different because each table is different. So have the utility scan the catalog (syscat.columns) and generate and run the text to create each trigger.

    Andy

  4. #4
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    @ARWinner: actually he wants to call a SP inside a trigger, so it should work

    @SE
    just an idea, I do not have test it: use corellation tables (REFERENCING OLD TABLE ... NEW TABLE) and try to send them as parameters to your SP.
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by aflorin27 View Post
    @ARWinner: actually he wants to call a SP inside a trigger, so it should work

    @SE
    just an idea, I do not have test it: use corellation tables (REFERENCING OLD TABLE ... NEW TABLE) and try to send them as parameters to your SP.
    That is just it, the correlation names are only valid within the trigger, you cannot pass them to a stored procedure, because they have no meaning there.

    Andy

  6. #6
    Join Date
    Aug 2012
    Posts
    24
    Quote Originally Posted by ARWinner View Post
    You cannot do what you are trying to do.

    First of all you cannot have a stored procedure call a trigger. Triggers are database objects whose execution is controlled by the databases and only execute at the time indicated in the trigger (e.g. AFTER UPDATE).

    Second, the Correlation names for OLD and NEW have no scope outside the trigger, so all work using them (like comparing the OLD and NEW values of a column has to be done in the trigger.

    So you cannot do this generically.

    What I would suggest is that you write a utility to create the triggers for you. You want them all to be basically the same algorithm but they all have to be different because each table is different. So have the utility scan the catalog (syscat.columns) and generate and run the text to create each trigger.

    Andy
    I may have been vague, but the flow is currently quite as you suggested. My implementation has a SP that works as a utility. It takes a list of tables to which it creates triggers. The SP loops through the tables list, takes one table, generates a string SQL (inline SQL, I believe it is called), executes the inline SQL (so now the trigger has been generated into the table). It performs this for all the tables.

    Your idea to remove the "generic" use of columns from the trigger, and move it up to the SP utility sounds like a valid approach. It is a bit of more work and prone to some errors, but it should work. I was just hoping there would be a generic way to access the columns in the OLD/NEW tables, because the column names are known within the scope of the trigger Thanks anyway!

  7. #7
    Join Date
    Aug 2012
    Posts
    24
    Quote Originally Posted by SoftwareEngineer3 View Post
    I may have been vague, but the flow is currently quite as you suggested. My implementation has a SP that works as a utility. It takes a list of tables to which it creates triggers. The SP loops through the tables list, takes one table, generates a string SQL (inline SQL, I believe it is called), executes the inline SQL (so now the trigger has been generated into the table). It performs this for all the tables.

    Your idea to remove the "generic" use of columns from the trigger, and move it up to the SP utility sounds like a valid approach. It is a bit of more work and prone to some errors, but it should work. I was just hoping there would be a generic way to access the columns in the OLD/NEW tables, because the column names are known within the scope of the trigger Thanks anyway!
    Hmm, the downside with this is that since e.g. one of my tables has a column count of 37, I will get 37 x the below code:

    IF (DELETED.col1 <> INSERTED.col1) THEN
    --Create a new audit row
    END IF;

    Since in DB2 a trigger has to be created separately for insert, update, delete, my table definitions start to become quite epic...

  8. #8
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Your aim seems to be to audit data changes.


    If programming in 'c' is an option for you, consider an alternate solution that does not require triggers, such as scanning the db2 transaction logs with the db2ReadLog or db2ReadLogNoConn API. See sample programs in sqllib samples c directory on your d 2-server.

  9. #9
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by SoftwareEngineer3 View Post

    in DB2 a trigger has to be created separately for insert, update, delete
    Since version 9.7 fixpack 4 DB2 supports multi-action triggers.
    ---
    "It does not work" is not a valid problem statement.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •