How to create a simple trigger on DB2

a little example how to create a simple trigger on DB2

See the example below, how to create a trigger, and check how it works.

db2 "create table table_1 (cod int)"

db2 "create table table_2 (cod2 int)"

db2 "create trigger trig1 after insert on table_1 for each row update table_2  set cod2 = cod2 +1  where cod2 > 0"

db2 "insert into table_2 values (1)"
db2 "insert into table_2 values (2)"
db2 "insert into table_2 values (3)"
db2 "insert into table_2 values (4)"
db2 "insert into table_2 values (5)"
db2 "insert into table_2 values (5)"

 db2 "select * from table_2"

Output:

COD2      

-----------

          1
          2
          3
          4
          5
          6

  6 record(s) selected.

db2 "insert into table_1 values (1)"

 db2 "select * from table_2"

Output:

COD2      

-----------

          2
          3
          4
          5
          6
          7
  6 record(s) selected.

If the table_1 is 10 values, example:

 db2 "select * from table_1"

COD      

-----------

          1
          2
          3
          4
          5
          6
          7
          8
          9
         10

  10  record(s) selected.

and the values of table_2 is:

 db2 "select * from table_2"

COD2      

-----------

          1
          2
          3
          4
          5
          6  
     6  record(s) selected.

db2 "insert into table table_1 (select * from table_1 )

After the statement above, we have:

 db2 "select * from table_2"

COD2      

-----------

          11
          12
          13
          14
          15
          16  

       6  record(s) selected.

If you are using the clause "for each statement" instead of "for each row", you will have the results below:

COD2      

-----------

          2
          3
          4
          5
          6
          7  

       6  record(s) selected.

This trigger will be:

db2 "create trigger trig1 after insert on table_1 for each statement update table_2  set cod2 = cod2 +1  where cod2 > 0"