DB2 10.5 for Linux, UNIX, and Windows

Trigger processing of XML data

To create triggers that act in response to insert, update, or delete operations on XML data, use the CREATE TRIGGER statement to create BEFORE or AFTER triggers with the INSERT, UPDATE, or DELETE option on XML columns.

In the trigger body, transition variables that reference columns of type XML in affected rows can be used only for validation with the XMLVALIDATE function, to set XML column values to NULL, or to leave XML columns values unchanged.

A BEFORE trigger used with INSERT or UPDATE statements can be used to automatically validate XML documents before they are stored in an XML column. Validation of XML documents against registered XML schemas is optional, but highly recommended when data integrity is in question, because it ensures that only valid XML documents are inserted or updated.

The trigger is activated when the condition you set for it is met; if you do not specify any condition, the trigger always becomes activated. To trigger validation of XML documents against XML schemas only when it is necessary, you can specify a condition for the XML column with the WHEN clause of the BEFORE trigger. In the WHEN clause you include the required validation state for the XML documents, either that the documents must already be validated or that they must not be validated in order to activate the trigger (IS VALIDATED or IS NOT VALIDATED). Optionally, you can include one or several XML schemas by specifying the ACCORDING TO XMLSCHEMA clause that tells the trigger which XML schemas it should consider in the evaluation of the constraint.

Note: A trigger that specifies the WHEN clause will incur additional overhead. If validation before inserting XML documents should always be performed, the WHEN clause can be omitted.

Any trigger that references an XML schema has a dependency on that schema. Before you can reference an XML schema, it must be registered in the XML schema repository. If the XML schema the trigger depends on is later dropped from the XML schema repository, the trigger is marked inoperative.

Example 1: Create a BEFORE trigger that automatically validates XML documents containing new product descriptions before they are inserted into the PRODUCT table of the SAMPLE database. This trigger is activated any time before XML documents are updated.

CREATE TRIGGER NEWPROD NO CASCADE BEFORE INSERT ON PRODUCT
  REFERENCING NEW AS N
  FOR EACH ROW MODE DB2SQL
  BEGIN ATOMIC
     SET (N.DESCRIPTION) = XMLVALIDATE(N.DESCRIPTION
     ACCORDING TO XMLSCHEMA URI 'http://posample.org/product.xsd');
  END

Example 2: After you evolve an XML schema product2.xsd, already stored XML documents are guaranteed to be valid under the evolved schema, if they were valid against the original XML schema product.xsd. However, you might want to ensure that any updates to these XML documents are in fact also valid under the evolved schema product2.xsd. After you registered product2.xsd with the XML schema repository, a BEFORE UPDATE trigger can validate the XML documents before any updates are made:

CREATE TRIGGER UPDPROD NO CASCADE BEFORE UPDATE ON PRODUCT 
  REFERENCING NEW AS N 
  FOR EACH ROW MODE DB2SQL 
  BEGIN ATOMIC 
    SET (N.DESCRIPTION) = XMLVALIDATE(N.DESCRIPTION 
    ACCORDING TO XMLSCHEMA ID product2); 
  END

Example 3: You want to log inserted or updated customer records in another table. This requires you to create two triggers, one AFTER INSERT for newly inserted records and one AFTER UPDATE for updated records. In the following example, the triggers are created on the XML column Info of table MyCustomer, which is a copy of the sample Customer table. The triggers cause a record with the timestamp and the Customer ID to be written to a table called CustLog each time a record is inserted or updated in the MyCustomer table. The next example, Example 4, shows how to keep a copy of the actual data as well in the CustLog table

First create the AFTER INSERT trigger on the MyCustomer table:

CREATE TRIGGER INSAFTR 
  AFTER INSERT ON MyCustomer 
  REFERENCING NEW AS N 
  FOR EACH ROW 
  BEGIN ATOMIC 
    INSERT INTO CustLog VALUES(N.CID, CURRENT TIMESTAMP, 'Insert'); 
  END

Then create the AFTER UPDATE trigger on the MyCustomer table:

CREATE TRIGGER UPDAFTR 
  AFTER UPDATE OF Info 
  ON MyCustomer 
  REFERENCING NEW AS N 
  FOR EACH ROW 
  BEGIN ATOMIC 
    INSERT INTO CustLog VALUES(N.CID, CURRENT TIMESTAMP, 'Update'); 
  END

Example 4: This example demonstrates how to set up a table to act as an audit log for inserted or updated customer records. As in Example 3, you create two triggers, one AFTER INSERT for newly inserted records and one AFTER UPDATE for updated records. The triggers are created on the XML column Info of table MyCustomer, which is a copy of the sample Customer table. Each time a record is inserted or updated in the MyCustomer table, the triggers cause a record with the timestamp, the Customer ID, and the contents of the XML type column, Info, to be written to a table called CustLog.

First create the AFTER INSERT trigger on the MyCustomer table:

CREATE TRIGGER INSAFTR 
  AFTER INSERT ON MyCustomer 
  REFERENCING NEW AS N 
  FOR EACH ROW 
  BEGIN ATOMIC 
    INSERT INTO CustLog VALUES(N.CID, CURRENT TIMESTAMP, 'Insert',
      (SELECT Info FROM MyCustomer WHERE CID = N.CID));
  END

Then create the AFTER UPDATE trigger on the MyCustomer table:

CREATE TRIGGER UPDAFTR 
  AFTER UPDATE OF Info 
  ON MyCustomer 
  REFERENCING NEW AS N 
  FOR EACH ROW 
  BEGIN ATOMIC 
    INSERT INTO CustLog VALUES(N.CID, CURRENT TIMESTAMP, 'Update', 
      (SELECT Info FROM MyCustomer WHERE CID = N.CID));
  END