Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2012
    Posts
    134

    Unanswered: table variables in triggers

    Hi all,
    I'm using DB2 10.5 on Linux.

    I would like to declare a table variable in a trigger, to store temporary data only useful for the trigger.
    I'm wondering if I should declare a DGTT or there are smarter alternatives, since the trigger must be as fast as possible and it can be fired thousands of times in an hour.

    Thanks for any advice you can provide.

  2. #2
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    294
    Provided Answers: 45
    Hi,

    You can use CGTTs or arrays in triggers.
    Only tests can show the difference in performance.
    Regards,
    Mark.

  3. #3
    Join Date
    Mar 2012
    Posts
    134
    Hi Mark,
    thanks for your reply.
    I tried with CGTTs but I'm not able to create a trigger with such a statement in the triggered action.
    Could you please send an example about a dummy trigger with CGTT that is created and filled?

  4. #4
    Join Date
    Oct 2007
    Posts
    161
    Provided Answers: 9
    Hard to tell what you are doing with limited info you have given us here. I would think that a trigger on a heavily used table that then performs inserts to a temp table, then selects some data out to perform some function is not going to be a good long term solution, but then I've been wrong in the past. Most times, a temp table can be handled within the initial SQL, it just requires more thought and complexity and will typically perform better.

  5. #5
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    294
    Provided Answers: 45
    Try this:
    Code:
    --#SET TERMINATOR @
    create global temporary table test_trig(ts timestamp) on commit preserve rows@
    create table test_trig2 (i int, ts timestamp) in userspace1@
    
    create or replace trigger test_trig2_air
    after insert on test_trig2
    referencing new as n
    for each row
    begin atomic
      insert into test_trig 
      select ts from table(values n.ts) t(ts)
      where n.i=1;
    end@
    Regards,
    Mark.

Posting Permissions

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