Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2010
    Posts
    31

    Unanswered: Create trigger before update

    Hi, I have two tables

    1. nav.temp1 (user_id, type, email_address, update_user, update_date)
    2. nav.temp1_hist ((user_id, type, email_address, update_user, update_date)

    If the email_address is updated in the nav.temp1, then I would like to insert the record in the nav.temp1_hist with the old email_address and update user fields before updating the email_address in the nav.temp1. I am using the following trigger to do this task

    create trigger nav.trg_update_temp1_hist no cascade before update of email_address on nav.temp1
    REFERENCING OLD AS old_row FOR EACH ROW MODE DB2SQL

    insert into nav.temp1_hist (user_id, type, email_address, update_user, update_date)

    values (old_row.user_id, 'EMAIL', old_row.email_address, old_row.update_user, current timestamp);

    But I am getting the following error message when creating the trigger.

    The trigger "nav.trg_update_temp1_hist" is defined with an unsupported triggered SQL statement.. SQLCODE=-797, SQLSTATE=42987, DRIVER=3.63.108

    Please help.

    Thanks

    Nav
    Last edited by navch; 07-29-13 at 12:12.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You need to do it as an AFTER trigger, not BEFORE. You will have access to the values before the update.

    Andy

  3. #3
    Join Date
    Jan 2010
    Posts
    31
    Thanks a lot.

    Nav

  4. #4
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    wrong approach

    The TRIGGER is how you tell the world that you cannot write declarative code and have to lapse back to 1950's COBOL or Auto_Coder to do your job.

    And not posting DDL is how you express contempt for Netiquette. The useless punch card file skeleton seems to include a “blood_type” column; anytime I see a generic ”<nothing in particular>_type” column in a table I assume it is blood. Hey, why not? That follows ISO-11179 standards and you do not.

    Why did you violate the data versus meta-data rules of data modeling? Why don't you know that rows are not records? That is fundamental to RDBMS!

    To track the history of, say, Foobars we need to see time as a continuum and model it as (begin_date, end_date) pairs that define when a foobar had a particular value. Here is the skeleton.

    CREATE TABLE Foobar_History
    (foo_id CHAR(9) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATETIME, --null means current
    CHECK (start_date <= end_date),
    foo_status INTEGER NOT NULL,
    PRIMARY KEY (foo_id, start_date));

    When the end_date is NULL, that state of being is still current. You use a simple query for the status on any particular date;

    SELECT *
    FROM Foobar
    WHERE @in_cal_date
    BETWEEN start_date
    AND COALESCE (end_date, CURRENT_TIMESTAMP);

    There are more tricks in the DDL to prevent gaps, etc with deferred constraints.

Posting Permissions

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