Results 1 to 3 of 3

Thread: Trigger

  1. #1
    Join Date
    Feb 2005
    Location
    Barcelona
    Posts
    42

    Angry Unanswered: Trigger


    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?
    Last edited by dvtdbd; 03-04-05 at 07:12.

  2. #2
    Join Date
    Jan 2004
    Location
    Europe, Finland, Helsinki
    Posts
    64
    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

  3. #3
    Join Date
    Aug 2004
    Posts
    330
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •