Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2003
    Posts
    1,605

    Unanswered: What is difference between 'row' and 'statement' in trigger action

    Hi,

    What is difference between "for each row" and "for each statement" in trigger action after insert.

    My system: db2 v8.1fp6 on win2000

    Thanks,
    Grofaty

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    If you issue a multi-row INSERT statement, the 'each statement' trigger is invoked only once whereas a "each row" trigger is invoked for every row ..

    Eg of a multi-row INSERT
    insert into tab1 values(2,2),(3,3)
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    Thanks. What about if SQL is written like this:
    1. INSERT INTO schema.tab1 SELECT * FROM schema.tab2
    or like this:
    2. UPDATE schema.tab1 WHERE value = value (I would like to involke trigger on whole table)

    My question: how many times is trigger fired out if ROW or STATEMENT is used in trigger for query 1 and query 2.

    Thanks,
    Grofaty

  4. #4
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    I have noticed that 'for statement' works 100-times faster then 'for row'.
    I am still interested in above questions.

    Thanks,
    Grofaty

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Grofaty,
    For statement 1, a trigger with STATEMENT will execute only once, while a trigger with ROW will execute SELECT count(*) FROM schema.tab2 times.

    Similiarly for statement 2, a trigger with STATEMENT will execute only once, while one with ROW will execute select count(*) from schema.tab1 WHERE value = value

    HTH

    Andy

  6. #6
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    arwinner: thanks a lot. 'With ROW' is 100-times slower. What is the benefit of using 'with ROW' option? When must be 'with ROW' used instread of 'with STATEMENT'.

    Grofaty

  7. #7
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    There is no hard and fast rule ... it depends on the business requirement rather than technical ...

    For eg, consider and UPDATE trigger ... If the business requirement is to log before image of each row along with the update timestamp, then you will have to use ROW .. But, if the requirement is to just log the time when a table is updated, then use the STATEMENT

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  8. #8
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    Thanks to you all.

    Grofaty

Posting Permissions

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