Trigger failure issue

[lespaul]

I have a table in a production library (CMLIB.CMPCHKINS), I made a copy of this table in my library (LANDREWS.CMPCHKINS). I added a trigger to the CMLIB table:

/* Creating SQL trigger CMLIB.TO_LANDREWS */
CREATE TRIGGER CMLIB.TO_LANDREWS 
AFTER INSERT ON CMLIB.CMPCHKINS 
REFERENCING NEW n 
FOR EACH ROW  
MODE DB2ROW  
BEGIN ATOMIC
    INSERT INTO LANDREWS.CMPCHKINS (offnum, magenc, chkindate, chkintime, status, courtrooms, timesort, message, addcrtroom, chkouttime, msgdate, msgtime, msgsource) values (n.offnum, n.magenc, n.chkindate, n.chkintime, n.status, n.courtrooms, n.timesort, n.message, n.addcrtroom, n.chkouttime, n.msgdate, n.msgtime, n.msgsource);
 END;

not very complicated, whenever a record is inserted in CMLIB, copy that record to LANDREWS (there is another trigger on LANDREWS that updates another table - mentioning this because I don't know if it's important or not).

Now, the trigger code above is an AFTER INSERT. I would expect that the trigger doesn't fire until AFTER the record is INSERTed in CMLIB. But the application that is inserting the record to CMLIB is getting a database error that the trigger on LANDREWS failed (Error Message code says 'trigger failed because trigger failed' typical IBM error code!) and therefore, the record is NOT inserted into CMLIB.

Is this correct? Is there an implicit rollback in the production table if the trigger insert fails? Is there a way to override that? I really don't care if the trigger failed to insert the record into LANDREWS, but it causes major issues that it ALSO fails to insert the record into the CMLIB copy.

I've been reading through the Redbook on triggers, sp and UDF, but haven't found anything on this type of situation.

Thanks!

leslie

 

[blom0344]

By the looks of it you have set up cascading triggers. Not sure how it is handled on AS400, but if the whole procedure is handled as one unit of work (UOW) and a part fails then everything is rolled back. (I'm guessing here)

Here's a link to a db2mag article and perhaps a link for a real AS400 guru..

http://www.db2mag.com/db_area/archives/2002/q2/pavlak.shtml

Ties Blom