XML support in triggers

You can use the CREATE TRIGGER SQL statement to create BEFORE UPDATE or AFTER UPDATE triggers on XML columns. You can also use this statement to create INSERT or DELETE triggers on tables that include XML columns.

Triggers on tables with XML columns have the following restrictions:

A transition variable cannot have the XML type.
A column of a transition table that is referenced in the trigger body cannot have the XML type.

Example: Create a BEFORE UPDATE trigger on table MYCUSTOMER, which is a copy of the sample CUSTOMER table.

CREATE TRIGGER UPDBEFORE 
  NO CASCADE BEFORE UPDATE ON MYCUSTOMER 
  REFERENCING NEW AS N 
  FOR EACH ROW MODE DB2SQL
  BEGIN ATOMIC 
    SET N.CID=O.CID+10000; 
  END

Although MYCUSTOMER contains two XML columns, the transition variable N.CID refers to a non-XML column, so this trigger is valid.

Example: Create an INSERT trigger on the MYCUSTOMER table.

CREATE TRIGGER INSAFTR 
  AFTER INSERT ON MYCUSTOMER 
  REFERENCING NEW TABLE AS N 
  FOR EACH ROW MODE DB2SQL
  BEGIN ATOMIC 
    SELECT N.CID FROM N; 
  END

Although transition table N has two XML columns, the trigger body does not refer to the XML columns of the transition table, so this trigger is valid.

If you need to use data from an XML column in a transition variable, you can circumvent the restriction on transition variables of the XML data type in triggers by using the XMLTABLE function to access the data in the XML column as non-XML data types.

Example: Suppose that the CUST table is defined like this:

CREATE TABLE CUST (
 ID   BIGINT NOT NULL PRIMARY KEY,
 NAME VARCHAR(30),
 CITY VARCHAR(20),
 ZIP  VARCHAR(12),
 INFO XML)

Create an INSERT trigger on the CUST table that copies name, city, and zip code information to itself for rows that are inserted into the CUST table. The data that you need to copy is in XML column INFO. You cannot refer to INFO directly in the trigger body. However, you can use the XMLTABLE function to create a result table with non-XML columns that contain the fields that you need. Then you can use a subselect to retrieve the row of the result table that corresponds to the row whose insertion activates the trigger.

CREATE TRIGGER INS_CUST
   AFTER INSERT ON CUST
   REFERENCING NEW AS NEWROW
   FOR EACH ROW MODE DB2SQL
   BEGIN ATOMIC
     UPDATE CUST
       SET (NAME, CITY, ZIP) =
           (SELECT X.NAME, X.CITY, X.ZIP
             FROM CUST, XMLTABLE('CUSTOMERINFO' PASSING CUST.INFO
                   COLUMNS
                     NAME VARCHAR(30)  PATH 'NAME',
                     CITY VARCHAR(20)  PATH 'ADDR/CITY',
                     ZIP  VARCHAR(12)  PATH 'ADDR/PCODE-ZIP') AS X
             WHERE CUST.ID = NEWROW.ID        
             )
       WHERE CUST.ID = NEWROW.ID;
   END