db2 v7R1 - Trigger INSERT New As XML Str

Question

Trying to create a record of changes as XML string.

The below code doesn't work, either XML cannot cast to varchar, or SQL invalid error.

CREATE TRIGGER "QS36F"."WEBTEST1_I" 
    AFTER INSERT ON "QS36F"."WEBTEST1"
    REFERENCING  NEW AS R
    FOR EACH ROW MODE DB2SQL
INSERT INTO "QS36F"."WEBTEST2" ( PREVIOUS_R , NEW_R , ENTRY_DATE ) VALUES ( NULL , CAST(XMLROW(R.ID,R.ITEMNUMBER) as varchar(16355)), CURRENT_TIMESTAMP )

Also tried

CREATE TRIGGER "QS36F"."WEBTEST1_I" 
    AFTER INSERT ON "QS36F"."WEBTEST1"
    REFERENCING  NEW AS R
    FOR EACH ROW MODE DB2SQL
INSERT INTO "QS36F"."WEBTEST2" ( PREVIOUS_R , NEW_R , ENTRY_DATE ) VALUES ( NULL ,  CAST((SELECT XMLROW(R.ID, R.ITEMNUMBER) FROM sysibm.sysdummy1) as varchar(16355)) , CURRENT_TIMESTAMP )

Also tried to make the data type of WEBTEST2 as XML... No dice.

[]

EDIT:

EDIT2:

I am getting

‬‪‬‎[‪SQL0312‬‎]‪‬‎ ‪Variable‬‎ ‪ITEMNUMBER‬‎ ‪not‬‎ ‪defined‬‎ ‪or‬‎ ‪not‬‎ ‪usable‬‎.‪

for the following query?

CREATE TRIGGER "QS36F"."WEBTEST1_U" 
    AFTER UPDATE ON "QS36F"."WEBTEST1"
    REFERENCING
    OLD AS o
    NEW AS r
    FOR EACH ROW MODE DB2SQL
    INSERT INTO 
     "QS36F"."WEBTEST2" ( NEW_R , PREVIOUS_R,  ENTRY_DATE ) 
                VALUES ( (select xmlserialize(XMLROW(id as id,itemnumber as itemnumber) as varchar(16355))
                          from table ( values (r.id, r.itemnumber)) as tbl
                          ), (select xmlserialize(XMLROW(id as id,itemnumber as itemnumber) as varchar(16355))
                          from table ( values (o.id, o.itemnumber)) as tbl
                          )
                       , CURRENT_TIMESTAMP 
                       )
          );

Answer 1

Try this...

CREATE TRIGGER "QS36F"."WEBTEST1_I" 
    AFTER INSERT ON "QS36F"."WEBTEST1"
    REFERENCING  NEW AS R
    FOR EACH ROW MODE DB2SQL
    INSERT INTO 
     "QS36F"."WEBTEST2" ( NEW_R , ENTRY_DATE ) 
                VALUES ( (select xmlserialize(XMLROW(id as id,itemnumber as itemnumber) as varchar(16355))
                          from table ( values (r.id, r.itemnumber)) as tbl
                          )
                       , CURRENT_TIMESTAMP 
                       )
          );