Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Aug 2003
    Location
    Austin, Texas
    Posts
    12

    Unanswered: Simple trigger help needed, Update who & when

    Hi...
    I have a simple question for one who is experienced...
    In all our tables we have the two fields Edit_Who and Edit_When:
    KL_Edit_Who VarChar(30) DEFAULT USER,
    KL_Edit_When TIMESTAMP DEFAULT CURRENT TIMESTAMP,

    In order to get those updated on each update we have to revert to triggers.
    So I wrote two versions neither works...
    CREATE TRIGGER KL_Key_LastGen_Upd
    AFTER INSERT ON DBO.KL_Key_LastGen
    FOR EACH ROW MODE DB2SQL
    UPDATE DBO.KL_Key_LastGen SET KL_Edit_Who = USER, KL_Edit_When = CURRENT TIMESTAMP

    CREATE TRIGGER KL_Key_LastGen_Upd
    AFTER INSERT ON DBO.KL_Key_LastGen
    REFERENCING NEW_TABLE AS NEWEMPS
    FOR EACH STATEMENT MODE DB2SQL
    UPDATE DBO.KL_Key_LastGen K SET KL_Edit_Who = USER, KL_Edit_When = CURRENT TIMESTAMP
    WHERE KL_Table_Key IN( SELECT NEWEMPS.KL_Table_Key FROM NEWEMPS)

    Anyone care to shed some light on my errors ???
    Thanks
    JR
    JR Andreassen
    janrune_NOSPAM@io.com

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

    Re: Simple trigger help needed, Update who & when

    When you say neither works, does it mean you get an error ?

    If so, please post the message

    Cheers
    Sathyaram

    Originally posted by janrune
    Hi...
    I have a simple question for one who is experienced...
    In all our tables we have the two fields Edit_Who and Edit_When:
    KL_Edit_Who VarChar(30) DEFAULT USER,
    KL_Edit_When TIMESTAMP DEFAULT CURRENT TIMESTAMP,

    In order to get those updated on each update we have to revert to triggers.
    So I wrote two versions neither works...
    CREATE TRIGGER KL_Key_LastGen_Upd
    AFTER INSERT ON DBO.KL_Key_LastGen
    FOR EACH ROW MODE DB2SQL
    UPDATE DBO.KL_Key_LastGen SET KL_Edit_Who = USER, KL_Edit_When = CURRENT TIMESTAMP

    CREATE TRIGGER KL_Key_LastGen_Upd
    AFTER INSERT ON DBO.KL_Key_LastGen
    REFERENCING NEW_TABLE AS NEWEMPS
    FOR EACH STATEMENT MODE DB2SQL
    UPDATE DBO.KL_Key_LastGen K SET KL_Edit_Who = USER, KL_Edit_When = CURRENT TIMESTAMP
    WHERE KL_Table_Key IN( SELECT NEWEMPS.KL_Table_Key FROM NEWEMPS)

    Anyone care to shed some light on my errors ???
    Thanks
    JR
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Aug 2003
    Location
    Austin, Texas
    Posts
    12

    Re: Simple trigger help needed, Update who & when

    Originally posted by sathyaram_s
    When you say neither works, does it mean you get an error ?

    If so, please post the message

    Cheers
    Sathyaram

    Actually Nothing happens...
    It accepts the trigger, but nothing get's executed.
    (even though the first one could have updated the whole table.)
    That's what's really got me stomped.
    Thanks
    JR
    JR Andreassen
    janrune_NOSPAM@io.com

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

    Re: Simple trigger help needed, Update who & when

    Are you sure the trigger is valid ?

    Check the VALID column in SYSCAT.TRIGGERS

    Sathyaram


    Originally posted by janrune
    Hi...
    I have a simple question for one who is experienced...
    In all our tables we have the two fields Edit_Who and Edit_When:
    KL_Edit_Who VarChar(30) DEFAULT USER,
    KL_Edit_When TIMESTAMP DEFAULT CURRENT TIMESTAMP,

    In order to get those updated on each update we have to revert to triggers.
    So I wrote two versions neither works...
    CREATE TRIGGER KL_Key_LastGen_Upd
    AFTER INSERT ON DBO.KL_Key_LastGen
    FOR EACH ROW MODE DB2SQL
    UPDATE DBO.KL_Key_LastGen SET KL_Edit_Who = USER, KL_Edit_When = CURRENT TIMESTAMP

    CREATE TRIGGER KL_Key_LastGen_Upd
    AFTER INSERT ON DBO.KL_Key_LastGen
    REFERENCING NEW_TABLE AS NEWEMPS
    FOR EACH STATEMENT MODE DB2SQL
    UPDATE DBO.KL_Key_LastGen K SET KL_Edit_Who = USER, KL_Edit_When = CURRENT TIMESTAMP
    WHERE KL_Table_Key IN( SELECT NEWEMPS.KL_Table_Key FROM NEWEMPS)

    Anyone care to shed some light on my errors ???
    Thanks
    JR
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Mar 2003
    Posts
    280
    I would try something like:

    CREATE TABLE KL_Key_LastGen (
    KL_Edit_Who varchar(50),
    KL_Edit_When timestamp,
    xxx int
    )@


    CREATE TRIGGER KL_Key_LastGen_Upd
    NO CASCADE BEFORE INSERT ON DBO.KL_Key_LastGen
    REFERENCING NEW AS NEWROW
    FOR EACH ROW MODE DB2SQL
    set newrow.KL_Edit_When = current timestamp, KL_Edit_Who = USER
    @

    -- test

    db2 -td@ -f dd.sql
    db2 "select * from KL_Key_LastGen"

    KL_EDIT_WHO KL_EDIT_WHEN XXX
    -------------------------------------------------- -------------------------- -----------

    0 record(s) selected.

    [jon@lillebror database]$ db2 "insert into KL_Key_LastGen (xxx) values 1"
    DB20000I The SQL command completed successfully.
    [jon@lillebror database]$ db2 "select * from KL_Key_LastGen"

    KL_EDIT_WHO KL_EDIT_WHEN XXX
    -------------------------------------------------- -------------------------- -----------
    JON 2003-08-20-15.47.45.490237 1

    1 record(s) selected.

    You will probably want to capture updates on the table, the same way. I.e. by creating triggers like

    NO CASCADE BEFORE UPDATE ON DBO.KL_Key_LastGen

    as well


    HTH
    /Lennart

  6. #6
    Join Date
    Mar 2003
    Posts
    280
    Sorry, forgot the explanation. The reason that you dont discover any action from the trigger, might depend on the fact that the rows that match the trigger condition have not yet been commited. I.e. if you construct a trigger as:

    CREATE TRIGGER TEST
    AFTER INSERT ON XX
    REFERENCING NEW AS NEWROW
    FOR EACH ROW MODE DB2SQL
    UPDATE XX SET T = CURRENT TIMESTAMP
    WHERE X = NEWROW.X

    and then insert into an empty table

    insert into xx (x) values 1

    At the time of update the newrow is not yet commited, hence there are no rows in the tables that match X = NEWROW.X

    Normally one uses BEFORE triggers for this type of thing. Another thing where one uses BEFORE triggers is for CONSTRAINT things (which are not possible to express as table constraints. Example:

    CREATE TRIGGER CHK_WHATEVER
    NO CASCADE BEFORE UPDATE ON XX
    REFERENCING OLD AS OLDROW NEW AS NEWROW
    FOR EACH ROW MODE DB2SQL
    WHEN (
    oldrow.status = 3 and newrow.status NOT IN (4, 5)
    ) SIGNAL SQLSTATE '75000' ('Message goes here')@


    HTH
    /Lennart

  7. #7
    Join Date
    Aug 2003
    Location
    Austin, Texas
    Posts
    12

    Re: Simple trigger help needed, Update who & when

    Originally posted by sathyaram_s
    Are you sure the trigger is valid ?

    Check the VALID column in SYSCAT.TRIGGERS

    Sathyaram
    It is listed as valid.
    JR Andreassen
    janrune_NOSPAM@io.com

  8. #8
    Join Date
    Aug 2003
    Location
    Austin, Texas
    Posts
    12
    Originally posted by lelle12
    Sorry, forgot the explanation. The reason that you dont discover any action from the trigger, might depend on the fact that the rows that match the trigger condition have not yet been commited. I.e. if you construct a trigger as:
    ...
    HTH
    /Lennart
    Thanks for answering....
    It still doesn't work....
    I'm thinking there has to be somethingelse wrong.
    Could it be trying to compile the triggers like it does with
    SQL language stored procedures ???
    We're having problems with that as well since we don't have a compiler or it's somehow not been installed ???

    On a separate note.... Do we have to purchase the compiler separate ???
    Thanks.
    JR
    JR Andreassen
    janrune_NOSPAM@io.com

  9. #9
    Join Date
    Mar 2003
    Posts
    280
    >Thanks for answering....
    >It still doesn't work....

    I take it that you have changed to a before trigger and it still doesnt update those columns. What happens if you try it in a dummie schema. I.e.

    CREATE TABLE Dummie.KL_Key_LastGen (
    KL_Edit_Who varchar(50),
    KL_Edit_When timestamp,
    xxx int
    )@


    CREATE TRIGGER Dummie.KL_Key_LastGen_Upd
    NO CASCADE BEFORE INSERT ON Dummie.KL_Key_LastGen
    REFERENCING NEW AS NEWROW
    FOR EACH ROW MODE DB2SQL
    set newrow.KL_Edit_When = current timestamp, KL_Edit_Who = USER

    and then insert a row like I did. Do you get the expected result or does it still not work?

    >I'm thinking there has to be somethingelse wrong.
    >Could it be trying to compile the triggers like it does with
    >SQL language stored procedures ???

    That should not be the case

    >We're having problems with that as well since we don't have a >compiler or it's somehow not been installed ???
    >
    >On a separate note.... Do we have to purchase the compiler >separate ???

    I dont think that there is a compiler shipped with DB2. I didnt see what platform your on, but gcc is availible for free on most platforms

    /Lennart


    >Thanks.
    >JR

  10. #10
    Join Date
    Aug 2003
    Location
    Austin, Texas
    Posts
    12
    Originally posted by lelle12
    and then insert a row like I did. Do you get the expected result or does it still not work?

    /Lennart
    That works with the dummy table.
    But still not with the real one ???
    JR
    JR Andreassen
    janrune_NOSPAM@io.com

  11. #11
    Join Date
    Apr 2003
    Location
    Phoenix, AZ
    Posts
    177
    Needs to be BEFORE INSERT trigger, like the test example you tried.
    Fred Prose

  12. #12
    Join Date
    Aug 2003
    Location
    Austin, Texas
    Posts
    12
    Originally posted by fprose
    Needs to be BEFORE INSERT trigger, like the test example you tried.
    It is defined as
    JR Andreassen
    janrune_NOSPAM@io.com

  13. #13
    Join Date
    Aug 2003
    Location
    Austin, Texas
    Posts
    12
    Originally posted by janrune
    It is defined as
    Oops....

    It is defined as :

    CREATE TRIGGER DBO.KT_Key_Table_Upd
    NO CASCADE BEFORE INSERT ON DBO.KT_Key_Table
    REFERENCING NEW AS NEWROW
    FOR EACH ROW MODE DB2SQL
    set newrow.KT_Edit_When = current timestamp, newrow.KT_Edit_Who = USER

    It doesn't work on the existing table, but it works just fine on
    a new one.. ???
    That's strange...
    Is there a way to make sure there is no trigger besides "DROP TRIGGER xxxx" ???
    Thanks
    JR
    JR Andreassen
    janrune_NOSPAM@io.com

  14. #14
    Join Date
    Mar 2003
    Posts
    280
    Originally posted by janrune
    Oops....

    Is there a way to make sure there is no trigger besides "DROP TRIGGER xxxx" ???
    Thanks
    JR
    Not sure what you mean. To investigate if there are triggers, you can select from syscat.triggers. I.e.

    select * from syscat.triggers where trigname = 'KT_Key_Table_Upd'

    But inorder to get rid of a trigger you use drop. I.e.

    drop trigger DBO.KT_Key_Table_Upd

    BTW, could you post ddl for the table and trigger?


    /Lennart

  15. #15
    Join Date
    Aug 2003
    Location
    Austin, Texas
    Posts
    12
    Originally posted by lelle12
    Not sure what you mean. To investigate if there are triggers, you can select from syscat.triggers. I.e.

    select * from syscat.triggers where trigname = 'KT_Key_Table_Upd'
    But inorder to get rid of a trigger you use drop. I.e.
    drop trigger DBO.KT_Key_Table_Upd
    BTW, could you post ddl for the table and trigger?

    /Lennart
    Here are the Definitions:

    -- SELECT * FROM SYSCAT.TRIGGERS
    -- * DROP TABLE DBO.KL_Key_LastGen
    CREATE TABLE DBO.KL_Key_LastGen (
    KL_Table_Key VARCHAR(18) NOT NULL,
    KL_Last_NKey INT DEFAULT 1 NOT NULL,
    KL_Last_CKey VARCHAR(20) DEFAULT '1' NOT NULL,
    KL_DayStamp SMALLINT DEFAULT 0 NOT NULL,
    KL_Edit_Who VarChar(30) DEFAULT USER,
    KL_Edit_When TIMESTAMP DEFAULT CURRENT TIMESTAMP,
    CONSTRAINT PK_KL_Key_LastGen PRIMARY KEY (KL_Table_Key)
    )
    -- DROP TRIGGER DBO.KL_Key_LastGen_Upd
    CREATE TRIGGER DBO.KL_Key_LastGen_Upd
    NO CASCADE BEFORE INSERT ON DBO.KL_Key_LastGen
    REFERENCING NEW AS NEWROW
    FOR EACH ROW MODE DB2SQL
    set newrow.KL_Edit_When = current timestamp, KL_Edit_Who = USER
    JR Andreassen
    janrune_NOSPAM@io.com

Posting Permissions

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