Results 1 to 3 of 3

Thread: DB2 Trigger

  1. #1
    Join Date
    May 2002
    Posts
    12

    Unanswered: DB2 Trigger

    Hi,

    I have the following table:

    Table A
    x char(10) not null
    y char(20) not null
    z1 char(10)
    z2 char(10)
    t1 char(10)
    t2 char(10)
    u char (10) not null

    In ASP page, we can have several different ways to insert into this table based on the input of the user. (Note that z1, z2, t1, t2 can be null). For example:

    Case 1: insert into A (x, y, z1, t2, u) values...
    Case 2: insert into A (x, y, z2, t2, u) values...
    Case 3: insert into A (x, y, z1, t1, u) values...
    ...............
    ...............

    And I have the following trigger that insert in all the fields into the history table no matter which case is used

    CREATE TRIGGER AIns
    AFTER INSERT ON A
    REFERENCING NEW AS N
    FOR EACH ROW MODE DB2SQL
    INSERT INTO A_History (x, y, z1, z2, t1, t2, u)
    VALUES (N.x, N.y, N.z1, N.z2, N.t1, N.t2, N.u)

    N.z1, N.z2, N.t1, N.t2 might be null depending on the case... Is that why it doesn't work? Is there any way that we can change this trigger so it will work no matter what cases we're gonna use to insert data.

    Thank you very much for your help!!!

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: DB2 Trigger

    Hi,

    I tried your definitions and it works . see the output below .... Does it not reflect the output you want ?
    Do you get any error message?

    connect to sample

    Database Connection Information

    Database server = DB2/NT 7.2.4
    SQL authorization ID = SATHYARA...
    Local database alias = SAMPLE


    create table table_a(x char(10) not null , y char(20) not null , z1 char(10) , z2 char(10) , t1 char(10) , t2 char(10) , u char (10) not null)
    DB20000I The SQL command completed successfully.

    create table A_History like table_a
    DB20000I The SQL command completed successfully.

    CREATE TRIGGER AIns
    AFTER INSERT ON table_A
    REFERENCING NEW AS N
    FOR EACH ROW MODE DB2SQL
    INSERT INTO A_History (x, y, z1, z2, t1, t2, u)
    VALUES (N.x, N.y, N.z1, N.z2, N.t1, N.t2, N.u)
    DB20000I The SQL command completed successfully.

    insert into table_A (x, y, z1, t2, u) values('x1','y1','z11','t21','u1')
    DB20000I The SQL command completed successfully.

    insert into table_A (x, y, z2, t2, u) values('x2','y2','z22','t22','u2')
    DB20000I The SQL command completed successfully.

    insert into table_A (x, y, z1, t1, u) values('x3','y3','z13','t13','u3')
    DB20000I The SQL command completed successfully.

    select * from a_history

    X Y Z1 Z2 T1 T2 U
    ---------- -------------------- ---------- ---------- ---------- ---------- ----------
    x1 y1 z11 - - t21 u1
    x2 y2 - z22 - t22 u2
    x3 y3 z13 - t13 - u3

    3 record(s) selected.

    Cheers

    Sathyaranm


    Originally posted by xauxi
    Hi,

    I have the following table:

    Table A
    x char(10) not null
    y char(20) not null
    z1 char(10)
    z2 char(10)
    t1 char(10)
    t2 char(10)
    u char (10) not null

    In ASP page, we can have several different ways to insert into this table based on the input of the user. (Note that z1, z2, t1, t2 can be null). For example:

    Case 1: insert into A (x, y, z1, t2, u) values...
    Case 2: insert into A (x, y, z2, t2, u) values...
    Case 3: insert into A (x, y, z1, t1, u) values...
    ...............
    ...............

    And I have the following trigger that insert in all the fields into the history table no matter which case is used

    CREATE TRIGGER AIns
    AFTER INSERT ON A
    REFERENCING NEW AS N
    FOR EACH ROW MODE DB2SQL
    INSERT INTO A_History (x, y, z1, z2, t1, t2, u)
    VALUES (N.x, N.y, N.z1, N.z2, N.t1, N.t2, N.u)

    N.z1, N.z2, N.t1, N.t2 might be null depending on the case... Is that why it doesn't work? Is there any way that we can change this trigger so it will work no matter what cases we're gonna use to insert data.

    Thank you very much for your help!!!

  3. #3
    Join Date
    May 2002
    Posts
    12
    It works now... Thank you very much...

Posting Permissions

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