How to create a SQL trigger for AS400 Table

I'm a Java developer but not a RPG developer. For one of my project i need to create a SQL AFTER INSERT trigger for a AS400 table (TABLE A) and copy that newly created row into the another table (TABLE B). Both table have the same structure. Really appreciate if someone can guide me to do the process. Please note i have fully authority to do any kind of process on AS400. Thanks in advance.

EDIT AS400 is a IBM appliance that's contain a SQL Engine (DB2)

Solution

IBM DB2 for i CREATE TRIGGER reference

You have a few choices to make, mode and granularity

MODE DB2SQL is valid for AFTER triggers. MODE DB2SQL AFTER triggers are activated after all of the row operations have occurred.

MODE DB2ROW triggers are activated on each row operation. MODE DB2ROW is valid for both the BEFORE and AFTER activation time.

FOR EACH ROW Specifies that the database manager executes the triggered-action for each row of the subject table that the triggering operation modifies. If the triggering operation does not modify any rows, the triggered-action is not executed.

FOR EACH STATEMENT Specifies that the database manager executes the triggered-action only once for the triggering operation. Even if the triggering operation does not modify or delete any rows, the triggered action is still executed once. FOR EACH STATEMENT cannot be specified for a BEFORE trigger. FOR EACH STATEMENT cannot be specified for a MODE DB2ROW trigger.

The REFERENCING clause of the documentation has a nice table showing how they interact.

Assuming your original table is having multi-row insert done, then performance would benefit from having the trigger fire once per statement...

CREATE OR REPLACE TRIGGER TRIGGER_NAME AFTER INSERT
ON TABLE_A
  REFERENCING NEW TABLE AS TABLE_N 
  FOR EACH STATEMENT MODE DB2SQL

  begin atomic
    insert into TABLE_B 
      select * from TABLE_N;
  end

Alternate, row by row...

CREATE OR REPLACE TRIGGER TRIGGER_NAME AFTER INSERT
ON TABLE_A
  REFERENCING NEW ROW AS N 
  FOR EACH ROW MODE DB2ROW

  begin atomic
    insert into TABLE_B 
      values(n.col1, n.col2, n.col3, <....>);
  end

If only one row is being inserted into TABLE_A at a time, both statements will insert 1 row at a time into TABLE_B.

If 10 rows are inserted into TABLE_A with a single insert, the FOR EACH STATEMENT MODE DB2SQL will insert 10 rows at once into TABLE_B; whereas FOR EACH ROW MODE DB2ROW will do 10 separate inserts.

Sometimes, you have to use FOR EACH ROW, for example for BEFORE TRIGGERs.

But (generally) in SQL, you're better off doing set based operations whenever possible.