CREATE TABLE persons(
code CHAR(9) not null,
birth DATE,
age INTEGER);

CREATE TRIGGER age_calculator
AFTER INSERT ON persons
REFERENCING NEW AS nxt
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC

UPDATE persons
SET age = YEAR(CURRENT DATE)-YEAR(nxt.birth)
WHERE code = nxt.code;

END

> CREATE (0.031 secs.)
> SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: END-OF-STATEMENT;ATE)-YEAR(nxt.birth);<delim_semicolon>

Which is the problem with the trigger creation?

Probably it's the delimiter. You should set in your environment (whatever it may be) your delimiter to some other character (for instance to # ). Then run it again - and I recon, that it will be working!

Cheers, Bille

You might want to consider using a BEFORE INSERT trigger(This adjusts the transition variable instead of having to access the table):

CREATE TRIGGER BI_age_calc
BEFORE INSERT ON persons
REFERENCING NEW AS nxt
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC

SET nxt.age = YEAR(CURRENT DATE)-YEAR(nxt.birth);

END

Also, consider an associated AFTER UPDATE trigger to help insure that the derived column is kept up-to-date.

CREATE TRIGGER AU_age_calc
AFTER UPDATE of birth ON persons
REFERENCING OLD as OLD NEW AS nxt
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC

UPDATE persons
SET age = YEAR(CURRENT DATE)-YEAR(nxt.birth)
WHERE code = nxt.code;

END

In addition, you may want to restrict updatability of the age column so that the value is maintained by the triggers only.